Geek asks and answers

SQLite Performance Improvement

Well, I have file.sql, containing 20,000insert commands

Example from file .sql

INSERT INTO tableVALUES (1, -400,400,3,154850, 'Text', 590628, 'Text', 1610, 'Text', 79);

INSERT INTO tableVALUES (39, -362,400,3,111659, 'Text', 74896, 'Text', 0, 'Text', 14);

And I use the following code to create a Sqlite database in memory and pull values ​​into it, and then calculate elapsed time

using (var conn = new SQLiteConnection(@"Data Source=:memory:"))
{
    conn.Open();

    var stopwatch = new Stopwatch();
    stopwatch.Start();

    using (var cmd = new SQLiteCommand(conn))
    {
        using (var transaction = conn.BeginTransaction())
        {

                cmd.CommandText = File.ReadAllText(@"file.sql");
                cmd.ExecuteNonQuery();

            transaction.Commit();
        }
    }

    var timeelapsed = stopwatch.Elapsed.TotalSeconds <= 60
                          ? stopwatch.Elapsed.TotalSeconds + " seconds"
                          : Math.Round(stopwatch.Elapsed.TotalSeconds/60) + " minutes";
    MessageBox.Show(string.Format("Time elapsed {0}", timeelapsed));
    conn.Close();
}

Things i tried

  • Using a database of files instead of memory.
  • Using a transaction to start a transaction and commit a transaction [AS SHOWN IN MY CODE].
  • Firefox SQLite Manager , - script; 20,000, , 4 !!!.
  • PRAGMA synchronous = OFF, PRAGMA journal_mode = MEMORY.
  • begin transaction; commit transaction; .sql, .

SQLite: SQLite 50,000 . , , SQLite Manager [ -, ]; , 20,000 4 , , - .

: , , ?!

+5
2

SQLite.Net

using (SqliteConnection conn = new SqliteConnection(@"Data Source=:memory:")) 
{
  conn.Open();
  using(SqliteTransaction trans = conn.BeginTransaction())
  {
    using (SqliteCommand cmd = new SQLiteCommand(conn))
    {
      cmd.CommandText = File.ReadAllText(@"file.sql");
      cmd.ExecuteNonQuery();
    }
    trans.Commit();
  }
  con.Close();
}
0

, :

INSERT INTO table (col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, col11)
SELECT 1,-400,400,3,154850,'Text',590628,'TEXT',1610,'TEXT',79
UNION ALL
SELECT 39,-362,400,3,111659,'Text',74896,'TEXT',0,'TEXT',14
;

, " " 100 .

http://sqlite.org/lang_select.html

SqlLite, , UNION ALL.

0

More articles:


All Articles