Re: [sqlite] Storing text in sqlite vs. external flat file
Ed Porter wrote: I found that performance began to fail miserably as the blob size increased above 500 bytes (has anyone else experienced this problem?). I just ran a test where I do "count" INSERTs of a BLOB of different sizes. Here's the result: size=100 count=100 time=26374 microseconds per iteration size=200 count=100 time=24480 microseconds per iteration size=400 count=100 time=31388 microseconds per iteration size=1000 count=100 time=37764 microseconds per iteration size=2000 count=100 time=56178 microseconds per iteration size=4000 count=100 time=85590 microseconds per iteration size=8000 count=100 time=137930 microseconds per iteration size=1 count=100 time=168661 microseconds per iteration size=2 count=100 time=288281 microseconds per iteration size=4 count=100 time=429003 microseconds per iteration size=10 count=100 time=877089 microseconds per iteration size=100 count=1 time=1128732 microseconds per iteration size=200 count=5000 time=824365 microseconds per iteration size=400 count=2500 time=515504 microseconds per iteration size=1000 count=1000 time=269727 microseconds per iteration size=2000 count=500 time=243470 microseconds per iteration size=4000 count=250 time=206263 microseconds per iteration size=8000 count=125 time=170581 microseconds per iteration size=1 count=100 time=164367 microseconds per iteration size=2 count=50 time=156277 microseconds per iteration size=4 count=25 time=150406 microseconds per iteration size=10 count=10 time=151819 microseconds per iteration In the first group of tests, I hold the count constant and just increase the blob size. Doing 100 INSERTs of a 100K blob is only 33 times slower even though it is inserting 100 times more data. In the second set of tests, the total number of bytes inserted is held constant. As the blob size increases the number of INSERTs decreases. In this test we see that it is much faster to insert a few large blobs that to insert many small blobs. I doesn't look to me like performances is "miserable" for larger blobs. How did you run your tests? The results above are on SQLite version 3.0.6 using the Tcl bindings. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Storing text in sqlite vs. external flat file
Hi Ken I found that performance began to fail miserably as the blob size increased above 500 bytes (has anyone else experienced this problem?). When I posted the problem, I think that someone stated the docs show the reasonable limit on blobs is 230 bytes. Anyways, I had to store the blobs direct to disk and use SQLite to track the addresses (no different than Oracle, MSSQL, MySQL etc). Please notify if you can get your system to work! At 01:21 PM 9/10/04 -0700, [EMAIL PROTECTED] wrote: >I am looking into using sqlite for storing some data that will be ~100,000 >records in size, where each record will contain text that has an average >size of 40k, but could be > 200k. I will likely need to encrypt (and >potentially compress) the database. > > > >My question is whether to store the text in the database, or to keep a >separate file for the text with seek pointers and lengths in the database. >My preference is for the former, since I wouldn't have to manage a separate >robust encryption/compression/deletion process, but I am concerned about the >perf and size of the insertion stress experiments I've been running, and >I've seen comments on this list suggesting the latter for BLOBs. Are there >specific tuning tweaks I can make to improve my results? > > > >Thanks, > >Ken Cooper > > Sincerely, Ed Porter
Re: [sqlite] Storing text in sqlite vs. external flat file
If you decide to store the text in an external file, you might check out this library to make this process a bit more fault tolerant (like sqlite): http://users.auriga.wearlab.de/~alb/libjio/ Cheers! -j On Fri, 10 Sep 2004 13:21:08 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I am looking into using sqlite for storing some data that will be ~100,000 > records in size, where each record will contain text that has an average > size of 40k, but could be > 200k. I will likely need to encrypt (and > potentially compress) the database. > > My question is whether to store the text in the database, or to keep a > separate file for the text with seek pointers and lengths in the database. > My preference is for the former, since I wouldn't have to manage a separate > robust encryption/compression/deletion process, but I am concerned about the > perf and size of the insertion stress experiments I've been running, and > I've seen comments on this list suggesting the latter for BLOBs. Are there > specific tuning tweaks I can make to improve my results? > > Thanks, > > Ken Cooper > >
[sqlite] Storing text in sqlite vs. external flat file
I am looking into using sqlite for storing some data that will be ~100,000 records in size, where each record will contain text that has an average size of 40k, but could be > 200k. I will likely need to encrypt (and potentially compress) the database. My question is whether to store the text in the database, or to keep a separate file for the text with seek pointers and lengths in the database. My preference is for the former, since I wouldn't have to manage a separate robust encryption/compression/deletion process, but I am concerned about the perf and size of the insertion stress experiments I've been running, and I've seen comments on this list suggesting the latter for BLOBs. Are there specific tuning tweaks I can make to improve my results? Thanks, Ken Cooper
