Re: [sqlite] Generic speed testing
On Fri, Aug 15, 2008 at 11:19 PM, Noah Hart <[EMAIL PROTECTED]> wrote: > Greg, I intended that sqlite3 be launched without a filename, so this > will give a memory based database and disk I/O would not need to be > considered. If I remember correctly the default is an "anonymous" disk based database (without the fsync overhead), but can be a pure memory db if you pass the ":memory:" filename (and can be configured with the right option passed to the "configure" script or with the right "#define"). Also, IIRC, the memory db is a bit slower than the disk based one because it's not optimized for memory use (like using B+ Trees in memory instead of something like RB Trees, as it was on 2.8.x). This is all from memory, so someone correct me if I'm wrong. Regards, ~Nuno Lucas > > Regards -- Noah ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
- > -- TEST 3 > -- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL > > BEGIN; > INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT > 1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') - > 2440587.5)*86400 FROM TIMER; > UPDATE TEST1 SET I=I; > COMMIT; > UPDATE TIMER SET EndTime = (julianday('now') - > 2440587.5)*86400.0, Rows = changes() > WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); > > > -- TEST 4 > -- TRIVIAL DELETES > > BEGIN; > INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT > 1+MAX(TESTNUMBER), 'Trivial Deletes', (julianday('now') - > 2440587.5)*86400 FROM TIMER; > DELETE FROM TEST1 WHERE I >0; > COMMIT; > UPDATE TIMER SET EndTime = (julianday('now') - > 2440587.5)*86400.0, Rows = changes() > WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); > > -- > -- A LITTLE CLEANUP BEFORE WE CONTINUE > -- > > DROP TABLE TEST1; > CREATE TABLE TEST1 (I INTEGER, T TEXT); > PRAGMA page_count; > VACUUM; > PRAGMA page_count; > > > -- TEST 5 > -- INSERTS WITH CALCULATIONS -- SHOULD BE SLOWER THAN 1 > > BEGIN; > INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT > 1+MAX(TESTNUMBER), 'Insert with calculations', (julianday('now') - > 2440587.5)*86400 FROM TIMER; > INSERT INTO TEST1 SELECT N1.I*N2.I+N3.I, N1.T||N2.T||N3.T FROM N_2 > N1 CROSS JOIN N_2 N2 CROSS JOIN N_2 N3; > COMMIT; > UPDATE TIMER SET EndTime = (julianday('now') - > 2440587.5)*86400.0, Rows = changes() > WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); > > > -- TEST 6 > -- UPDATES WITH CALCULATIONS -- SHOULD BE SLOWER THAN 2 > > BEGIN; > INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT > 1+MAX(TESTNUMBER), 'Updates with calculations and longer rows', > (julianday('now') - 2440587.5)*86400 FROM TIMER; > UPDATE TEST1 SET I=I*1+2-3; > COMMIT; > UPDATE TIMER SET EndTime = (julianday('now') - > 2440587.5)*86400.0, Rows = changes() > WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); > > --- > -- REPORT THE RESULTS > > Select TestNumber, Description, ROUND(EndTime- StartTime,2), > Rows, Round(Rows/(EndTime-StartTime)/1000)||'K Rows/Second' from TIMER; > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt > Sent: Friday, August 15, 2008 2:03 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Generic speed testing > > On Thu, 14 Aug 2008 13:25:56 -0700, you wrote: > >> I'm not sure if this will even be a valid comparison, so your feedback >> and initial numbers are appreciated. >> >> Please reply with your data as follows > > (v1.1 without the DROP TABLE TEST1 statement): > > sqlite 3.6.0 running on Acer Aspire 9423 under "MS Windows > Vista 32bit on Intel Core 2 Duo T5500 1.66GHz, 667 MHz FSB, > 2GB DDR2 RAM, Hitachi HTS541616J9SA00 SATA 5400RPM with 8MB > buffer, write caching enabled" > > 0|performance.txt,v 1.1|1.0|0|0.0K Rows/Second > 1|Trivial Inserts|31.41|10077696|321.0K Rows/Second > 2|Trivial Selects|2.06|10077696|4887.0K Rows/Second > 3|Trivial Updates|142.87|10077696|71.0K Rows/Second > 4|Trivial Deletes|41.39|10077696|243.0K Rows/Second > 5|Insert with calculations|61.81|10077696|163.0K Rows/Second > 6|Updates with calculations and longer > rows|383.63|10077696|26.0K Rows/Second > > I might be able to squeeze more performance out of this box. > > Hint: > For a genuinely level playing field you'd have to use more > PRAGMAs, like page size and cache size. Defaults might be > different in different environments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Greg, I intended that sqlite3 be launched without a filename, so this will give a memory based database and disk I/O would not need to be considered. Regards -- Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: Friday, August 15, 2008 2:52 PM To: [EMAIL PROTECTED]; General Discussion of SQLite Database Subject: Re: [sqlite] Generic speed testing Regarding: "On my AMD system the tests seem to be CPU bound." On that note, I believe the test creates a 625 megabyte database before deleting most of it and vacuuming down to a tiny size. So I guess included in the test is not just one's disk speed, but how fast one's operating system can allocate the space and how fragmented the result is. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Ken, I'm not really sure what I want to test, or rather what would be a meaningful test, so I wanted to start a public discussion about relative performances. All systems will have limits in some way based on CPU, memory and disk. But an interesting question to me is sqlite whether sqlite is more efficient (whatever that means) on one platform vs. another. This first script was intended to focus on CPU which (with enough data) should give a nice baseline for further testing on various platforms. I should also note that it is intended that sqlite3 be launched without a filename, since this will give a memory based database, ignoring all I/O Regards -- Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ken Sent: Friday, August 15, 2008 2:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Generic speed testing Noah, really nice job with this tool. I find it quire useful just to get a relative performance comparison between my hardware systems. On my AMD system the tests seem to be CPU bound. But this is a DB, My concern is that this is really more of a CPU stress test than an I/O DB test. That begs the question, What do you really want to test? CPU prepare/parse with calculations ? Or select insert/update/delete that Is more I/O bound (what about indexing ?) Maybe think of creating another test suite that stresses i/o subsystem and does not run complex calculations. HTH Ken --- On Fri, 8/15/08, Noah Hart <[EMAIL PROTECTED]> wrote: From: Noah Hart <[EMAIL PROTECTED]> Subject: Re: [sqlite] Generic speed testing To: "General Discussion of SQLite Database" Date: Friday, August 15, 2008, 4:34 PM Good Point Ken, Here is version 1.2 with the missing CREATE TABLE statement and some new PRAGMA settings. Any suggestions for the CACHE_SIZE setting? Also -- I can see how to modify an existing Wiki page, but does anyone know how to create a new Wiki page, so I can put this script there rather than repeating it in email? Noah SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram, 5000rpmDisk" 0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second 1|Trivial Inserts|16.31|10077696|618.0K Rows/Second 2|Trivial Selects|0.85|10077696|11898.0K Rows/Second 3|Trivial Updates|82.41|10077696|122.0K Rows/Second 4|Trivial Deletes|23.32|10077696|432.0K Rows/Second 5|Insert with calculations|29.02|10077696|347.0K Rows/Second 6|Updates with calculations and longer rows|54.52|10077696|185.0K Rows/Second -- -- The author disclaims copyright to this source code. In place of -- a legal notice, here is a blessing: -- --May you do good and not evil. --May you find forgiveness for yourself and forgive others. --May you share freely, never taking more than you give. -- --** * -- This file contains code used to implement the performance scripts -- -- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $ -- -- LEVEL THE PLAYING FIELD WITH PRAGMAs -- PRAGMA auto_vacuum = NONE; PRAGMA cache_size = 2; PRAGMA count_changes = 1; PRAGMA encoding = "UTF-8"; PRAGMA fullfsync = 0; PRAGMA journal_mode = DELETE; PRAGMA locking_mode = EXCLUSIVE; PRAGMA page_size = 1024; PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY; -- -- A LITTLE SETUP BEFORE WE BEGIN -- CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL); INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0); CREATE TABLE TEST1 (I INTEGER, T TEXT); CREATE TABLE N_1(i INTEGER, t TEXT); INSERT INTO N_1 VALUES(1, 't1_'); INSERT INTO N_1 VALUES(2, 't_22_'); INSERT INTO N_1 VALUES(3, 'tx_3_3_3_'); INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_'); INSERT INTO N_1 VALUES(5, 'text_555_'); INSERT INTO N_1 VALUES(6, ' '); CREATE TABLE N_2(i INTEGER, t TEXT); INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS JOIN N_1 N2 CROSS JOIN N_1 N3; -- TEST 1 -- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL BEGIN; INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') - 2440587.5)*86400 FROM TIMER; INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS JOIN N_2 N3; COMMIT; UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes()
Re: [sqlite] Generic speed testing
Regarding: "On my AMD system the tests seem to be CPU bound." On that note, I believe the test creates a 625 megabyte database before deleting most of it and vacuuming down to a tiny size. So I guess included in the test is not just one's disk speed, but how fast one's operating system can allocate the space and how fragmented the result is. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Noah, really nice job with this tool. I find it quire useful just to get a relative performance comparison between my hardware systems. On my AMD system the tests seem to be CPU bound. But this is a DB, My concern is that this is really more of a CPU stress test than an I/O DB test. That begs the question, What do you really want to test? CPU prepare/parse with calculations ? Or select insert/update/delete that Is more I/O bound (what about indexing ?) Maybe think of creating another test suite that stresses i/o subsystem and does not run complex calculations. HTH Ken --- On Fri, 8/15/08, Noah Hart <[EMAIL PROTECTED]> wrote: From: Noah Hart <[EMAIL PROTECTED]> Subject: Re: [sqlite] Generic speed testing To: "General Discussion of SQLite Database" Date: Friday, August 15, 2008, 4:34 PM Good Point Ken, Here is version 1.2 with the missing CREATE TABLE statement and some new PRAGMA settings. Any suggestions for the CACHE_SIZE setting? Also -- I can see how to modify an existing Wiki page, but does anyone know how to create a new Wiki page, so I can put this script there rather than repeating it in email? Noah SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram, 5000rpmDisk" 0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second 1|Trivial Inserts|16.31|10077696|618.0K Rows/Second 2|Trivial Selects|0.85|10077696|11898.0K Rows/Second 3|Trivial Updates|82.41|10077696|122.0K Rows/Second 4|Trivial Deletes|23.32|10077696|432.0K Rows/Second 5|Insert with calculations|29.02|10077696|347.0K Rows/Second 6|Updates with calculations and longer rows|54.52|10077696|185.0K Rows/Second -- -- The author disclaims copyright to this source code. In place of -- a legal notice, here is a blessing: -- --May you do good and not evil. --May you find forgiveness for yourself and forgive others. --May you share freely, never taking more than you give. -- --** * -- This file contains code used to implement the performance scripts -- -- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $ -- -- LEVEL THE PLAYING FIELD WITH PRAGMAs -- PRAGMA auto_vacuum = NONE; PRAGMA cache_size = 2; PRAGMA count_changes = 1; PRAGMA encoding = "UTF-8"; PRAGMA fullfsync = 0; PRAGMA journal_mode = DELETE; PRAGMA locking_mode = EXCLUSIVE; PRAGMA page_size = 1024; PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY; -- -- A LITTLE SETUP BEFORE WE BEGIN -- CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL); INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0); CREATE TABLE TEST1 (I INTEGER, T TEXT); CREATE TABLE N_1(i INTEGER, t TEXT); INSERT INTO N_1 VALUES(1, 't1_'); INSERT INTO N_1 VALUES(2, 't_22_'); INSERT INTO N_1 VALUES(3, 'tx_3_3_3_'); INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_'); INSERT INTO N_1 VALUES(5, 'text_555_'); INSERT INTO N_1 VALUES(6, ' '); CREATE TABLE N_2(i INTEGER, t TEXT); INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS JOIN N_1 N2 CROSS JOIN N_1 N3; -- TEST 1 -- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL BEGIN; INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') - 2440587.5)*86400 FROM TIMER; INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS JOIN N_2 N3; COMMIT; UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes() WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); -- TEST 2 -- TRIVIAL SELECTS INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') - 2440587.5)*86400 FROM TIMER; UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid > 0) WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); -- TEST 3 -- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL BEGIN; INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(
Re: [sqlite] Generic speed testing
LECT MAX(TESTNUMBER) FROM TIMER); -- -- A LITTLE CLEANUP BEFORE WE CONTINUE -- DROP TABLE TEST1; CREATE TABLE TEST1 (I INTEGER, T TEXT); PRAGMA page_count; VACUUM; PRAGMA page_count; -- TEST 5 -- INSERTS WITH CALCULATIONS -- SHOULD BE SLOWER THAN 1 BEGIN; INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Insert with calculations', (julianday('now') - 2440587.5)*86400 FROM TIMER; INSERT INTO TEST1 SELECT N1.I*N2.I+N3.I, N1.T||N2.T||N3.T FROM N_2 N1 CROSS JOIN N_2 N2 CROSS JOIN N_2 N3; COMMIT; UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes() WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); -- TEST 6 -- UPDATES WITH CALCULATIONS -- SHOULD BE SLOWER THAN 2 BEGIN; INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Updates with calculations and longer rows', (julianday('now') - 2440587.5)*86400 FROM TIMER; UPDATE TEST1 SET I=I*1+2-3; COMMIT; UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes() WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); --- -- REPORT THE RESULTS Select TestNumber, Description, ROUND(EndTime- StartTime,2), Rows, Round(Rows/(EndTime-StartTime)/1000)||'K Rows/Second' from TIMER; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt Sent: Friday, August 15, 2008 2:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Generic speed testing On Thu, 14 Aug 2008 13:25:56 -0700, you wrote: >I'm not sure if this will even be a valid comparison, so your feedback >and initial numbers are appreciated. > >Please reply with your data as follows (v1.1 without the DROP TABLE TEST1 statement): sqlite 3.6.0 running on Acer Aspire 9423 under "MS Windows Vista 32bit on Intel Core 2 Duo T5500 1.66GHz, 667 MHz FSB, 2GB DDR2 RAM, Hitachi HTS541616J9SA00 SATA 5400RPM with 8MB buffer, write caching enabled" 0|performance.txt,v 1.1|1.0|0|0.0K Rows/Second 1|Trivial Inserts|31.41|10077696|321.0K Rows/Second 2|Trivial Selects|2.06|10077696|4887.0K Rows/Second 3|Trivial Updates|142.87|10077696|71.0K Rows/Second 4|Trivial Deletes|41.39|10077696|243.0K Rows/Second 5|Insert with calculations|61.81|10077696|163.0K Rows/Second 6|Updates with calculations and longer rows|383.63|10077696|26.0K Rows/Second I might be able to squeeze more performance out of this box. Hint: For a genuinely level playing field you'd have to use more PRAGMAs, like page size and cache size. Defaults might be different in different environments. -- ( Kees Nuyt ) c[_] CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
On Thu, 14 Aug 2008 13:25:56 -0700, you wrote: >I'm not sure if this will even be a valid comparison, so your feedback >and initial numbers are appreciated. > >Please reply with your data as follows (v1.1 without the DROP TABLE TEST1 statement): sqlite 3.6.0 running on Acer Aspire 9423 under "MS Windows Vista 32bit on Intel Core 2 Duo T5500 1.66GHz, 667 MHz FSB, 2GB DDR2 RAM, Hitachi HTS541616J9SA00 SATA 5400RPM with 8MB buffer, write caching enabled" 0|performance.txt,v 1.1|1.0|0|0.0K Rows/Second 1|Trivial Inserts|31.41|10077696|321.0K Rows/Second 2|Trivial Selects|2.06|10077696|4887.0K Rows/Second 3|Trivial Updates|142.87|10077696|71.0K Rows/Second 4|Trivial Deletes|41.39|10077696|243.0K Rows/Second 5|Insert with calculations|61.81|10077696|163.0K Rows/Second 6|Updates with calculations and longer rows|383.63|10077696|26.0K Rows/Second I might be able to squeeze more performance out of this box. Hint: For a genuinely level playing field you'd have to use more PRAGMAs, like page size and cache size. Defaults might be different in different environments. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
On Thu, 14 Aug 2008 13:25:56 -0700, you wrote: >-- >-- A LITTLE CLEANUP BEFORE WE CONTINUE >-- > > DROP TABLE TEST1; I don't think you really want to drop TEST1. We'll need it later. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Windows XP SP2, SQLite 3.6.1, Intel T2400 (1.83GHZ) Dual Core, 2Gb RAM, 5000RPM Drive SQL error near line 112: no such table: TEST1 SQL error near line 127: no such table: TEST1 0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second 1|Trivial Inserts|21.17|10077696|476.0K Rows/Second 2|Trivial Selects|1.61|10077696|6259.0K Rows/Second 3|Trivial Updates|105.89|10077696|95.0K Rows/Second 4|Trivial Deletes|28.31|10077696|356.0K Rows/Second 5|Insert with calculations|0.0|1| 6|Updates with calculations and longer rows|0.0|1| Not sure why the last two results didn't print with "Rows/Second" column. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Ubuntu 7.10, SQLite 3.5.9 (debugging turned on), Intel Pentium E2140 (1.60GHz) Dual Core, 2Gb RAM, 7200RPM Drive SQL error near line 111: no such table: TEST1 SQL error near line 126: no such table: TEST1 0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second 1|Trivial Inserts|21.56|10077696|468.0K Rows/Second 2|Trivial Selects|1.43|10077696|7037.0K Rows/Second 3|Trivial Updates|113.97|10077696|88.0K Rows/Second 4|Trivial Deletes|33.59|10077696|300.0K Rows/Second 5|Insert with calculations|0.0|1|2.0K Rows/Second 6|Updates with calculations and longer rows|0.0|1|2.0K Rows/Second ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Linux: Sqlite 3.5.9, AMD x64 3800 (2ghz) dual core, 2gb RAM, 7200rpm drive. SQL error near line 100: no such table: TEST1 SQL error near line 115: no such table: TEST1 TestNumber|Description|ROUND(EndTime- StartTime,2)|Rows|Round(Rows/(EndTime-StartTime)/1000)||'K Rows/Second' 0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second 1|Trivial Inserts|24.34|10077696|414.0K Rows/Second 2|Trivial Selects|1.6|10077696|6286.0K Rows/Second 3|Trivial Updates|130.56|10077696|77.0K Rows/Second 4|Trivial Deletes|35.13|10077696|287.0K Rows/Second 5|Insert with calculations|0.0|1|2.0K Rows/Second 6|Updates with calculations and longer rows|0.0|1|2.0K Rows/Second Interesting that your hard drive is 5000rpm but yet your getting x2 performance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Generic speed testing
After looking at the code for speed test #1-4, I've decided that a command line version that does not use TCL would be better. Wanting to keep things simple, I'm looking only at inserts, selections and deletes. I'm not sure if this will even be a valid comparison, so your feedback and initial numbers are appreciated. Please reply with your data as follows SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram, 5000rpmDisk" 0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second 1|Trivial Inserts|16.12|10077696|625.0K Rows/Second 2|Trivial Selects|0.87|10077696|11650.0K Rows/Second 3|Trivial Updates|81.69|10077696|123.0K Rows/Second 4|Trivial Deletes|22.17|10077696|455.0K Rows/Second 5|Insert with calculations|29.02|10077696|347.0K Rows/Second 6|Updates with calculations and longer rows|54.52|10077696|185.0K Rows/Second Regards, Noah Performance script version 1 follows: -- -- The author disclaims copyright to this source code. In place of -- a legal notice, here is a blessing: -- --May you do good and not evil. --May you find forgiveness for yourself and forgive others. --May you share freely, never taking more than you give. -- --** * -- This file contains code used to implement the performance scripts -- -- $Id: performance.txt,v 1.0 2008/08/14 12:50:00 nbh Exp $ PRAGMA SYNCHRONIZATION = FULL; PRAGMA locking_mode = EXCLUSIVE; PRAGMA synchronous = OFF; -- -- A LITTLE SETUP BEFORE WE BEGIN -- CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL); INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.0', 0, 1, 0); CREATE TABLE TEST1 (I INTEGER, T TEXT); CREATE TABLE N_1(i INTEGER, t TEXT); INSERT INTO N_1 VALUES(1, 't1_'); INSERT INTO N_1 VALUES(2, 't_22_'); INSERT INTO N_1 VALUES(3, 'tx_3_3_3_'); INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_'); INSERT INTO N_1 VALUES(5, 'text_555_'); INSERT INTO N_1 VALUES(6, ' '); CREATE TABLE N_2(i INTEGER, t TEXT); INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS JOIN N_1 N2 CROSS JOIN N_1 N3; -- TEST 1 -- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL BEGIN; INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') - 2440587.5)*86400 FROM TIMER; INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS JOIN N_2 N3; COMMIT; UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes() WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); -- TEST 2 -- TRIVIAL SELECTS INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') - 2440587.5)*86400 FROM TIMER; UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid > 0) WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); -- TEST 3 -- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL BEGIN; INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') - 2440587.5)*86400 FROM TIMER; UPDATE TEST1 SET I=I; COMMIT; UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes() WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); -- TEST 4 -- TRIVIAL DELETES BEGIN; INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Deletes', (julianday('now') - 2440587.5)*86400 FROM TIMER; DELETE FROM TEST1 WHERE I >0; COMMIT; UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes() WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER); -- -- A LITTLE CLEANUP BEFORE WE CONTINUE -- DROP TABLE TEST1; PRAGMA page_count; VACUUM; PRAGMA page_count; -- TEST 5 -- INSERTS WITH CALCULATIONS -- SHOULD BE SLOWER THAN 1 BEGIN; INSERT INTO