FW: [sqlite] slow "INSERT"

[Alessandro Renzi (RM/TEI)]

Thanks to everyone,
I'll have to study more but I've already tried some of your suggestions and now 
sqlite
behaviour is very, very good.
/Ale


-----Original Message-----
From: Tito Ciuro [mailto:[EMAIL PROTECTED]
Sent: luned́ 31 gennaio 2005 11.06
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] slow "INSERT"

Alessandro,

Take a few minutes to read the different wiki pages. There is a lot of 
info there. Answering your question:

http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning

Just scroll at the end of the page: Use transactions when updating 
tables. Another reference:

http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

Regards,

-- Tito

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: luned́ 31 gennaio 2005 10.58
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] slow "INSERT"

You should use a transaction otherwise sqlite syncs the file for every INSERT.

Clive

-----Original Message-----
From: Ionut Filip [mailto:[EMAIL PROTECTED]
Sent: luned́ 31 gennaio 2005 11.04
To: Alessandro Renzi (RM/TEI)
Subject: RE: [sqlite] slow "INSERT"


Hi Ale,

Your code is correct, however there are two things you should be aware:

1. With SQLite if you don't use explicit transactions every INSERT will
have it's own transaction. This means data will be flushed to disk after
each insert. Use a single transactions for all inserts ("BEGIN
TRANSACTION" / "END TRANSACTION").

2. For every insert that you made now, the SQL statement is parsed, this
is obviously not efficient. Use instead prepared (or precompiled)
statements. See http://www.sqlite.org/docs.html for details.

By doing this two things you'll get speeds as in
http://www.sqlite.org/speed.html .

Ionut


-----Original Message-----
From: Alessandro Renzi (RM/TEI) [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 31, 2005 11:49 AM
To: 'sqlite-users@sqlite.org'
Subject: [sqlite] slow "INSERT"


Hi,
I'm a new sqlite user.
For sure I'm doing something wrong with sqlite because it takes about 1
second to perform 10 INSERT operation ! If instead I use the sqlite client
and read an external file with thousands of  INSERT operation the updating
is almost immediate. The test program I'm using is the following one:

        char * sql = "INSERT INTO PEOPLE (NAME, AGE) VALUES
('John','25')";
        for (int i=0; i<100; i++) {
                rc = sqlite_exec(db,sql,callback,0,&zErrMsg);
                if( rc!=SQLITE_OK ){
                        fprintf(stderr, "SQL error: %s\n", zErrMsg);
                        return 1;
                }
        }

Where I'm wrong ?
Thanks, Ale