wasCSharpSQLite – Blame information for rev
?pathlinks?
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 |