Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> Did you try www.aducom.com/sqlite, you can create it dynamically in your > code or use it as VCL-component. > It also works in Delphi6 > > Use the Database component and the Query component, you get a > tdataset-alike resultset. That's the one we were using initially that seemed slow. I tried the version for SQLite 3 but I don't think it is completely working at this point. It seemed as if they where still converting from the old style 2.8 callbacks Eventually we discovered a wrapper that, while it doesn't have the same functionality as the Aducom stuff, it was faster so I stopped trying to work out what I was doing wrong and swopped over The wrappers don't even have a decent name :) they were from http://www.itwriting.com/sqlitesimple.php off the SQLite wrappers page. Work fine so far
RE: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
Tried to compile your wrapper this morning with no luck The code seems to be written with Delphi 7 or higher? We are still using delphi 6 which doesn't have the SysUtils data structure TFormatSettings, and the functions FormatDateTime have different declarations (only 2 paramaters) Those where the only issues I could see at a glacne, may try and hack that out at a later date when I get more time > 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..
RE: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
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!
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!
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
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! --- Chris Schirlinger <[EMAIL PROTECTED]> wrote: > > H The fact that the freelist size is different > > suggests either a bug in sqlite3_analyzer or a corrupt > > database file. What does "PRAGMA integrity_check" say? > > It returned a batch of Page not used (from about 4 to 48 I think).\ > > That was it > > > What OS did you say you are using? > > Windows XP professional on both test machines > > We are currently examining the componants we are using to access > SQLite3. We are accessing the DLL more directly now through exported > DLL functions and getting better speeds > > > = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> H The fact that the freelist size is different > suggests either a bug in sqlite3_analyzer or a corrupt > database file. What does "PRAGMA integrity_check" say? It returned a batch of Page not used (from about 4 to 48 I think).\ That was it > What OS did you say you are using? Windows XP professional on both test machines We are currently examining the componants we are using to access SQLite3. We are accessing the DLL more directly now through exported DLL functions and getting better speeds
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> > CREATE INDEX myMyIndex ON myTable (myKey); > > CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); > > CREATE INDEX myNumOneIndex ON myTable (NumOne); > > Why did you chose these indexes? > Updates and Inserts are faster with fewer indices. > Perhaps they can be reduces? > > You do have a rather large database, you'll have to be pretty > aggressive to get good performance from it. Yeah, originally there was (and should be) only the one UNIQUE index. I added the other two during a test and when someone here asked about the schema they were still in They have been dropped now, and the single unique index is the only one remaining. It is about the bare minimum I think I can get away with
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> Out of curiousity, have you tried doing a plain INSERT instead of INSERT > OR REPLACE INTO? > Since you have no unique fields in your table, I'm not exactly sure how > or why you might get an > INSERT failure, but perhaps Sqlite is doing some sort of data comparison > on each insert. We have one multi-field unique index, > CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); Interestingly enough, on further tests (one developer forgot to create the index and was duplicating their data every time they ran a test) we discovered that the INSERT OR REPLACE INTO was not really much slower when it was doing it's job correctly He was getting 6 million records inserted into the DB in about 16 minutes. With the index added it dropped to about 18 but at least it was doing the right thing Thats still only about 2500 records a second, but getting much better!
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> The myMyIndex will only slow you down. myNumOneIndex > will help if you have queries that use the numOne column. Hurm, yes that makes sense. it wasn't supposed to be there, but was added just incase it helped, which it didn't > What is the output from sqlite3_analyzer? Unfortunatly didn't space well :( /** Disk-Space Utilization Report For test.sdb *** As of 2005-Feb-02 22:13:52 Page size in bytes 1024 Pages in the whole file (measured) 282059 Pages in the whole file (calculated).. 282014.0 Pages that store data. 282014.099.984% Pages on the freelist (per header) 00.0% Pages on the freelist (calculated) 45.0 0.016% Number of tables in the database.. 2 Number of indices. 3 Number of named indices... 3 Automatically generated indices... 0 Size of the file in bytes. 288828416 Bytes of user payload stored.. 135341808 46.9% *** Page counts for all tables with their indices myTable 282013.099.984% SQLITE_MASTER. 1.0 0.000% *** All tables and indices *** Percentage of total database.. 99.984% Number of entries. 10659384.0 Bytes of storage consumed. 288782336.0 Bytes of payload.. 213059020.0 73.8% Average payload per entry. 19.9879298841 Average unused bytes per entry 2.90023344689 Average fanout 94.18 Maximum payload per entry. 262 Entries that use overflow. 0.0 0.0% Index pages used.. 1763.0 Primary pages used 280251.0 Overflow pages used... 0.0 Total pages used.. 282014.0 Unused bytes on index pages... 253006.014.0% Unused bytes on primary pages. 30661696.0 10.7% Unused bytes on overflow pages 0.0 Unused bytes on all pages. 30914702.0 10.7% *** All tables *** Percentage of total database.. 59.5% Number of entries. 2664849.0 Bytes of storage consumed. 171823104.0 Bytes of payload.. 135342313.0 78.8% Average payload per entry. 50.7879857358 Average unused bytes per entry 6.38924982241 Average fanout 94.18 Maximum payload per entry. 262 Entries that use overflow. 0.0 0.0% Index pages used.. 1763.0 Primary pages used 166033.0 Overflow pages used... 0.0 Total pages used.. 167796.0 Unused bytes on index pages... 253006.014.0% Unused bytes on primary pages. 16773380.0 9.9% Unused bytes on overflow pages 0.0 Unused bytes on all pages. 17026386.0 9.9% *** All indices ** Percentage of total database.. 40.5% Number of entries. 7994535.0 Bytes of storage consumed. 116959232.0 Bytes of payload.. 77716707.0 66.4% Average payload per entry. 9.72122918969 Average unused bytes per entry 1.73722624268 Maximum payload per entry. 13 Entries that use overflow. 0.0 0.0% Primary pages used 114218.0 Overflow pages used... 0.0 Total pages used.. 114218.0 Unused bytes on primary pages. 13888316.0 11.9% Unused bytes on overflow pages 0.0 Unused bytes on all pages. 13888316.0 11.9% *** Table myTable and all its indices * Percentage of total database.. 99.984% Number of entries. 10659380.0 Bytes of storage consumed. 288781312.0 Bytes of payload.. 213058515.0 73.8% Average payload per entry. 19.9878900086 Average unused bytes per entry 2.90019757247 Average fanout 94.18 Maximum payload per entry. 61 Entries that use overflow. 0.0 0.0% Index pages used.. 1763.0 Primary pages used 280250.0 Overflow pages used... 0.0 Total pages used.. 282013.0 Unused bytes on index pages... 253006.014.0% Unused bytes on primary pages. 30661302.0 10.7% Unused bytes on overflow pages 0.0 Unused bytes on all pages. 30914308.0 10.7% *** Table myTable w/o any indices
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
Ok I hadn't toyed with those. Just tried it, the speed for queries hasn't increased much, but a little However, I started getting hard drive thrashes for 10+ seconds from time to time. Will investigate the cahce further to see what suits this app > Did you try increasing the page cache size. Your data set is very big. > > pragma page_cache = 2; > > This should at least improve the speed for queries.
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
Did you try increasing the page cache size. Your data set is very big. pragma page_cache = 2; This should at least improve the speed for queries. Clive "Chris Schirlinger" <[EMAIL PROTECTED]> on 02-02-2005 09:07:49 Please respond to [EMAIL PROTECTED] To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow? > What speed were you expecting? > Are you comparing it to another database? If so what are the results for that > database? Anything better than what we got. The results are the worst we have gotton from any DB or any self rolled data system (Jet is better, het shouldn't be better) After more tests, it is dipping to 10 records per second update time. Based on the speed showen on the web site, I was expecting to at least get 1000 records a second updating and somewhere above 10k when selecting I am assuming we MUST have mucked something up
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> What speed were you expecting? > Are you comparing it to another database? If so what are the results for that > database? Anything better than what we got. The results are the worst we have gotton from any DB or any self rolled data system (Jet is better, het shouldn't be better) After more tests, it is dipping to 10 records per second update time. Based on the speed showen on the web site, I was expecting to at least get 1000 records a second updating and somewhere above 10k when selecting I am assuming we MUST have mucked something up
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
What speed were you expecting? Are you comparing it to another database? If so what are the results for that database? Clive "Chris Schirlinger" <[EMAIL PROTECTED]> on 02-02-2005 06:00:45 Please respond to [EMAIL PROTECTED] To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: [sqlite] Not getting the speed I think is possoble. Basic select statment slow? I've got a 6 million row DB in SQLite 3, but getting... odd results which don't match up with the speed tests I've seen The statement: SELECT * FROM myTable WHERE myKey=1000 takes between 1 second to 4 or 5 on spikes. The returned result set is ~2000 records. I havn't seen more than 2000 recs/second usually less Similarly, the query (A basic APPEND or INSERT) INSERT OR REPLACE INTO myTable (myKey, NumOne, NumTwo, NumThree, NumFour, NumFive, NumSix, NumSeven) VALUES (1000, 1, 2, 3, 4, 5, 6, 7) is doing at MOST about 300 records per second and at worst 100 a second. I have about 3000 inserts/updates all wrapped inside a single Transaction unless doing a complete population of the DB in which case it is batched but still all wrapped in transactions The schema is VERY basic: CREATE TABLE [myTable] ( [myKey] [bigint] NULL , [NumOne] [int] NULL , [NumTwo] [real] NULL , [NumThree] [real] NULL , [NumFour] [real] NULL , [NumFive] [real] NULL , [NumSix] [float] NULL , [NumSeven] [float] NULL ); CREATE INDEX myMyIndex ON myTable (myKey); CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); CREATE INDEX myNumOneIndex ON myTable (NumOne); Now initially I didn't have anything except the UNIQUE index, though adding the second two hasn't made any difference once way or the other Personalyl I'd LOVE for that UNIQUE index to be a clustered index (it was elsewhere) but that just doesn't seem to be an option Any ideas where I am going wrong here? Or are these the numbers I am expected to see? (Note: I am using transactions in case I didn't make that clear, I am also doing this in Delphi using the open source Aducom.nl componants, but at the raw end it seems their code is mostly fairly close to the bare bones of the DLL exported functions. Doesn't seem to be an issue there but who knows)
