wasCSharpSQLite – Blame information for rev
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | // $Header$ |
2 | |||
3 | using System; |
||
4 | using System.Data; |
||
5 | using System.Data.SQLite; |
||
6 | using System.Diagnostics; |
||
7 | using System.IO; |
||
8 | using Community.CsharpSqlite; |
||
9 | |||
10 | /* |
||
11 | * Benchmark Test for both SQLite and C#-SQLite |
||
12 | */ |
||
13 | |||
14 | public class Benchmark |
||
15 | { |
||
16 | private static int nRecords; |
||
17 | |||
18 | private static string[] PRAGMA_Commands = { |
||
19 | "PRAGMA synchronous = OFF", |
||
20 | "PRAGMA temp_store = MEMORY", |
||
21 | "PRAGMA journal_mode = OFF" , |
||
22 | "PRAGMA locking_mode=EXCLUSIVE" |
||
23 | }; |
||
24 | |||
25 | private static string[] CREATE_Commands = { |
||
26 | "CREATE TABLE Root (intIndex INTEGER PRIMARY KEY, strIndex TEXT)", |
||
27 | "CREATE INDEX RootStrIndex ON Root (strIndex)" |
||
28 | }; |
||
29 | |||
30 | private static string INSERT_Command = "INSERT INTO Root VALUES (?,?)"; |
||
31 | private static string SELECT_Bind_i = "SELECT * FROM Root WHERE intIndex = ?"; |
||
32 | private static string SELECT_Bind_s = "SELECT * FROM Root WHERE strIndex = ?"; |
||
33 | |||
34 | private static string SELECT_Command_i = "SELECT * FROM Root ORDER BY intIndex"; |
||
35 | private static string SELECT_Command_s = "SELECT * FROM Root ORDER BY strIndex"; |
||
36 | |||
37 | private static string DELETE_Bind = "DELETE FROM Root WHERE intIndex = ?"; |
||
38 | |||
39 | private static long[,] timer = new long[2, 4]; |
||
40 | |||
41 | private static string databaseName; |
||
42 | |||
43 | public static void Main() |
||
44 | { |
||
45 | for ( nRecords = 10000; nRecords <= 200000; nRecords *= 2 ) |
||
46 | { |
||
47 | databaseName = "Benchmark_cs-SQLite.sqlite"; |
||
48 | TestSQLite(); |
||
49 | // |
||
50 | databaseName = "Benchmark_cs-Sqlite3.sqlite"; |
||
51 | TestCsharpSqlite(); |
||
52 | // |
||
53 | PrintStats( nRecords ); |
||
54 | } |
||
55 | Console.WriteLine( "Enter to Continue: " ); |
||
56 | Console.ReadKey(); |
||
57 | } |
||
58 | |||
59 | private static void TestCsharpSqlite() |
||
60 | { |
||
61 | SQLiteDatabase db; |
||
62 | SQLiteVdbe stmt; |
||
63 | SQLiteVdbe c1, c2; |
||
64 | |||
65 | bool found; |
||
66 | int i; |
||
67 | |||
68 | string databaseName = "Benchmark_cs-SQLite.sqlite"; |
||
69 | if ( File.Exists( databaseName ) ) File.Delete( databaseName ); |
||
70 | |||
71 | db = new SQLiteDatabase( databaseName ); |
||
72 | for ( i = 0; i < PRAGMA_Commands.Length; i++ ) { db.ExecuteNonQuery( PRAGMA_Commands[i] ); } |
||
73 | |||
74 | db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); |
||
75 | for ( i = 0; i < CREATE_Commands.Length; i++ ) { db.ExecuteNonQuery( CREATE_Commands[i] ); } |
||
76 | stmt = new SQLiteVdbe( db, INSERT_Command ); |
||
77 | long start = DateTime.Now.Ticks; |
||
78 | long key = 1999; |
||
79 | for ( i = 0; i < nRecords; i++ ) |
||
80 | { |
||
81 | key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
||
82 | stmt.Reset(); |
||
83 | stmt.BindLong( 1, key ); |
||
84 | stmt.BindText( 2, key.ToString() ); |
||
85 | stmt.ExecuteStep(); |
||
86 | } |
||
87 | stmt.Close(); |
||
88 | db.ExecuteNonQuery( "END" ); |
||
89 | timer[1, 0] = DateTime.Now.Ticks - start; |
||
90 | |||
91 | db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); |
||
92 | start = DateTime.Now.Ticks; |
||
93 | c1 = new SQLiteVdbe( db, SELECT_Bind_i ); |
||
94 | c2 = new SQLiteVdbe( db, SELECT_Bind_s ); |
||
95 | key = 1999; |
||
96 | for ( i = 0; i < nRecords; i++ ) |
||
97 | { |
||
98 | key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
||
99 | c1.Reset(); |
||
100 | c1.BindLong( 1, key ); |
||
101 | c1.ExecuteStep(); |
||
102 | |||
103 | c2.Reset(); |
||
104 | c2.BindText( 1, key.ToString() ); |
||
105 | c2.ExecuteStep(); |
||
106 | |||
107 | long id = (long)c1.Result_Long( 0 ); |
||
108 | Debug.Assert( id == (long)c2.Result_Long( 0 ) ); |
||
109 | |||
110 | } |
||
111 | c1.Close(); |
||
112 | c2.Close(); |
||
113 | db.ExecuteNonQuery( "END" ); |
||
114 | timer[1, 1] = DateTime.Now.Ticks - start; |
||
115 | |||
116 | db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); |
||
117 | start = DateTime.Now.Ticks; |
||
118 | key = Int64.MinValue; |
||
119 | i = 0; |
||
120 | c1 = new SQLiteVdbe( db, SELECT_Command_i ); |
||
121 | while ( c1.ExecuteStep() != Sqlite3.SQLITE_DONE ) |
||
122 | { |
||
123 | long intKey = (long)c1.Result_Long( 0 ); |
||
124 | Debug.Assert( intKey >= key ); |
||
125 | key = intKey; |
||
126 | i += 1; |
||
127 | } |
||
128 | c1.Close(); |
||
129 | Debug.Assert( i == nRecords ); |
||
130 | |||
131 | String strKey = ""; |
||
132 | i = 0; |
||
133 | c2 = new SQLiteVdbe( db, SELECT_Command_s ); |
||
134 | while ( c2.ExecuteStep() != Sqlite3.SQLITE_DONE ) |
||
135 | { |
||
136 | string recStrKey = (string)c2.Result_Text( 1 ); |
||
137 | Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 ); |
||
138 | strKey = recStrKey; |
||
139 | i += 1; |
||
140 | } |
||
141 | c2.Close(); |
||
142 | Debug.Assert( i == nRecords ); |
||
143 | timer[1, 2] = DateTime.Now.Ticks - start; |
||
144 | db.ExecuteNonQuery( "END" ); |
||
145 | |||
146 | db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); |
||
147 | start = DateTime.Now.Ticks; |
||
148 | key = 1999; |
||
149 | stmt = new SQLiteVdbe( db, DELETE_Bind ); |
||
150 | for ( i = 0; i < nRecords; i++ ) |
||
151 | { |
||
152 | key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
||
153 | stmt.Reset(); |
||
154 | stmt.BindLong( 1, key ); |
||
155 | stmt.ExecuteStep(); |
||
156 | } |
||
157 | stmt.Close(); |
||
158 | db.ExecuteNonQuery( "END" ); |
||
159 | timer[1, 3] = DateTime.Now.Ticks - start; |
||
160 | db.CloseDatabase(); |
||
161 | #if NET_35 |
||
162 | Sqlite3.Shutdown(); |
||
163 | #else |
||
164 | Sqlite3.sqlite3_shutdown(); |
||
165 | #endif |
||
166 | } |
||
167 | private static void TestSQLite() |
||
168 | { |
||
169 | int i; |
||
170 | string databaseName = "Benchmark_SQLite.sqlite"; |
||
171 | if ( File.Exists( databaseName ) ) File.Delete( databaseName ); |
||
172 | |||
173 | SQLiteConnectionStringBuilder constring = new SQLiteConnectionStringBuilder(); |
||
174 | constring.PageSize = 1024; |
||
175 | constring.SyncMode = SynchronizationModes.Off; |
||
176 | constring.DataSource = databaseName; |
||
177 | |||
178 | SQLiteConnection con = new SQLiteConnection( constring.ToString() ); |
||
179 | con.Open(); |
||
180 | SQLiteCommand com = con.CreateCommand(); |
||
181 | for ( i = 0; i < PRAGMA_Commands.Length; i++ ) |
||
182 | { |
||
183 | com.CommandText = PRAGMA_Commands[i]; |
||
184 | com.ExecuteNonQuery(); |
||
185 | } |
||
186 | for ( i = 0; i < CREATE_Commands.Length; i++ ) |
||
187 | { |
||
188 | com.CommandText = CREATE_Commands[i]; |
||
189 | com.ExecuteNonQuery(); |
||
190 | } |
||
191 | |||
192 | com.CommandText = "BEGIN EXCLUSIVE"; |
||
193 | com.ExecuteNonQuery(); |
||
194 | |||
195 | com.CommandText = "INSERT INTO Root VALUES (?,?)"; |
||
196 | SQLiteParameter p1 = com.CreateParameter(); |
||
197 | p1.DbType = DbType.Int64; |
||
198 | com.Parameters.Add( p1 ); |
||
199 | SQLiteParameter p2 = com.CreateParameter(); |
||
200 | p2.DbType = DbType.String; |
||
201 | com.Parameters.Add( p2 ); |
||
202 | |||
203 | long start = DateTime.Now.Ticks; |
||
204 | long key = 1999; |
||
205 | for ( i = 0; i < nRecords; i++ ) |
||
206 | { |
||
207 | key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
||
208 | p1.Value = key; |
||
209 | p2.Value = key.ToString(); |
||
210 | com.ExecuteNonQuery(); |
||
211 | } |
||
212 | com.CommandText = "END"; |
||
213 | com.Parameters.Clear(); |
||
214 | com.ExecuteNonQuery(); |
||
215 | timer[0, 0] = DateTime.Now.Ticks - start; |
||
216 | |||
217 | com.CommandText = "BEGIN EXCLUSIVE"; |
||
218 | com.ExecuteNonQuery(); |
||
219 | |||
220 | using ( SQLiteCommand com2 = con.CreateCommand() ) |
||
221 | { |
||
222 | com.CommandText = SELECT_Bind_i; |
||
223 | com.Parameters.Clear(); |
||
224 | com.Parameters.Add( p1 ); |
||
225 | |||
226 | com2.CommandText = SELECT_Bind_s; |
||
227 | com2.Parameters.Clear(); |
||
228 | com2.Parameters.Add( p2 ); |
||
229 | |||
230 | start = DateTime.Now.Ticks; |
||
231 | key = 1999; |
||
232 | object[] resValues = new object[2]; |
||
233 | for ( i = 0; i < nRecords; i++ ) |
||
234 | { |
||
235 | key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
||
236 | p1.Value = key; |
||
237 | p2.Value = key.ToString(); |
||
238 | using ( SQLiteDataReader res = com.ExecuteReader() ) |
||
239 | { |
||
240 | res.Read(); |
||
241 | res.GetValues( resValues ); |
||
242 | } |
||
243 | long id = (long)resValues[0]; |
||
244 | using ( SQLiteDataReader res = com2.ExecuteReader() ) |
||
245 | { |
||
246 | res.Read(); |
||
247 | res.GetValues( resValues ); |
||
248 | } |
||
249 | Debug.Assert( id == ( (long)resValues[0] ) ); |
||
250 | } |
||
251 | } |
||
252 | |||
253 | timer[0, 1] = DateTime.Now.Ticks - start; |
||
254 | com.CommandText = "END"; |
||
255 | com.Parameters.Clear(); |
||
256 | com.ExecuteNonQuery(); |
||
257 | |||
258 | com.CommandText = "BEGIN EXCLUSIVE"; |
||
259 | com.ExecuteNonQuery(); |
||
260 | |||
261 | start = DateTime.Now.Ticks; |
||
262 | com.CommandText = SELECT_Command_i; |
||
263 | com.Parameters.Clear(); |
||
264 | key = Int64.MinValue; |
||
265 | i = 0; |
||
266 | using ( SQLiteDataReader reader = com.ExecuteReader() ) |
||
267 | { |
||
268 | object[] resValues = new object[2]; |
||
269 | while ( reader.Read() ) |
||
270 | { |
||
271 | reader.GetValues( resValues ); |
||
272 | long intKey = (long)resValues[0]; |
||
273 | Debug.Assert( intKey >= key ); |
||
274 | key = intKey; |
||
275 | i += 1; |
||
276 | } |
||
277 | Debug.Assert( i == nRecords ); |
||
278 | } |
||
279 | com.CommandText = SELECT_Command_s; |
||
280 | using ( SQLiteDataReader reader = com.ExecuteReader() ) |
||
281 | { |
||
282 | i = 0; |
||
283 | String strKey = ""; |
||
284 | object[] resValues = new object[2]; |
||
285 | while ( reader.Read() ) |
||
286 | { |
||
287 | reader.GetValues( resValues ); |
||
288 | string recStrKey = (string)resValues[1]; |
||
289 | Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 ); |
||
290 | strKey = recStrKey; |
||
291 | i += 1; |
||
292 | } |
||
293 | Debug.Assert( i == nRecords ); |
||
294 | } |
||
295 | timer[0, 2] = DateTime.Now.Ticks - start; |
||
296 | |||
297 | com.CommandText = "END"; |
||
298 | com.Parameters.Clear(); |
||
299 | com.ExecuteNonQuery(); |
||
300 | |||
301 | com.CommandText = "BEGIN EXCLUSIVE"; |
||
302 | com.ExecuteNonQuery(); |
||
303 | |||
304 | com.CommandText = DELETE_Bind; |
||
305 | com.Parameters.Clear(); |
||
306 | com.Parameters.Add( p1 ); |
||
307 | |||
308 | start = DateTime.Now.Ticks; |
||
309 | key = 1999; |
||
310 | for ( i = 0; i < nRecords; i++ ) |
||
311 | { |
||
312 | key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
||
313 | p1.Value = key; |
||
314 | com.ExecuteNonQuery(); |
||
315 | } |
||
316 | com.CommandText = "END"; |
||
317 | com.Parameters.Clear(); |
||
318 | com.ExecuteNonQuery(); |
||
319 | |||
320 | timer[0, 3] = DateTime.Now.Ticks - start; |
||
321 | con.Close(); |
||
322 | } |
||
323 | |||
324 | static void PrintStats( int nRecords ) |
||
325 | { |
||
326 | |||
327 | Console.WriteLine( " # Records Inserting Searching Iterating Deleting" ); |
||
328 | Console.WriteLine( |
||
329 | String.Format( " SQLite{0,10:####,###}{1,10:#####.0s}{2,10:#####.0s}{3,10:#####.0s}{4,10:#####.0s}" |
||
330 | , nRecords |
||
331 | , ( timer[0, 0] ) * 10e-8 + .05 |
||
332 | , ( timer[0, 1] ) * 10e-8 + .05 |
||
333 | , ( timer[0, 2] ) * 10e-8 + .05 |
||
334 | , ( timer[0, 3] ) * 10e-8 + .05 |
||
335 | ) ); |
||
336 | Console.WriteLine( |
||
337 | String.Format( "C#-SQLite{0,10:####,###}{1,10:#####.0s}{2,10:#####.0s}{3,10:#####.0s}{4,10:#####.0s}" |
||
338 | , nRecords |
||
339 | , ( timer[1, 0] ) * 10e-8 + .05 |
||
340 | , ( timer[1, 1] ) * 10e-8 + .05 |
||
341 | , ( timer[1, 2] ) * 10e-8 + .05 |
||
342 | , ( timer[1, 3] ) * 10e-8 + .05 |
||
343 | ) ); |
||
344 | Console.WriteLine( |
||
345 | String.Format( "C#/SQLite{0,10:####,###}{1,10:#####.0x}{2,10:#####.0x}{3,10:#####.0x}{4,10:#####.0x}" |
||
346 | , nRecords |
||
347 | , ( (double)timer[1, 0] / timer[0, 0] ) |
||
348 | , ( (double)timer[1, 1] / timer[0, 1] ) |
||
349 | , ( (double)timer[1, 2] / timer[0, 2] ) |
||
350 | , ( (double)timer[1, 3] / timer[0, 3] ) |
||
351 | ) ); |
||
352 | } |
||
353 | } |