Vice Underdogs

Scripting => Scripting Support => Topic started by: stormeus on November 23, 2011, 10:07:08 pm

Title: [Benchmark] SQLite vs INI
Post by: stormeus on November 23, 2011, 10:07:08 pm
SQLite vs. INI: Read, Write, and Read/Write/Read
This test consists of three tests measuring the speed of SQLite's and INI files' read speeds, write speeds, and read/write/read speeds.

Read
Test consists of reading 1,000 records, printing the value of each record as it's read.
INI:
Quote
1 = a
2 = a
3 = a{...}


SQLite:
Quote
one | two
----|----
1   | a
2   | a
3   | a{...}

Write
Consists of writing 1,000 records in the same format as above.

Read/Write/Read
Reads each of the 1,000 records, prints the value of the record, overwrites it with "b", and prints the new value. (SQLite transactions are not used as it is a single query, and not a group)

Read
SQLite
INI
Quote
function onScriptLoad()
{
   LoadModule( "sq_lite" );
   local i, checkpoint, sql;
   checkpoint = GetTickCount();
   sql = ConnectSQL( "a.sql" );
   
   for( i = 1; i <= 1000; i++ )
   {
      local res = QuerySQL( sql, "SELECT two FROM a WHERE one = " + i );
      print( i + " = " + GetSQLColumnData( res, 0 ) );
      
      FreeSQLQuery( res );
   }
   
   local timeTaken = GetTickCount() - checkpoint;
   print( timeTaken + " ticks." );
}
Quote
function onScriptLoad()
{
   LoadModule( "sq_ini" );
   local i, checkpoint;
   checkpoint = GetTickCount();
   
   for( i = 1; i <= 1000; i++ )
      print( i + " = " + ReadIniString( "a.ini", "a", i.tostring() ) );
   
   local timeTaken = GetTickCount() - checkpoint;
   print( timeTaken + " ticks." );
}
Time: 1,514 ticksTime: 6,892 ticks

Write
SQLite
INI
Quote
function onScriptLoad()
{
   LoadModule( "sq_lite" );
   local i, checkpoint, sql;
   checkpoint = GetTickCount();
   sql = ConnectSQL( "a.sql" );
   
   QuerySQL( sql, "CREATE TABLE a ( one INTEGER, two TEXT )" );
   QuerySQL( sql, "BEGIN TRANSACTION" );
   for( i = 1; i <= 1000; i++ )
   {
      QuerySQL( sql, "INSERT INTO a VALUES ( " + i + ", 'a' )" );
   }
   QuerySQL( sql, "END TRANSACTION" );
   
   local timeTaken = GetTickCount() - checkpoint;
   print( timeTaken + " ticks." );
}
Quote
function onScriptLoad()
{
   LoadModule( "sq_ini" );
   local i, checkpoint;
   checkpoint = GetTickCount();
   
   for( i = 1; i <= 1000; i++ )
      WriteIniString( "a.ini", "a", i.tostring(), "a" );
   
   local timeTaken = GetTickCount() - checkpoint;
   print( timeTaken + " ticks." );
}
Time: 287 ticksTime: 11,481 ticks

Read/Write/Read
SQLite
INI
Quote
function onScriptLoad()
{
   LoadModule( "sq_lite" );
   local i, checkpoint, sql;
   checkpoint = GetTickCount();
   sql = ConnectSQL( "a.sql" );
   
   for( i = 1; i <= 1000; i++ )
   {
      local res = QuerySQL( sql, "SELECT two FROM a WHERE one = " + i );
      print( i + " = " + GetSQLColumnData( res, 0 ) );
      FreeSQLQuery( res );
      
      QuerySQL( sql, "UPDATE a SET two = 'b' WHERE a = " + i );
      
      res = QuerySQL( sql, "SELECT two FROM a WHERE one = " + i );
      print( i + " = " + GetSQLColumnData( res, 0 ) );
      FreeSQLQuery( res );
   }
   
   local timeTaken = GetTickCount() - checkpoint;
   print( timeTaken + " ticks." );
}
Quote
function onScriptLoad()
{
   LoadModule( "sq_ini" );
   local i, checkpoint;
   checkpoint = GetTickCount();
   
   for( i = 1; i <= 1000; i++ )
   {
      print( i + " = " + ReadIniString( "a.ini", "a", i.tostring() ) );
      WriteIniString( "a.ini", "a", i.tostring(), "b" );
      print( i + " = " + ReadIniString( "a.ini", "a", i.tostring() ) );
   }
   
   local timeTaken = GetTickCount() - checkpoint;
   print( timeTaken + " ticks." );
}
Time: 264,316 ticksTime: 29,800 ticks

Conclusion
SQLite is excellent for reading and writing long chains of data or single pieces of data, but fails when expected to write without using transactions. INIs are slower when reading and writing long chains of data, but performed better than SQLite when expected to read, write, and read again.

Because transactions could not have been used, the performance of SQLite was hindered by that and because SQLite had to check the integrity of each entry as it was written. INIs are efficient for that purpose, but because of how specific the situation was, I'm concluding that SQLite is better than using INIs in overall performance, and can perform even faster if I/O synchronization was disabled (which can corrupt data if power is lost or the server crashes).
Title: Re: [Benchmark] SQLite vs INI
Post by: Thijn on November 24, 2011, 02:31:40 pm
Even though INI's relay on the I/O speed of the OS/HDD its overall slower then Sqlite.
And even slower if its a big file.
Also, One sqlite database with around 5700 accounts, with each stats and aliases. is 1.7 MB.
If you have to convert that to INI's you'll need a bit more space.
Title: Re: [Benchmark] SQLite vs INI
Post by: Skirmant on November 27, 2011, 06:07:04 pm
Quote from: Thijn on November 24, 2011, 02:31:40 pm
Even though INI's relay on the I/O speed of the OS/HDD its overall slower then Sqlite.
And even slower if its a big file.
Also, One sqlite database with around 5700 accounts, with each stats and aliases. is 1.7 MB.
If you have to convert that to INI's you'll need a bit more space.

I know, right? But no worries, I'm fighting the good fight and proving that INI is faster on...
http://vcmp.liberty-unleashed.co.uk/forum/index.php?topic=488.0 (http://vcmp.liberty-unleashed.co.uk/forum/index.php?topic=488.0) :3
Read - down. Write to go.
Title: Re: [Benchmark] SQLite vs INI
Post by: stormeus on November 27, 2011, 07:36:12 pm
Quote from: stormeus
If it's 20x faster, SQLite still outperforms it.