| /trunk/Community.CsharpSqlite.Benchmark/src/Benchmark.cs |
|---|
| @@ -0,0 +1,353 @@ |
|---|
| // $Header$ |
| |
| using System; |
| using System.Data; |
| using System.Data.SQLite; |
| using System.Diagnostics; |
| using System.IO; |
| using Community.CsharpSqlite; |
| |
| /* |
| * Benchmark Test for both SQLite and C#-SQLite |
| */ |
| |
| public class Benchmark |
| { |
| private static int nRecords; |
| |
| private static string[] PRAGMA_Commands = { |
| "PRAGMA synchronous = OFF", |
| "PRAGMA temp_store = MEMORY", |
| "PRAGMA journal_mode = OFF" , |
| "PRAGMA locking_mode=EXCLUSIVE" |
| }; |
| |
| private static string[] CREATE_Commands = { |
| "CREATE TABLE Root (intIndex INTEGER PRIMARY KEY, strIndex TEXT)", |
| "CREATE INDEX RootStrIndex ON Root (strIndex)" |
| }; |
| |
| private static string INSERT_Command = "INSERT INTO Root VALUES (?,?)"; |
| private static string SELECT_Bind_i = "SELECT * FROM Root WHERE intIndex = ?"; |
| private static string SELECT_Bind_s = "SELECT * FROM Root WHERE strIndex = ?"; |
| |
| private static string SELECT_Command_i = "SELECT * FROM Root ORDER BY intIndex"; |
| private static string SELECT_Command_s = "SELECT * FROM Root ORDER BY strIndex"; |
| |
| private static string DELETE_Bind = "DELETE FROM Root WHERE intIndex = ?"; |
| |
| private static long[,] timer = new long[2, 4]; |
| |
| private static string databaseName; |
| |
| public static void Main() |
| { |
| for ( nRecords = 10000; nRecords <= 200000; nRecords *= 2 ) |
| { |
| databaseName = "Benchmark_cs-SQLite.sqlite"; |
| TestSQLite(); |
| // |
| databaseName = "Benchmark_cs-Sqlite3.sqlite"; |
| TestCsharpSqlite(); |
| // |
| PrintStats( nRecords ); |
| } |
| Console.WriteLine( "Enter to Continue: " ); |
| Console.ReadKey(); |
| } |
| |
| private static void TestCsharpSqlite() |
| { |
| SQLiteDatabase db; |
| SQLiteVdbe stmt; |
| SQLiteVdbe c1, c2; |
| |
| bool found; |
| int i; |
| |
| string databaseName = "Benchmark_cs-SQLite.sqlite"; |
| if ( File.Exists( databaseName ) ) File.Delete( databaseName ); |
| |
| db = new SQLiteDatabase( databaseName ); |
| for ( i = 0; i < PRAGMA_Commands.Length; i++ ) { db.ExecuteNonQuery( PRAGMA_Commands[i] ); } |
| |
| db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); |
| for ( i = 0; i < CREATE_Commands.Length; i++ ) { db.ExecuteNonQuery( CREATE_Commands[i] ); } |
| stmt = new SQLiteVdbe( db, INSERT_Command ); |
| long start = DateTime.Now.Ticks; |
| long key = 1999; |
| for ( i = 0; i < nRecords; i++ ) |
| { |
| key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
| stmt.Reset(); |
| stmt.BindLong( 1, key ); |
| stmt.BindText( 2, key.ToString() ); |
| stmt.ExecuteStep(); |
| } |
| stmt.Close(); |
| db.ExecuteNonQuery( "END" ); |
| timer[1, 0] = DateTime.Now.Ticks - start; |
| |
| db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); |
| start = DateTime.Now.Ticks; |
| c1 = new SQLiteVdbe( db, SELECT_Bind_i ); |
| c2 = new SQLiteVdbe( db, SELECT_Bind_s ); |
| key = 1999; |
| for ( i = 0; i < nRecords; i++ ) |
| { |
| key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
| c1.Reset(); |
| c1.BindLong( 1, key ); |
| c1.ExecuteStep(); |
| |
| c2.Reset(); |
| c2.BindText( 1, key.ToString() ); |
| c2.ExecuteStep(); |
| |
| long id = (long)c1.Result_Long( 0 ); |
| Debug.Assert( id == (long)c2.Result_Long( 0 ) ); |
| |
| } |
| c1.Close(); |
| c2.Close(); |
| db.ExecuteNonQuery( "END" ); |
| timer[1, 1] = DateTime.Now.Ticks - start; |
| |
| db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); |
| start = DateTime.Now.Ticks; |
| key = Int64.MinValue; |
| i = 0; |
| c1 = new SQLiteVdbe( db, SELECT_Command_i ); |
| while ( c1.ExecuteStep() != Sqlite3.SQLITE_DONE ) |
| { |
| long intKey = (long)c1.Result_Long( 0 ); |
| Debug.Assert( intKey >= key ); |
| key = intKey; |
| i += 1; |
| } |
| c1.Close(); |
| Debug.Assert( i == nRecords ); |
| |
| String strKey = ""; |
| i = 0; |
| c2 = new SQLiteVdbe( db, SELECT_Command_s ); |
| while ( c2.ExecuteStep() != Sqlite3.SQLITE_DONE ) |
| { |
| string recStrKey = (string)c2.Result_Text( 1 ); |
| Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 ); |
| strKey = recStrKey; |
| i += 1; |
| } |
| c2.Close(); |
| Debug.Assert( i == nRecords ); |
| timer[1, 2] = DateTime.Now.Ticks - start; |
| db.ExecuteNonQuery( "END" ); |
| |
| db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); |
| start = DateTime.Now.Ticks; |
| key = 1999; |
| stmt = new SQLiteVdbe( db, DELETE_Bind ); |
| for ( i = 0; i < nRecords; i++ ) |
| { |
| key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
| stmt.Reset(); |
| stmt.BindLong( 1, key ); |
| stmt.ExecuteStep(); |
| } |
| stmt.Close(); |
| db.ExecuteNonQuery( "END" ); |
| timer[1, 3] = DateTime.Now.Ticks - start; |
| db.CloseDatabase(); |
| #if NET_35 |
| Sqlite3.Shutdown(); |
| #else |
| Sqlite3.sqlite3_shutdown(); |
| #endif |
| } |
| private static void TestSQLite() |
| { |
| int i; |
| string databaseName = "Benchmark_SQLite.sqlite"; |
| if ( File.Exists( databaseName ) ) File.Delete( databaseName ); |
| |
| SQLiteConnectionStringBuilder constring = new SQLiteConnectionStringBuilder(); |
| constring.PageSize = 1024; |
| constring.SyncMode = SynchronizationModes.Off; |
| constring.DataSource = databaseName; |
| |
| SQLiteConnection con = new SQLiteConnection( constring.ToString() ); |
| con.Open(); |
| SQLiteCommand com = con.CreateCommand(); |
| for ( i = 0; i < PRAGMA_Commands.Length; i++ ) |
| { |
| com.CommandText = PRAGMA_Commands[i]; |
| com.ExecuteNonQuery(); |
| } |
| for ( i = 0; i < CREATE_Commands.Length; i++ ) |
| { |
| com.CommandText = CREATE_Commands[i]; |
| com.ExecuteNonQuery(); |
| } |
| |
| com.CommandText = "BEGIN EXCLUSIVE"; |
| com.ExecuteNonQuery(); |
| |
| com.CommandText = "INSERT INTO Root VALUES (?,?)"; |
| SQLiteParameter p1 = com.CreateParameter(); |
| p1.DbType = DbType.Int64; |
| com.Parameters.Add( p1 ); |
| SQLiteParameter p2 = com.CreateParameter(); |
| p2.DbType = DbType.String; |
| com.Parameters.Add( p2 ); |
| |
| long start = DateTime.Now.Ticks; |
| long key = 1999; |
| for ( i = 0; i < nRecords; i++ ) |
| { |
| key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
| p1.Value = key; |
| p2.Value = key.ToString(); |
| com.ExecuteNonQuery(); |
| } |
| com.CommandText = "END"; |
| com.Parameters.Clear(); |
| com.ExecuteNonQuery(); |
| timer[0, 0] = DateTime.Now.Ticks - start; |
| |
| com.CommandText = "BEGIN EXCLUSIVE"; |
| com.ExecuteNonQuery(); |
| |
| using ( SQLiteCommand com2 = con.CreateCommand() ) |
| { |
| com.CommandText = SELECT_Bind_i; |
| com.Parameters.Clear(); |
| com.Parameters.Add( p1 ); |
| |
| com2.CommandText = SELECT_Bind_s; |
| com2.Parameters.Clear(); |
| com2.Parameters.Add( p2 ); |
| |
| start = DateTime.Now.Ticks; |
| key = 1999; |
| object[] resValues = new object[2]; |
| for ( i = 0; i < nRecords; i++ ) |
| { |
| key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
| p1.Value = key; |
| p2.Value = key.ToString(); |
| using ( SQLiteDataReader res = com.ExecuteReader() ) |
| { |
| res.Read(); |
| res.GetValues( resValues ); |
| } |
| long id = (long)resValues[0]; |
| using ( SQLiteDataReader res = com2.ExecuteReader() ) |
| { |
| res.Read(); |
| res.GetValues( resValues ); |
| } |
| Debug.Assert( id == ( (long)resValues[0] ) ); |
| } |
| } |
| |
| timer[0, 1] = DateTime.Now.Ticks - start; |
| com.CommandText = "END"; |
| com.Parameters.Clear(); |
| com.ExecuteNonQuery(); |
| |
| com.CommandText = "BEGIN EXCLUSIVE"; |
| com.ExecuteNonQuery(); |
| |
| start = DateTime.Now.Ticks; |
| com.CommandText = SELECT_Command_i; |
| com.Parameters.Clear(); |
| key = Int64.MinValue; |
| i = 0; |
| using ( SQLiteDataReader reader = com.ExecuteReader() ) |
| { |
| object[] resValues = new object[2]; |
| while ( reader.Read() ) |
| { |
| reader.GetValues( resValues ); |
| long intKey = (long)resValues[0]; |
| Debug.Assert( intKey >= key ); |
| key = intKey; |
| i += 1; |
| } |
| Debug.Assert( i == nRecords ); |
| } |
| com.CommandText = SELECT_Command_s; |
| using ( SQLiteDataReader reader = com.ExecuteReader() ) |
| { |
| i = 0; |
| String strKey = ""; |
| object[] resValues = new object[2]; |
| while ( reader.Read() ) |
| { |
| reader.GetValues( resValues ); |
| string recStrKey = (string)resValues[1]; |
| Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 ); |
| strKey = recStrKey; |
| i += 1; |
| } |
| Debug.Assert( i == nRecords ); |
| } |
| timer[0, 2] = DateTime.Now.Ticks - start; |
| |
| com.CommandText = "END"; |
| com.Parameters.Clear(); |
| com.ExecuteNonQuery(); |
| |
| com.CommandText = "BEGIN EXCLUSIVE"; |
| com.ExecuteNonQuery(); |
| |
| com.CommandText = DELETE_Bind; |
| com.Parameters.Clear(); |
| com.Parameters.Add( p1 ); |
| |
| start = DateTime.Now.Ticks; |
| key = 1999; |
| for ( i = 0; i < nRecords; i++ ) |
| { |
| key = ( 3141592621L * key + 2718281829L ) % 1000000007L; |
| p1.Value = key; |
| com.ExecuteNonQuery(); |
| } |
| com.CommandText = "END"; |
| com.Parameters.Clear(); |
| com.ExecuteNonQuery(); |
| |
| timer[0, 3] = DateTime.Now.Ticks - start; |
| con.Close(); |
| } |
| |
| static void PrintStats( int nRecords ) |
| { |
| |
| Console.WriteLine( " # Records Inserting Searching Iterating Deleting" ); |
| Console.WriteLine( |
| String.Format( " SQLite{0,10:####,###}{1,10:#####.0s}{2,10:#####.0s}{3,10:#####.0s}{4,10:#####.0s}" |
| , nRecords |
| , ( timer[0, 0] ) * 10e-8 + .05 |
| , ( timer[0, 1] ) * 10e-8 + .05 |
| , ( timer[0, 2] ) * 10e-8 + .05 |
| , ( timer[0, 3] ) * 10e-8 + .05 |
| ) ); |
| Console.WriteLine( |
| String.Format( "C#-SQLite{0,10:####,###}{1,10:#####.0s}{2,10:#####.0s}{3,10:#####.0s}{4,10:#####.0s}" |
| , nRecords |
| , ( timer[1, 0] ) * 10e-8 + .05 |
| , ( timer[1, 1] ) * 10e-8 + .05 |
| , ( timer[1, 2] ) * 10e-8 + .05 |
| , ( timer[1, 3] ) * 10e-8 + .05 |
| ) ); |
| Console.WriteLine( |
| String.Format( "C#/SQLite{0,10:####,###}{1,10:#####.0x}{2,10:#####.0x}{3,10:#####.0x}{4,10:#####.0x}" |
| , nRecords |
| , ( (double)timer[1, 0] / timer[0, 0] ) |
| , ( (double)timer[1, 1] / timer[0, 1] ) |
| , ( (double)timer[1, 2] / timer[0, 2] ) |
| , ( (double)timer[1, 3] / timer[0, 3] ) |
| ) ); |
| } |
| } |
| /trunk/Community.CsharpSqlite.Benchmark/src/performance.sql |
|---|
| @@ -0,0 +1,134 @@ |
|---|
| -- |
| -- 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 |
| -- |
| -- Repository path: $HeadURL: https://sqlitecs.googlecode.com/svn/trunk/test/performance.sql $ |
| -- Last Revised : $Revision: 62 $ |
| -- Last Changed By : $LastChangedBy: noah.hart $ |
| -- Last Changed Date : $LastChangedDate: 2009-08-03 09:19:48 -0700 (Mon, 03 Aug 2009) $ |
| ----------------------------------------------------------------------------------------- |
| --------------------------------------------------------------------- |
| -- |
| -- NOTES: |
| -- |
| --------------------------------------------------------------------- |
| |
| ------------------------------------------ |
| -- 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 = NONE; |
| PRAGMA locking_mode = EXCLUSIVE; |
| PRAGMA page_size = 1024; |
| PRAGMA synchronous = OFF; |
| PRAGMA temp_store = MEMORY; |
| ------------------------------------------ |
| -- A LITTLE SETUP BEFORE WE BEGIN |
| ------------------------------------------ |
| |
| ATTACH ':memory:' as tDB; |
| CREATE TABLE tDB.TIMER(TestNumber INTEGER, Description TEXT, StartTime REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL); |
| INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.3', 0, 1, 0); |
| CREATE TABLE tDB.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_'); |
| 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 tDB.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; |
| |
| |
