I'm an SQLite novice, so if I'm wrong about this, I hope someone will correct me.
The first query will be slower than later queries for the reason Simon mentioned. However, it may still be possible to get some speed up of the first query, if it can avoid reading stuff not in the result set and also avoid lots of wide-spread random seeks. If your 1 million records are the entire database, none of these suggestions will help much. If you "own" the database (can perform writes, or even change the schema) you may be able to change it to a form which reads-faster on the first attempt. All of these are most likely to help if not a lot of changes have occurred since the last VACUUM. For instance, if your 1 million records are an entire table, but are also only 1% of the volume of the database as a whole, a VACUUMed database may be faster. In addition, if your 1 million records are each small, and extracted from a table of 100 million records, then having your table's primary key such-that the result set comes from a contiguous range of the original table may help. The documentation says that VACUUM makes each table contiguous. It doesn't explicitly say that it makes them contiguous in primary-key order, so I don't for sure that this would help (but I suspect it does). Finally, if the table has substantially more columns than you are asking for, splitting the table into two tables (the stuff you want, and the stuff you don't want) may help. If nothing else, VACUUM is likely to shrink the database, which speeds loading it from disk. Bill > From: Simon Slavin [mailto:slavins at bigfraud.org] > Sent: Tuesday, August 04, 2015 10:46 AM > On 3 Aug 2015, at 1:58pm, Linquan Bai <linquan.bai at gmail.com> wrote: >> I am trying to read large data from the database about 1 million records. >> It takes around 1min for the first time read. But if I do the same >> process thereafter, the time is significantly reduced to 3 seconds. >> How can I get a fast speed for the first time read? > You can't. Some part of your computer has pulled that data into cache, and > it's still in the cache when you run the process again, so it the data > doesn't need to be fetched from disk again. > Simon. ************************************************************************************** This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited. **************************************************************************************
