[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On Thu, 6 Aug 2015 10:17:43 -0500, John McKown wrote: > Too bad that SQLite does not implement updatable views. Sometimes a similar construct as updatable views can be obtained with an INSTEAD OF trigger. -- Regards, Kees Nuyt
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On Thu, Aug 6, 2015 at 4:54 PM, Wade, William wrote: > 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. Pedantically speaking, "will" is not quite right. That will _normally_ apply, but depends on the whims (and memory pressure) of the OS. When this question/problem has come up before, some people have suggested "coercing" the raw db file into the filesystem cache with something like: cat mydb > /dev/null (or whatever the equivalent is on Windows) which simply causes mydb to be read into the filesystem cache, independently of sqlite (again, subject to the whims of the OS). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
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 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. **
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
If the OP shows us the data base schema, and the select, then perhaps its performance could be enhanced by changing something. One thing which I sometimes do is "denormalize" a table by splitting it into two, or more, tables which are related via a shared UUID value. I put the often used columns in one and the lesser used columns in the other. That way, when I do a SELECT which only needs the columns in the first, I don't need to read the other table at all. In my case, this table was rarely updated. But updating it (changing column values and adding new rows) is a bit more difficult. Too bad that SQLite does not implement updatable views. On Thu, Aug 6, 2015 at 10:08 AM, Stephan Beal wrote: > On Thu, Aug 6, 2015 at 4:54 PM, Wade, William wrote: > > > 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. > > > Pedantically speaking, "will" is not quite right. That will _normally_ > apply, but depends on the whims (and memory pressure) of the OS. > > When this question/problem has come up before, some people have suggested > "coercing" the raw db file into the filesystem cache with something like: > > cat mydb > /dev/null > > (or whatever the equivalent is on Windows) > > which simply causes mydb to be read into the filesystem cache, > independently of sqlite (again, subject to the whims of the OS). > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of > those who insist on a perfect world, freedom will have to do." -- Bigby > Wolf > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On 4 Aug 2015, at 6:35pm, Scott Hess wrote: > 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. That's why I didn't give any advice. /Something/ has to load those sectors from main storage and whenever that is done, that's when the extra 57 seconds is going to happen. It could be tested, of course. Make a source file which is ten times that length and see if it takes 10 minutes the first time and 30 seconds subsequent times. I doubt that a database with 10 million rows could all fit in the cache. Simon.
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On 3 Aug 2015, at 1:58pm, Linquan Bai 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. If you tell us how big an average row is (how many bytes of data) then we can tell you whether 1 million rows in 1 minute is a reasonable time. But my guess is that that's more or less the time you'd expect. Simon.
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On Tue, Aug 4, 2015 at 10:45 AM, Simon Slavin wrote: > > On 3 Aug 2015, at 1:58pm, Linquan Bai 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. Of course, there is an expensive way: ?Put your SQLite data base on an PCIe SATA III (6Gib/s) SSD drive. Lowest cost for a reasonable sized one which I saw on Amazon was USD 250 for 240GiB. http://smile.amazon.com/Kingston-Digital-Predator-SHPM2280P2H-240G/dp/B00V01C4RK This is for me as a Amazon Prime member. > > If you tell us how big an average row is (how many bytes of data) then we > can tell you whether 1 million rows in 1 minute is a reasonable time. But > my guess is that that's more or less the time you'd expect. > > Simon. > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On Tue, Aug 4, 2015 at 9:23 AM, John McKown wrote: > On Tue, Aug 4, 2015 at 10:45 AM, Simon Slavin > wrote: > > On 3 Aug 2015, at 1:58pm, Linquan Bai 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
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
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? Hope to received your valuable suggestions. Thanks!
