> Doing a keyed search is no guarantee that you won't touch *every* single > page in the table, if the rows are inserted in random order. Try this: ...cut... > Assuming key is the key field you want, the records will be inserted into > wibble in key order. Selecting by key will then touch the least number of > pages, speeding up the select.
Ahhh excellent idea, this seems similar to a clustered index, where the data is actually stored on disk in order. That is actually what we wanted, but SQLite didn't seem to support those sorts of index > Why? Does you program require the machine to be rebooted before use? > > I'm not trying to be facetious, but your test seem very invalid without > further explanation. No perfectly understandable. This is not a test for SQLite in particular, but was an issue I discovered a long time ago when doing speed tests Speed tests on any system. SQLite, basic files whatever, are *SLOWER* on the first time you run them due mainly to HDD caching, and whatever other caching the program does with the data. We would get lovely 20k per second record update on stuff and then on fresh reboot discover we dropped to 20 records per second for first 2000 records Unfortunatly, our program has the following requirement, user turns on machine, user immedeatly does some NASTY damn data retrieval or update, closes program and turns off machine This first retrival is our issue, and is slow. What I have working now seems to be good however. We did several things: 1) Set the page size of the database to 4096 which matches most peoples NTFS partions and has sped up the initial access nicely. Wont work for everyone but most I think 2) spawn several threads as the program is opened, atrifically caching the data before the user has a chance to push any buttons and wonder why they take 2 seconds to respond. Gonna try your ordering idea, that will help IMMENSLY for the initial DB though I can see how it would slowly fall out of order as the user updates. Frankly, SQLite has been the closest I've seen to resolve this issue (well it DOES actually solve the issue). Love this DB :) Personally I think it asking a bit much (I mean 20 million rows on some guys Windows 95box? and you want it HOW fast?) Thanks
