NOT EXISTS
Sqlite is finally a database product that values performance and minimal footprint (disk and memory) above a trashcan strategy that would add whatever feature to make the result so-called 'feature rich', say, a bloated piece of software. Therefore, I would vehemently reject all additions listed above, except for one. It's quite difficult to obtain the result for a correlated 'NOT EXISTS' subquery in any alternative way; which is the choice way to determine a subset of data that doesn't satisfy criteria contained in another table.
In my experience I have found 'NOT EXISTS' (or is it 'NOT IN') to be extraordinarly slow. Being that SQLite provides 'EXCEPT' the much faster construct can be used to the same end (at least it was faster with Oracles's equvalent: 'MINUS', to wit:
select name,addr from employee where id not in (select id from sales)
becomes
select name,addr from employee where id in ( select id from employee except select id from sales )
-- Are you calling Oracle 'a bloated piece of software'?. LOL. I would love to see a comparison of Oracle and SQLite (latest stable or bleeding edge SQLite version Vs Oracle 10g). I would love it. [This comparison idea is as valid as comparing a novel to a short story.] Anyway, SQLite seems a lil' database engine for lil' works. Sorry, not enough for me :). -- Why would anyone compare Oracle to sqlite other than to say "can you add support for this Oracle syntax to make migration between them easier"? -- Someone might mistakenly compare Oracle to SQLite because they fail to comprehend that the two products solve very different problems.
- Up to this moment I thought that Postgree was smallest possible free DB engine (since MySQL is NOT free), so if you are looking for something to distribute along with your application, SQLite seem to win against 10g, MySQL, Postgree, or whatever (by Makc). - Just to be awkward, what about berkelydb etc? And when you say free you mean free of restrictions don't you (rather than Free software)? -- Berkeley DB does not include SQL support.
- To above paragraph. Sometimes it is better pay few hundreds of bucks (just few hours of my work rate) and get much more powerful commercial solution which is royalty free. For example I very love Valentina database - (http://www.paradigmasoft.com). Valentina beats anything in 10-100+ times, especially on big dbs . It is not expensive, royalty free. Really full SQL92, yet they have cool Object-Relational features.
- To above paragraph: 'Valentina beats anything in 10-100+ times'. Ok, 10-100+ times of WHAT?. RAM Usage?, CPU Usage?, CPU Cycles?, CPU Count?, Consistent Gets?, Concurrent Users?, Concurrent Transactions?, Parses?, Executes?, Fetches?, Recursive Calls?, Physical Reads?, Documentation? ... If you don't have arguments based on real data in a production environment with real data load (users & transactions) your comment is useless. Benchmark it, prove it and then show us your results, not your 'thoughts'.
- Let me answer. I have talk about speed :-) About time of queries execution in seconds. Example. I have bench Table with million records. 10 fields of all types. 100MB size. Next query "SELECT DISTINCT * From T1" Valentina do in 1.7 seconds, SqlLite in 280 seconds. Difference 180 times. Or query: "SELECT * FROM T1 ORDER BY fld_byte, fld_varchar Desc" Valentina do in 1.99 seconds, SqlLite in 115 seconds. Difference 55 times. This is just single table queries. I do not mention joins on 3-5-7 tables with, with GROUP BY, HAVING, ... More, if to make database in 10 million records, so db grow to 1Gb the you will see even better Valentina wins. If you want get information about real production power then just go to their users testimonials page and read quotes starting from 1998 year it seems.
- As to the above - those numbers and the queries are meaningless without the database schema and the source used to conduct the test. If you'd like to lend some credibility to your assertions, you need to provide a link where that information can be downloaded or viewed.
- (1) About the 'speed results'. Speed?, are you talking about speed?, ouch. I can give you the fastest queries on Oracle with 'FIRST_ROWS' hints, i can tweak to the death some internal parameters to give users their results on a fraction of a second, heck, i can even cheat with my indexes to achieve this results ... but know what?: these results don't mean anything. Like yours. What kind of value has a querie that executes on 2 seconds and uses 50% of CPU if another one do it on 10 seconds and uses only 10% of CPU?. Do you know how to answer to this question?. Easy: it depends. Always depends. C,mon man, don't pretend to be a Lord Sith on the first day, complete your Padawan lessons before ;).
- (1, continued) How many parses and fetches are generating your queries on your Database?, what kind of 'stress' level are suffering your disks?, and your CPU?, what about the locks?, and the waits?, are you doing 'implicit conversions' of the SQLite side?, are you using the same amount and cardinality of data on SQLite?, are you using some kind of index on SQLite?, do you know how to create indexes on SQLite?, WHY are you querying against a table and not against a view?, do you know something about views and database schemas?, do you have the ER diagram of both schemas in order to have 'something' to show us?, do you have REAL benchmarks that we could tests on our environments? ... etc.
- (1, continued) HOW COULD YOU (sorry for the caps) guide me to the 'users testimonials page' in order to check real data load (on the 'wide' meaning) on production environments?. Are you kidding me?. Oh, god!. This is like "hey, i would like to know how your Database behaves on a 3000 concurrent users environment with 2000 transactions per second. I am mainly interested on the cluster solution that you could give me and the efficient ways to maximize CPU, RAM and HD resources" and your answer being "yeah, go to www.nice-database.com/testimonials.htm and read my happy-customers histories". LOL. Are you serious?.
* Ha-Ha-Ha. Be sure I am not dummy users, so I know answers to all questions you asks. I have more than 15 years of db experience. All your blames that I have not take into account something are really foolish! Because let me repeat: I have made the same table with the same fields with the same data in records, and run the same queries on the same hardware in the same clean environment (no other apps was run to eat CPU or disk). And I have not use any tricks like "FIRST_ROWS". Both dbs was on default parameters. You still claim that this is not fair bench ???!!! Tell this to somebody else.
- Well, if you (a DBA with 15 years of experience) can't show us a 'miserable' execution plan or a report with timed statistics (like Oracle's Tkprof), then, my friend, your 15 years of work as a DBA has been totally and dramatically wasted. Sincerelesy, i can't imagine any senior DBA (heh, even an Access 'Senior DBA' if this job exists, by the way) posting on forums, wikis or blogs things like 'hey, this RDBMS is 100x faster than yours' or 'hey, the testimonials page is where you will find the answers of all your tuning and performance related questions'. Sorry, i just can't imagine it. Anyway, discussion ended for my side. Sorry to all SQLite users & developers for these non-sense paragraphs.