On Tue, Aug 4, 2015 at 9:23 AM, John McKown <john.archie.mckown at gmail.com> wrote:
> On Tue, Aug 4, 2015 at 10:45 AM, Simon Slavin <slavins at bigfraud.org> > wrote: > > 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. > > > > That sounds correct to me. I don't know which OS the OP is running (likely > Windows, which I don't know well). But I wonder if there is some way, like > running a program before he runs his application which can tell the OS to > "preload" the file into RAM cache. On Linux, I might do something like: "dd > if=/path/to/sqlite-database.sqlite3 of=/dev/null bs=1024 count=100" which > would, as a side effect, pull the first 100KiB of the file into RAM. You could also write code to get the file underlying the database (sqlite3_file_control with SQLITE_FCNTL_FILE_POINTER), then use the VFS layer to manually page through the file. BUT, keep in mind that you might find that you've just moved the 1min time from query time to preload time, in which case you've just complexified without adding anything. If preloading makes the overall operation faster, then you could probably see some benefit from running VACUUM. You might also try different page sizes (note that changing page size requires VACUUM, so make sure that you're actually measuring page-size differences and not VACUUM differences). Changing to memory-mapped may change timings, too, since the OS may predict reads differently for memory-mapped I/O versus POSIX I/O. -scott
