Michael Van Canneyt escreveu:
On Thu, 11 May 2006, Luiz Americo wrote:
Michael Van Canneyt escreveu:
sqlite is good candidate too (multiplatform,single file,fast, only
one
dll/so required)
Sorry, but no:
- No external dependencies, please.
- Specifically: sqlite is a horrible database for use in Pascal.
Based in what you say that?
Based on extensive tests which I did and which I published in a German
magazine.
In fact the TDataset descendant has better performance (see
http://www.geocities.com/camara_luiz/sqlite4fpc/benchmarks.html) than sqldb
(Mysql,Postgresql, firebird). This is so true that recently the sqldb internal
data handling schema was changed borrowing some ideas from
TCustomSqliteDataset. Of course, you are invited to try to replicate the
benchmarks and/or post new benchmarks (with the sources) you did.
It has nothing to do with TDataset. I did tests based on direct API calls.
It also has some extra features like Master/Detail, Lookup,Locate, LocateNext,
Auto Increment.
Any way you can use sqlite using the plain interface (sqlite, sqlite3 units)
that directly call the sqlite dll. Exactly how a C program does. So the
problem is not the pascal language or implementation.
This is what I did.
And very slow for complex queries, in general.
Again, post benchmarks/tests.
See the German Toolbox issues 1,2 and 3 of this year.
Notice that to keep code small the sqlite SQL optimizer only handles the
common queries and let the corner cases to hand optimizations.
I find
SELECT
COUNT(PU_ID)
FROM
PUPIL
LEFT JOIN PUPILTRACK ON (PT_PUPIL_FK=PU_ID)
WHERE
(PT_DATE='2005-09-06') AND (PT_CODE='I') AND (PT_TIME<='08:28:00');
A VERY simple query and the result is a factor 180 worse than
Firebird or MySQL.
Some timings;
sqlite firebird Mysql
1 0:0:42.00 0:1:29.47 0:40.48
2 0:6:12.75 0:0:02.58 0:02.40
3 0:0:00.67 0:0:00.44 0:02.43
4 0:5:59.38 0:0:00.48 0:02.44
1. is the insert of 600.000 records. Here SQLIte is very fast.
The rest are selects like the one above.
I did more tests, all give comparable results: sqlite is very
fast for simple stuff.
Your results are comparable from one made without index published in
sqlite wiki, but when using index, according to the test, sqlite
compares to the other.
See: http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison
And yes, I did all possible optimizations. Version 3 was used.
I have a lot of experience with databases of all kinds, and I
know what to test for.
Please post the database schema, no of records, indexes, so i can
reproduce and publish in a site. As i said, sqlite requires some hand
SQL optimizations. You can see here:
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
If you're using Windows take read this
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows.
Specially the last topic.
Finishing, sqlite is not for all uses neither mysql, postgres. See
http://www.sqlite.org/whentouse.html.
Look, I know that SQLite is popular, but it's simply not suitable for enterprise-use
nor for complex queries. Furthermore a database which says that 'we don't do
typechecking' is completely on the wrong track, even more so in a Pascal
environment. And this is the conclusion of my articles...
As i said above
Luiz
_________________________________________________________________
To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
