Re: [sqlite] Improving insert speed?

[Kurt Welgehausen]

> ... should I be using commit before end ...

Commit and end are synonyms; you don't need both.

> ... this is single user, I assume using immediate or exclusive is  ok ...

It's OK but not necessary. A simple begin is just as good, since
no one else can apply a lock before yours is upgraded.

Regards

Re: [sqlite] Improving insert speed?

[Tony Harris]

OMG!  I can't believe the speed difference!

I wrapped around 3000 transactions, executing them 3 times, so I did a

for y=0, y<3, y++
{
begin immediate transaction testtrans
{
for x=0, x<3000, x++
insert record
}
end transaction testtrans
}

and inserting those 9000 records took maybe a second!  Wow, much more 
impressive!! Thanks for the suggestions!



Now my only question is looking at the reference, it talks about begin, end, 
commit, rollback - should I be using commit before end, or does end imply a 
commit?


also, since this is single user, I assume using immediate or exclusive is 
ok.


-Tony

- Original Message - 
From: "A. Pagaltzis" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, June 24, 2006 12:07 PM
Subject: Re: [sqlite] Improving insert speed?



* Tony Harris <[EMAIL PROTECTED]> [2006-06-24 19:05]:

Is this about average, or is there a way I might be able to get
a little more speed out of it?


Put a transaction around your INSERTs, at least around batches of
a few thousand each, and you’ll get much better speed.

Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/> 

Re: [sqlite] Improving insert speed?

[John Stanton]

Tony Harris wrote:

Hi all,

I have a database where one table stores the bulk of logged data, it has 14 
columns, 11 integers and 3 reals.

Initially I'm gonna have to import a lot of data into this database - about 
9,000 entries per months worth of data.  I already know 3K entries takes about 
192KBytes in the db file.  The biggest problem I am having is insert speed.  I 
know the initial amount will cover 3-4 months worth of data that will need to 
be inserted.  Right now I'm clocking raw inserts of data w/ no unique checking 
at about 1 min 26 sec / 1K entries.  Is this about average, or is there a way I 
might be able to get a little more speed out of it?

Granted, I might be hitting a ceiling of any db api, but I was hoping to squeeze a little more speed if possible ;)  


-Tony

Are you inserting in a transaction?

Re: [sqlite] Improving insert speed?

[A. Pagaltzis]

* Tony Harris <[EMAIL PROTECTED]> [2006-06-24 19:05]:
> Is this about average, or is there a way I might be able to get
> a little more speed out of it?

Put a transaction around your INSERTs, at least around batches of
a few thousand each, and you’ll get much better speed.

Regards,
-- 
Aristotle Pagaltzis // 

[sqlite] Improving insert speed?

[Tony Harris]

Hi all,

I have a database where one table stores the bulk of logged data, it has 14 
columns, 11 integers and 3 reals.

Initially I'm gonna have to import a lot of data into this database - about 
9,000 entries per months worth of data.  I already know 3K entries takes about 
192KBytes in the db file.  The biggest problem I am having is insert speed.  I 
know the initial amount will cover 3-4 months worth of data that will need to 
be inserted.  Right now I'm clocking raw inserts of data w/ no unique checking 
at about 1 min 26 sec / 1K entries.  Is this about average, or is there a way I 
might be able to get a little more speed out of it?

Granted, I might be hitting a ceiling of any db api, but I was hoping to 
squeeze a little more speed if possible ;)  

-Tony