wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 --
2 -- The author disclaims copyright to this source code. In place of
3 -- a legal notice, here is a blessing:
4 --
5 -- May you do good and not evil.
6 -- May you find forgiveness for yourself and forgive others.
7 -- May you share freely, never taking more than you give.
8 --
9 -----------------------------------------------------------------------------------------
10 -- This file contains code used to implement the performance scripts
11 --
12 -- Repository path: $HeadURL: https://sqlitecs.googlecode.com/svn/trunk/test/performance.sql $
13 -- Last Revised : $Revision: 62 $
14 -- Last Changed By : $LastChangedBy: noah.hart $
15 -- Last Changed Date : $LastChangedDate: 2009-08-03 09:19:48 -0700 (Mon, 03 Aug 2009) $
16 -----------------------------------------------------------------------------------------
17 ---------------------------------------------------------------------
18 --
19 -- NOTES:
20 --
21 ---------------------------------------------------------------------
22  
23 ------------------------------------------
24 -- LEVEL THE PLAYING FIELD WITH PRAGMAs
25 ------------------------------------------
26  
27 PRAGMA auto_vacuum = NONE;
28 PRAGMA cache_size = 20000;
29 PRAGMA count_changes = 1;
30 PRAGMA encoding = "UTF-8";
31 PRAGMA fullfsync = 0;
32 PRAGMA journal_mode = NONE;
33 PRAGMA locking_mode = EXCLUSIVE;
34 PRAGMA page_size = 1024;
35 PRAGMA synchronous = OFF;
36 PRAGMA temp_store = MEMORY;
37 ------------------------------------------
38 -- A LITTLE SETUP BEFORE WE BEGIN
39 ------------------------------------------
40  
41 ATTACH ':memory:' as tDB;
42 CREATE TABLE tDB.TIMER(TestNumber INTEGER, Description TEXT, StartTime REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
43 INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.3', 0, 1, 0);
44 CREATE TABLE tDB.TEST1 (I INTEGER, T TEXT);
45 CREATE TABLE N_1(i INTEGER, t TEXT);
46 INSERT INTO N_1 VALUES(1, 't1_');
47 INSERT INTO N_1 VALUES(2, 't_22_');
48 INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
49 INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
50 CREATE TABLE N_2(i INTEGER, t TEXT);
51 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;
52  
53 --------------------------------------------------------
54 -- TEST 1
55 -- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL
56 --------------------------------------------------------
57 BEGIN;
58 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') - 2440587.5)*86400 FROM TIMER;
59 INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS JOIN N_2 N3;
60 COMMIT;
61  
62 UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes()
63 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
64  
65 --------------------------------------------------------
66 -- TEST 2
67 -- TRIVIAL SELECTS
68 --------------------------------------------------------
69 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') - 2440587.5)*86400 FROM TIMER;
70 UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid > 0)
71 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
72 UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0
73 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
74  
75 --------------------------------------------------------
76 -- TEST 3
77 -- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL
78 --------------------------------------------------------
79 BEGIN;
80 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') - 2440587.5)*86400 FROM TIMER;
81 UPDATE TEST1 SET I=I;
82 COMMIT;
83 UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes()
84 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
85  
86 --------------------------------------------------------
87 -- TEST 4
88 -- TRIVIAL DELETES
89 --------------------------------------------------------
90 BEGIN;
91 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Trivial Deletes', (julianday('now') - 2440587.5)*86400 FROM TIMER;
92 DELETE FROM TEST1 WHERE I >0;
93 COMMIT;
94 UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes()
95 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
96  
97 ------------------------------------------
98 -- A LITTLE CLEANUP BEFORE WE CONTINUE
99 ------------------------------------------
100  
101 DROP TABLE TEST1;
102 CREATE TABLE tDB.TEST1 (I INTEGER, T TEXT);
103 PRAGMA page_count;
104 VACUUM;
105 PRAGMA page_count;
106  
107 --------------------------------------------------------
108 -- TEST 5
109 -- INSERTS WITH CALCULATIONS -- SHOULD BE SLOWER THAN 1
110 --------------------------------------------------------
111 BEGIN;
112 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Insert with calculations', (julianday('now') - 2440587.5)*86400 FROM TIMER;
113 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;
114 COMMIT;
115 UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes()
116 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
117  
118 --------------------------------------------------------
119 -- TEST 6
120 -- UPDATES WITH CALCULATIONS -- SHOULD BE SLOWER THAN 2
121 --------------------------------------------------------
122 BEGIN;
123 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT 1+MAX(TESTNUMBER), 'Updates with calculations and longer rows', (julianday('now') - 2440587.5)*86400 FROM TIMER;
124 UPDATE TEST1 SET I=I*1+2-3;
125 COMMIT;
126 UPDATE TIMER SET EndTime = (julianday('now') - 2440587.5)*86400.0, Rows = changes()
127 WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
128  
129 -----------------------------------------------
130 -- REPORT THE RESULTS
131 --------------------------------------------------------
132 Select TestNumber, Description, ROUND(EndTime- StartTime,2), Rows, Round(Rows/(EndTime-StartTime)/1000)||'K Rows/Second' from TIMER;
133  
134