*** 451,634 **** NOT EXISTS remarks (off topic) -> UnsupportedSqlRemarkOffTopic - 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. - - - I wonder how useful these "remarks" are... - - They don't really pertain to the question of how well SQLite supports either - standard or "extended" SQL features; I'd suggest that if the participants in - this debate want to continue it, they create a new wiki page specifically for - it; copy everything not related to feature support over to it, and delete it - from this page. - What about Apache Derby? It uses the Apache 2.0 license and is easy to embed in Java applications (http://db.apache.org/derby/). -- See SqliteVersusDerby --- 451,456 ----
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|