Version 0.19 is released

2004-08-04
2004-08-06
  • Alexander Gavrilov

      The new version supports SQLite2 and SQLite3 in the same code. To load and use SQLite3, you have to add 'Version=3' to the connection string. To use SQLite2, you can add 'Version=2' to the connection string or omit 'Version' option altogether. The downloadable package includes the latest versions of SQLite2 and 3 (2.8.15 and 3.0.3 at this time).

     
    • Nobody/Anonymous

      Thanks for all your great work. I have noticed a severe degradation inserting into tables using the version=3 - is this a Sqlite3.dll problem?

      I modified the code in test.cs to demonstrate:

          for (int v=2;v<4;++v) // Added to compare Versions
          {
              IDbConnection conn = new SQLiteConnection();
              conn.ConnectionString = "Data Source=test" + v + ".db;New=True;Compress=False;Synchronous=Off;UTF8Encoding=False;Version=" + v + ""; // Modified for Version Comparison
              conn.Open();
              if (conn.State != ConnectionState.Open)
                  return;
              IDbCommand cmd = conn.CreateCommand();
              cmd.CommandText = "create table mytable (src text, dst float, time float, value float)";
              cmd.ExecuteNonQuery();
              cmd.CommandText = "insert into mytable values (?, ?, ?, ?)";
              ((SQLiteCommand) cmd).CreateAndAddUnnamedParameters();
              Write ("Inserting Version=" + v + "... "); // Modified for Version Comparison
              int startTime = Environment.TickCount;
              IDbTransaction trans = conn.BeginTransaction();
              for (int i = 0; i < Count; ++i)
              {
                  ((IDataParameter) cmd.Parameters[0]).Value = "AaBbCc123";
                  if (i % 20 != 0)
                      ((IDataParameter) cmd.Parameters[1]).Value = i;
                  else
                      ((IDataParameter) cmd.Parameters[1]).Value = null;
                  ((IDataParameter) cmd.Parameters[2]).Value = (i * 3.3).ToString();
                  ((IDataParameter) cmd.Parameters[3]).Value = (Single) (i * 4.4);
                  cmd.ExecuteNonQuery();
              }
              trans.Commit();
              WriteLine ("{0} inserts/sec", (Count*1000 / (Environment.TickCount - startTime)));
         
              Write ("Reading Version=" + v + "... "); // Modified for Version Comparison
              startTime = Environment.TickCount;
              cmd.CommandText = "select * from mytable";
              IDataReader reader = cmd.ExecuteReader();
              Double d;
              String s;
              int count = 0;
              while (reader.Read())
              {
                  ++count;
                  s = reader.GetString (0);
                  if (!reader.IsDBNull (1))
                      d = reader.GetDouble (1);
                  d = reader.GetDouble (2);
                  d = reader.GetDouble (3);
              }
              WriteLine ("{0} reads/sec", (count*1000 / (Environment.TickCount - startTime)));
          } // Added for Version Comparison

       
    • Nobody/Anonymous

      Thanks for all your great work.
      I have noticed a noticeable reduction in inserts per sec using version=3. Is this a problem with SQLite3.dll?

      I modified the code in test.cs to demonstrate:

          for (int v=2;v<4;++v) // Added to compare Versions
          {
              IDbConnection conn = new SQLiteConnection();
              conn.ConnectionString = "Data Source=test" + v + ".db;New=True;Compress=False;Synchronous=Off;UTF8Encoding=False;Version=" + v + ""; // Modified for Version Comparison
              conn.Open();
              if (conn.State != ConnectionState.Open)
                  return;
              IDbCommand cmd = conn.CreateCommand();
              cmd.CommandText = "create table mytable (src text, dst float, time float, value float)";
              cmd.ExecuteNonQuery();
              cmd.CommandText = "insert into mytable values (?, ?, ?, ?)";
              ((SQLiteCommand) cmd).CreateAndAddUnnamedParameters();
              Write ("Inserting Version=" + v + "... "); // Modified for Version Comparison
              int startTime = Environment.TickCount;
              IDbTransaction trans = conn.BeginTransaction();
              for (int i = 0; i < Count; ++i)
              {
                  ((IDataParameter) cmd.Parameters[0]).Value = "AaBbCc123";
                  if (i % 20 != 0)
                      ((IDataParameter) cmd.Parameters[1]).Value = i;
                  else
                      ((IDataParameter) cmd.Parameters[1]).Value = null;
                  ((IDataParameter) cmd.Parameters[2]).Value = (i * 3.3).ToString();
                  ((IDataParameter) cmd.Parameters[3]).Value = (Single) (i * 4.4);
                  cmd.ExecuteNonQuery();
              }
              trans.Commit();
              WriteLine ("{0} inserts/sec", (Count*1000 / (Environment.TickCount - startTime)));
         
              Write ("Reading Version=" + v + "... "); // Modified for Version Comparison
              startTime = Environment.TickCount;
              cmd.CommandText = "select * from mytable";
              IDataReader reader = cmd.ExecuteReader();
              Double d;
              String s;
              int count = 0;
              while (reader.Read())
              {
                  ++count;
                  s = reader.GetString (0);
                  if (!reader.IsDBNull (1))
                      d = reader.GetDouble (1);
                  d = reader.GetDouble (2);
                  d = reader.GetDouble (3);
              }
              WriteLine ("{0} reads/sec", (count*1000 / (Environment.TickCount - startTime)));
          } // Added for Version Comparison

       
      • Alexander Gavrilov

        Version 3.0 trades CPU cycles for disk I/O. See thread at:
        http://www.mail-archive.com/sqlite-users%40sqlite.org/msg03240.html

        Anyway, Test.cs was written for the previous version of SQLite and didn't set DbType of parameters correctly. It wasn't important for the version 2 because the version 2 kept all data as C strings. The version 3 of SQLite introduced  Manifest Typing and DbType become important. I've corrected the test and the corrected test runs version 3 at the same speed and even faster. The corrected version of Test.cs in CVS.

         
    • Wilson Chan

      Wilson Chan - 2004-08-06

      Well, will the driver itself auto detect the actual version of the database without specifying it?

       
      • Alexander Gavrilov

        No.

         

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks