Ubuntu 7.10, SQLite 3.5.9 (debugging turned on), Intel Pentium E2140
(1.60GHz) Dual Core, 2Gb RAM, 7200RPM Drive
Bunch of crud to stdout followed by:
0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
1|Trivial Inserts|21.52|10077696|468.0K Rows/Second
2|Trivial Selects|1.43|10077696|7046.0K Rows/Second
3|Trivial Updates|114.01|10077696|88.0K Rows/Second
4|Trivial Deletes|32.91|10077696|306.0K Rows/Second
5|Insert with calculations|40.94|10077696|246.0K Rows/Second
6|Updates with calculations and longer rows|89.98|10077696|112.0K
Rows/Second
Noah Hart wrote:
> 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 = 20000;
> 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_55555555555_');
> 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;
> 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
