wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
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 }