Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine
Regarding: "I found that win function FlushFileBuffers (used by SQLite) slows down performances,..." Perhaps you've already seen this message thread from 2005 below. Maybe FILE_FLAG_WRITE_THROUGH instead of FlushFileBuffers would benefit your particular application. (I don't know what would explain the 10x xp differences you're finding.) http://www.mail-archive.com/sqlite-users@sqlite.org/msg08121.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine
> I found that win function FlushFileBuffers (used by SQLite) slows down > performances, > but still don't have explanation why this funcion on some machines > works 10x slower then on others. > > I have used win xp sp2 on all machines where I have tested the > behaviour. Try using CodeAnalyst, see where the time is being spent.. - Sherief ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine
Check the third party processes running on the respective machines. Virus scanners, file indexing utilities, TortoiseSVN, etc. have been shown to adversely affect performance on Windows machines by locking the journal file at inopportune times. -Shane On Wed, Nov 5, 2008 at 6:36 AM, Darko Filipovic <[EMAIL PROTECTED]> wrote: > I found that win function FlushFileBuffers (used by SQLite) slows down > performances, > but still don't have explanation why this funcion on some machines works > 10x slower then on others. > > I have used win xp sp2 on all machines where I have tested the behaviour. > > > > > > From: Sherief N. Farouk <[EMAIL PROTECTED]> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Tuesday, November 4, 2008 5:53:16 PM > Subject: Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow > machine > > > The problem is that I'm getting better performance on AMD machine with > > ATA disk, > > but 10 times slower on DualCore machine with SATA disk (both disks are > > at 7200 rpm)... > > I don't understand the source of this behaviour... > > OS? Tried VSTS profiler, VTune or the free CodeAnalyst to look at where the > time is being spent? > > - Sherief > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine
I found that win function FlushFileBuffers (used by SQLite) slows down performances, but still don't have explanation why this funcion on some machines works 10x slower then on others. I have used win xp sp2 on all machines where I have tested the behaviour. From: Sherief N. Farouk <[EMAIL PROTECTED]> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Tuesday, November 4, 2008 5:53:16 PM Subject: Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine > The problem is that I'm getting better performance on AMD machine with > ATA disk, > but 10 times slower on DualCore machine with SATA disk (both disks are > at 7200 rpm)... > I don't understand the source of this behaviour... OS? Tried VSTS profiler, VTune or the free CodeAnalyst to look at where the time is being spent? - Sherief ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine
> The problem is that I'm getting better performance on AMD machine with > ATA disk, > but 10 times slower on DualCore machine with SATA disk (both disks are > at 7200 rpm)... > I don't understand the source of this behaviour... OS? Tried VSTS profiler, VTune or the free CodeAnalyst to look at where the time is being spent? - Sherief ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine
Thank you, but I am aware of transaction, and I explicitly need to perform inserts out of transaction... The problem is that I'm getting better performance on AMD machine with ATA disk, but 10 times slower on DualCore machine with SATA disk (both disks are at 7200 rpm)... I don't understand the source of this behaviour... From: John Stanton <[EMAIL PROTECTED]> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Tuesday, November 4, 2008 4:11:30 PM Subject: Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine First, wrap your inserts into a BEGIN...COMMIT transaction. Expect the machine with the fastest disk rotation to perform the inserts fastest. Darko Filipovic wrote: > Hello, > > I'm having following situation: > Table: > CREATE TABLE TEST_TABLE ( > COL1 INTEGER(20) NOT NULL, > COL2 INTEGER(20) NOT NULL, > COL3 REAL(30,10), > COL4 REAL(30,10), > COL5 REAL(30,10), > COL6 REAL(30,10), > COL7 REAL(30,10), > COL8 REAL(30,10), > COL9 REAL(30,10), > COL10 REAL(30,10), > COL11 DATETIME NOT NULL); > CREATE INDEX XI1_TEST_TABLE_COL11 ON TEST_TABLE (COL11); > CREATE INDEX XI2_TEST_TABLE_COL2 ON TEST_TABLE (COL2); > CREATE INDEX XI3_TEST_TABLE_COL1 ON TEST_TABLE (COL1); > > I'm doing following insert (100x) out of transaction: > INSERT INTO TEST_TABLEVALUES ( 1, 527047, 2902.9326171875, 1180.5153808594, > 177.3292846680, 850.6628417969, 0.00, 578.7066650391, > 2727.7397460938, 922.6400756836, '2008-10-29 13:22:06' ); > > I was doing this test on: > - DualCore 2.5GHz machine SATA2 disk and on > - AMD Athlon 1.8GHz ATA disk. > > I'm getting 10 times better performances on AMD machine, then on DualCore, > precisely, AMD machine took about 1 second to write 100 rows, and DualCore > machine took about 10 seconds. > > Can someone, please, explain to me this behaviour, or even better help me to > speed up DualCore test?! > > Thank you in advance! > Darko F. > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow INSERT on fast machine, fast INSERT on slow machine
First, wrap your inserts into a BEGIN...COMMIT transaction. Expect the machine with the fastest disk rotation to perform the inserts fastest. Darko Filipovic wrote: > Hello, > > I'm having following situation: > Table: > CREATE TABLE TEST_TABLE ( > COL1 INTEGER(20) NOT NULL, > COL2 INTEGER(20) NOT NULL, > COL3 REAL(30,10), > COL4 REAL(30,10), > COL5 REAL(30,10), > COL6 REAL(30,10), > COL7 REAL(30,10), > COL8 REAL(30,10), > COL9 REAL(30,10), > COL10 REAL(30,10), > COL11 DATETIME NOT NULL); > CREATE INDEX XI1_TEST_TABLE_COL11 ON TEST_TABLE (COL11); > CREATE INDEX XI2_TEST_TABLE_COL2 ON TEST_TABLE (COL2); > CREATE INDEX XI3_TEST_TABLE_COL1 ON TEST_TABLE (COL1); > > I'm doing following insert (100x) out of transaction: > INSERT INTO TEST_TABLEVALUES ( 1, 527047, 2902.9326171875, 1180.5153808594, > 177.3292846680, 850.6628417969, 0.00, 578.7066650391, > 2727.7397460938, 922.6400756836, '2008-10-29 13:22:06' ); > > I was doing this test on: > - DualCore 2.5GHz machine SATA2 disk and on > - AMD Athlon 1.8GHz ATA disk. > > I'm getting 10 times better performances on AMD machine, then on DualCore, > precisely, AMD machine took about 1 second to write 100 rows, and DualCore > machine took about 10 seconds. > > Can someone, please, explain to me this behaviour, or even better help me to > speed up DualCore test?! > > Thank you in advance! > Darko F. > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slow INSERT on fast machine, fast INSERT on slow machine
Hello, I'm having following situation: Table: CREATE TABLE TEST_TABLE ( COL1 INTEGER(20) NOT NULL, COL2 INTEGER(20) NOT NULL, COL3 REAL(30,10), COL4 REAL(30,10), COL5 REAL(30,10), COL6 REAL(30,10), COL7 REAL(30,10), COL8 REAL(30,10), COL9 REAL(30,10), COL10 REAL(30,10), COL11 DATETIME NOT NULL); CREATE INDEX XI1_TEST_TABLE_COL11 ON TEST_TABLE (COL11); CREATE INDEX XI2_TEST_TABLE_COL2 ON TEST_TABLE (COL2); CREATE INDEX XI3_TEST_TABLE_COL1 ON TEST_TABLE (COL1); I'm doing following insert (100x) out of transaction: INSERT INTO TEST_TABLEVALUES ( 1, 527047, 2902.9326171875, 1180.5153808594, 177.3292846680, 850.6628417969, 0.00, 578.7066650391, 2727.7397460938, 922.6400756836, '2008-10-29 13:22:06' ); I was doing this test on: - DualCore 2.5GHz machine SATA2 disk and on - AMD Athlon 1.8GHz ATA disk. I'm getting 10 times better performances on AMD machine, then on DualCore, precisely, AMD machine took about 1 second to write 100 rows, and DualCore machine took about 10 seconds. Can someone, please, explain to me this behaviour, or even better help me to speed up DualCore test?! Thank you in advance! Darko F. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
FW: [sqlite] slow "INSERT"
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,);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
return 1;
}
}
Where I'm wrong ?
Thanks, Ale
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
On Jan 31, 2005, at 10:49, Alessandro Renzi (RM/TEI) wrote:
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,);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
return 1;
}
}
Where I'm wrong ?
Thanks, Ale
Re: [sqlite] slow "INSERT"
You should use a transaction otherwise sqlite syncs the file for every INSERT.
Clive
"Alessandro Renzi (RM/TEI)" <[EMAIL PROTECTED]> on 31-01-2005
11:49:24
Please respond to sqlite-users@sqlite.org
To: "'sqlite-users@sqlite.org'" <sqlite-users@sqlite.org>
cc:(bcc: clive/Emultek)
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,);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
return 1;
}
}
Where I'm wrong ?
Thanks, Ale
[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,);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
return 1;
}
}
Where I'm wrong ?
Thanks, Ale
