Re: [sqlite] Sqlite Insert Speed Optimization

On Wed, Jul 14, 2010 at 12:34:05PM -0500, Black, Michael (IS) scratched on the wall: > Was that a facetious remark??? > > Rather than "here's a function/pragma that allows you to put the > journal file where you want -- but BE CAREFUL BECAUSE..." > > Writing you own VFS is not for the casual

Re: [sqlite] Sqlite Insert Speed Optimization

From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp Sent: Wed 7/14/2010 1:12 PM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Sqlite Insert Speed Optimization On Wed, Jul 14, 2010 at 1:51 PM, Richard Hipp <d...@sqlite.

Re: [sqlite] Sqlite Insert Speed Optimization

On Wed, Jul 14, 2010 at 1:51 PM, Richard Hipp wrote: > > > On Wed, Jul 14, 2010 at 1:34 PM, Black, Michael (IS) < > michael.bla...@ngc.com> wrote: > >> Was that a facetious remark??? >> >> Rather than "here's a function/pragma that allows you to put the journal >> file where you

Re: [sqlite] Sqlite Insert Speed Optimization

D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp Sent: Wed 7/14/2010 12:51 PM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Sqlite Insert Speed Optimization

Re: [sqlite] Sqlite Insert Speed Optimization

On 14 Jul 2010, at 5:02pm, Black, Michael (IS) wrote: > Does anybody know how to make the journal file go to a different location > than the database? Apprarently it's not treated as a "temporary" file. > Perhaps it should be?? It's essential not to treat the journal file as a temporary

Re: [sqlite] Sqlite Insert Speed Optimization

On Wed, Jul 14, 2010 at 1:34 PM, Black, Michael (IS) wrote: > Was that a facetious remark??? > > Rather than "here's a function/pragma that allows you to put the journal > file where you want -- but BE CAREFUL BECAUSE..." > > Writing you own VFS is not for the casual

Re: [sqlite] Sqlite Insert Speed Optimization

Hipp Sent: Wed 7/14/2010 12:12 PM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Sqlite Insert Speed Optimization On Wed, Jul 14, 2010 at 12:31 PM, Black, Michael (IS) < michael.bla...@ngc.com> wrote: > If you could set the journcal location BEFORE you open

Re: [sqlite] Sqlite Insert Speed Optimization

On Wed, Jul 14, 2010 at 12:31 PM, Black, Michael (IS) < michael.bla...@ngc.com> wrote: > If you could set the journcal location BEFORE you open the database that > wouldn't be such a bad thing. Giving us the ability to do this would allow > for the flexibility when needed with appropriate

Re: [sqlite] Sqlite Insert Speed Optimization

of SQLite Database Subject: EXTERNAL:Re: [sqlite] Sqlite Insert Speed Optimization If you don't know why it's slow you'll be shooting in the dark. And doing compression on a local network link isn't likely to buy you much. Might even hurt. In other words, is it latency or bandwidth? Give 8K

Re: [sqlite] Sqlite Insert Speed Optimization

Werner Smit wrote: > After taking out count(*) and adding a few pragma's and saving 6000 > records rather than 500 at a time I've got it down to 34 minutes. > If I build in on local drive it takes 28 minutes.(with chunks of 500) Why not do an apples-to-apples test and commit the same number

Re: [sqlite] Sqlite Insert Speed Optimization

If you don't know why it's slow you'll be shooting in the dark. And doing compression on a local network link isn't likely to buy you much. Might even hurt. In other words, is it latency or bandwidth? Give 8K/sec I'm guessing it's latency unless you're running a 64KBit line. Are you THAT

Re: [sqlite] Sqlite Insert Speed Optimization

Sent: Wed 7/14/2010 11:24 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Sqlite Insert Speed Optimization Black, Michael (IS) wrote: > Does anybody know how to make the journal file go to a different > location than the database? Apprarently it's not treated

Re: [sqlite] Sqlite Insert Speed Optimization

If you don't know why it's slow you'll be shooting in the dark. And doing compression on a local network link isn't likely to buy you much. Might even hurt. In other words, is it latency or bandwidth? Give 8K/sec I'm guessing it's latency unless you're running a 64KBit line. Are you THAT

Re: [sqlite] Sqlite Insert Speed Optimization

Black, Michael (IS) wrote: > Does anybody know how to make the journal file go to a different > location than the database? Apprarently it's not treated as a "temporary" > file. Perhaps it should be?? Seems like you'd have to communicate the journal location to other processes, meaning

Re: [sqlite] Sqlite Insert Speed Optimization

To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Sqlite Insert Speed Optimization >>According to my math your final database size should be on the order of 100Meg? >> >>That means at 200 minutes and 1,000,000 records: >>83 inserts per second >>

Re: [sqlite] Sqlite Insert Speed Optimization

>>According to my math your final database size should be on the order of 100Meg? >> >>That means at 200 minutes and 1,000,000 records: >>83 inserts per second >>8333 bytes per second >>Both of these values are terrible. >>#1 What kind of network connection do you have? 100BaseT? >>#2 What

Re: [sqlite] Sqlite Insert Speed Optimization

Can you answer #3 though? Does anybody know how to make the journal file go to a different location than the database? Apprarently it's not treated as a "temporary" file. Perhaps it should be?? Michael D. Black Senior Scientist Northrop Grumman Mission Systems

Re: [sqlite] Sqlite Insert Speed Optimization

>>On 14 Jul 2010, at 11:55am, Werner Smit wrote: >> ps. When I started with sqlite it took 500 minutes to save the 1 million >> records. >> I've got it down to just less than 200 minutes with current settings. >> Clarion does it in between 100 and 200 minutes. >Do you have any indexes defined ?

Re: [sqlite] Sqlite Insert Speed Optimization

On 14 Jul 2010, at 11:55am, Werner Smit wrote: > ps. When I started with sqlite it took 500 minutes to save the 1 million > records. > I've got it down to just less than 200 minutes with current settings. > Clarion does it in between 100 and 200 minutes. Do you have any indexes defined ? It

Re: [sqlite] Sqlite Insert Speed Optimization

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Corke Sent: 14 July 2010 01:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite Insert Speed Optimization On 14 July 2010 11:56, Werner Smit wrote

Re: [sqlite] Sqlite Insert Speed Optimization

On 14 July 2010 11:56, Werner Smit wrote: > 3. I'm saving to a network drive. Is this a one-off data import into a new clean sqlite db? If so have you considered writing to a db file on a local drive and then copying the whole file to the network drive afterwards? Paul.

Re: [sqlite] Sqlite Insert Speed Optimization

> Thanks for all the feedback! > > It helped a lot. > 1. I'm going to try and see what happen if I leave the "end transaction" > until 5 insert was done. This is what I meant also when I said 500 was too small. John ___ sqlite-users mailing list

Re: [sqlite] Sqlite Insert Speed Optimization

Thanks for all the feedback! It helped a lot. 1. I'm going to try and see what happen if I leave the "end transaction" until 5 insert was done. 2. I'm going to increase cache_size from 8192 to 16384 Extra info, 1. This program saved data to a clarion file before and in sqlite it's about

Re: [sqlite] Sqlite Insert Speed Optimization

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Drescher Sent: Tuesday, July 13, 2010 12:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite Insert Speed Optimization > I also wrap my stateme

Re: [sqlite] Sqlite Insert Speed Optimization

Griggs, Donald wrote: > I guess I was wondering if the fastest records-per-transaction value > would depend on the page cache and be more or less independent of the > total records to be imported. I think the page cache is one of a great many variables. > So, the records-per-transaction

Re: [sqlite] Sqlite Insert Speed Optimization

Griggs, Donald wrote: > Is the percentage of the final rowcount really a criterion? The answer to that, according to my brief exploration, is somewhere between "yes" and "very much yes", depending on various factors. Thanks, Eric. I guess I was wondering if the fastest

Re: [sqlite] Sqlite Insert Speed Optimization

Werner Smit wrote: > My question(s) > If I want to use the "insert or replace" to populate my database of > around 1 million records. > And I want to do it as fast as possible. > What are all the tricks I can use? Obey the first rule of optimization: don't do it unless you're sure you

Re: [sqlite] Sqlite Insert Speed Optimization

Griggs, Donald wrote: > Is the percentage of the final rowcount really a criterion? The answer to that, according to my brief exploration, is somewhere between "yes" and "very much yes", depending on various factors. -- Eric A. Smith The number of UNIX installations has grown to 10, with

Re: [sqlite] Sqlite Insert Speed Optimization

On 13 Jul 2010, at 5:33pm, Werner Smit wrote: > I currently use the following pragma's (for speed) > temp_store = 2 > page_size=4096 > cache_size=8192 > synchronous=off > > Any others I could try? Don't get too involved in the PRAGMAs until you have a good reason to. The default values are

Re: [sqlite] Sqlite Insert Speed Optimization

On Tue, Jul 13, 2010 at 12:48 PM, John Drescher wrote: >> I was wondering if that's really so.  Wouldn't the marginal speed >> improvement be quite small?  Is the percentage of the final rowcount really >> a criterion? > > Each transaction costs at least 1 disk seek. Doing

Re: [sqlite] Sqlite Insert Speed Optimization

> I was wondering if that's really so.  Wouldn't the marginal speed improvement > be quite small?  Is the percentage of the final rowcount really a criterion? Each transaction costs at least 1 disk seek. Doing thousands of seeks the result would be very slow. John

Re: [sqlite] Sqlite Insert Speed Optimization

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Drescher Sent: Tuesday, July 13, 2010 12:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite Insert Speed Optimization > I also wrap

Re: [sqlite] Sqlite Insert Speed Optimization

> After these 500 i take a few seconds to read more data so sqlite should > have time to do any housekeeping it might need. SQLite is not a Database Server. It has no background threads. So it can't do any "housekeeping" until you call some sqlite3_* function. Pavel On Tue, Jul 13, 2010 at

Re: [sqlite] Sqlite Insert Speed Optimization

> I also wrap my statements (about 500 inserts at a time) with a begin/end > transaction. > After these 500 i take a few seconds to read more data so sqlite should > have time to do any housekeeping it might need. > Wrap more into a transaction. 500 is too small of a percentage of a million.

[sqlite] Sqlite Insert Speed Optimization

Hi there. I've been playing around with sqlite. Very impressed so far. Using 3.5.6 in windows developing with Clarion. My question(s) If I want to use the "insert or replace" to populate my database of around 1 million records. And I want to do it as fast as possible. What are all the tricks I