out of curiocity, can you give a try to my delphi wrappers? http://www.sqlite.org/contrib (not Tdataset replacement, though, but they should be quite fast for this reason. also try the "serverCursor" setting, which allows you to step thorugh the result set one at a time, meaning there is no double-buffering required: rs:=db.createStatement; rs.serverCursor:=true; rs.open.... while not rs.eof do.. let me know how they fare speed-wise, as I have not tested any other wrappers..
________________________________ From: Chris Schirlinger [mailto:[EMAIL PROTECTED] Sent: Thu 3/2/2005 6:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow? No, the DLL wasn't the issue (heck the entire program is a DLL anyway and that's worked well for ages) We eventually discoverd through profiling the Delphi componants we had started using were the slow point, tossed them, and tried someone elses version (some stuff adapted by someone called Tim A.) and they work MUCH better It seems the chokepoints where in the wrapper code itself, perhaps in the mechanisms calling the various SQLite functions. I'd dig deeper but frankly the wrapper we have now lets me get to the bare bones easier. I am pleasently surprised about SQLites power now, for a large single user DB it works nicely. Sure, when we tossed a further 8 million rows into the DB it is slows down, but still ~1 secondish for a 3k select statement Still trying to wrench every piece of speed I can from the DB, and some functions don't seem to be working? (or I am misunderstanding them) For example: CREATE UNIQUE INDEX pk ON myTable (Field1, Field2) ON CONFLICT REPLACE; runs but doesn't create the ON CONFLICT part. When you examine the schema you see : CREATE UNIQUE INDEX pk ON myTable (Field1, Field2); Also any INSERT statement evaluate as if the default is still FAIL Still not really required, INSERT OR REPLACE works well. Was hoping setting the default CONFLICT handeler may speed things up a bit > I wouldn't think DLL calling overhead would be significant > when dealing with things as slow (relatively) as a database. > > Is it really necessary for it to be a DLL? > You might be able to statically link it and remove that overhead. > Are you using COM or ActiveX to call it? If I remember right > they had a lot more overhead than a vanilla DLL. > > Sounds like a job for the profiler!
