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
