Hello Nathan, Thanks. Counting on the primary key made a HUGE difference in performance. I didn't really expect that but, am pleased with the results.
I did the .explain and explain <query> but, wasn't sure what it was telling me. C Thursday, December 8, 2005, 1:14:10 PM, you wrote: NK> On Thu, Dec 08, 2005 at 12:26:44PM -0500, Teg wrote: >> I have a 6.5 gb database with 29,587 records in it. It takes about >> 30-40 seconds for a count to return the first time I specify one in >> "Sqlite3" (seeing this in my program as well). Subsequent "count" >> operations are nearly instantaneous even when I exit Sqlite3 and >> restart. I'm pretty much seeing that across the board with other DB's >> as well, that count take a fairly long time the first time it's called >> and then it's fast. >> >> Is there any way I can speed this up? I use "count" to set my progress >> bars to proper ranges so, the delay causes people to wait. NK> Most likely the time is being spent reading parts of the database from NK> disk into memory buffers. After it is read once, the OS caches it if NK> you have enough spare RAM. If you want it to be faster, your goal NK> should be to reduce the amount of data that has to be read. NK> If you give count() an argument, it counts the number of times that NK> argument is non-null. Because you are using count(FileOrder) and NK> there is no index on this column, the entire database (minus the NK> overflow pages from your blobs) is read. NK> So your goal will be to come up with a "select count()" statement that NK> doesn't need to read as much data. Try using ".explain" then "explain NK> select count(FileOrder) from Files_V1;" to see what is happening. NK> Determine if it is using an index, or reading the whole table. NK> Then try "select count(*) from Files_V1" and see if that is much NK> faster. If not, try doing the count on Signature to see if you can NK> force it to use the index. Failing that, you could always make a NK> small table of just FileOrder and do the count on that. NK> Good luck! NK> --nate -- Best regards, Teg mailto:[EMAIL PROTECTED]
