Menu
I create an sqlite3 database (using SQLite Expert Professional) with 1 table and more than 500,000 records; If I command a simple query like:
select * from tableOne where entry like 'book one'
if it's my first command to be executed after connecting to database, it takes a considerably long time to be executed and retrieve the result(~15seconds) but just after first command, everything comes back to normal and now every command executes with a very acceptable speed;
even if I close my application(I use pure LUA with sqlite modules)(and within it's logic, reasonably close all connections) as long as Windows(8 x64) is running an not restarted, every command even the first one executes very well but after restarting windows, again, like always first command is slow to be executed;
what is the reason? how can I prevent this?
522
The SQLite docs explains why this is so slow: Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe.
Query one takes about 30ms, but 150ms to fetch the data from the database. Query two takes about 3ms -this is the one I therefore prefer-, but also takes 170ms to fetch the data.
The query optimizer in SQLite has basically two choices on how to implement this query. (There are actually six different choices, but we will only consider two of them here.) Pseudocode below demonstrating these two choices.
Most likely after the first time you run this, you've loaded cache up with all your data, so subsequent queries are fast. Do you have an index on entry? An index will allow efficient querying using entry as a filter. You may want to create one:
CREATE INDEX i_tableone_entry ON tableOne( entry );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
