Why might SQLite insert be very slow? (Transactions used)

I am inserting 8500 lines at a SQLite database. It takes > 30sec at a Core 2 Duo. Its using 70% of CPU during this time, then the problem is the CPU usage.

I am using transaction.

I create the database, tables and inserts on the fly at a temp file. Then I donĀ“t need to worry about corruption, etc.

I just tried to use this, but dont help:

PRAGMA journal_mode = OFF;
PRAGMA synchronous = OFF;

What more can I do?

If I run the same script at Firefox SQLite Manager Plugin, it runs instantly.

I run the profiler.

All the time is at

27seg System.Data.SQLite.SQLite3.Prepare(SQLiteConnection, String, SQLiteStatement, UInt32, String&)

This method calls the three methods

12seg System.Data.SQLite.SQLiteConvert.UTF8ToString(IntPtr, Int32)
9seg System.Data.SQLite.SQLiteConvert.ToUTF8(String)
4seg System.Data.SQLite.UnsafeNativeMethods.sqlite3_prepare_interop(IntPtr, IntPtr, Int32, IntPtr&, IntPtr&, Int32&)

You asked to show the insert. Here:

INSERT INTO [Criterio] ([cd1],[cd2],[cd3],[dc4],[dc5],[dt6],[dc7],[dt8],[dt9],[dt10],[dt11])VALUES('FFFFFFFF-FFFF-FFFF-FFFF-B897A4DE6949',10,20,'',NULL,NULL,'',NULL,julianday('2011-11-25 17:00:00'),NULL,NULL);

Table:

CREATE TABLE Criterio (
   cd1          integer NOT NULL,
   cd2         text NOT NULL,
   dc3        text NOT NULL,
   cd4    integer NOT NULL,
   dt5           DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
   dt6         DATE NULL,
   dt7          DATE NULL,
   dc8   TEXT NULL,
   dt9   datetime NULL,
   dc10            TEXT NULL,
   dt11            datetime NULL,
   PRIMARY KEY (cd2 ASC, cd1 ASC)

);

C# Code:

        scriptSql = System.IO.File.ReadAllText(@"C:\Users\Me\Desktop\ScriptToTest.txt");

        using (DbCommand comando = Banco.GetSqlStringCommand(scriptSql))
        {
            try
            {
                using (TransactionScope transacao = new TransactionScope())
                {
                    Banco.ExecuteNonQuery(comando);
                    transacao.Complete();
                }
            }
            catch (Exception ex)
            {
                Logging.ErroBanco(comando, ex);
                throw;
            }
        }

Answers


I don't know why pst deleted his answer so I'll re-post the same information from it as this appears to be the correct answer.

According to the SQLite FAQ - INSERT is really slow - I can only do few dozen INSERTs per second

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second

...

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction.

So basically you need to group your INSERTs into fewer transactions.

Update: So the problem is probably mostly due to the sheer size of the SQL script - SQLite needs to parse the entire script before it can execute, but the parser will be designed to parse small scripts not massive ones! This is why you are seeing so much time spent in the SQLite3.Prepare method.

Instead you should use a parameterised query and insert records in a loop in your C# code, for example if your data was in CSV format in your text file you could use something like this:

using (TransactionScope txn = new TransactionScope())
{
    using (DbCommand cmd = Banco.GetSqlStringCommand(sql))
    {
        string line = null;
        while ((line = reader.ReadLine()) != null)
        {
            // Set the parameters for the command at this point based on the current line
            Banco.ExecuteNonQuery(cmd);
            txn.Complete();
        }
    }
}

Need Your Help

XMPP C# Interaction

c# javascript xmpp ejabberd

I am trying to connect via c# and via javascript to an xmpp server (currently ejabberd). Im having a little trouble conceptualizing how the connections will exists.

SQLiteException Error Code = 1 Syntax Error near 'columnName'

android sqlite3

I am new to android platform. I am working on a project in which I have to make a shared calender with two calendar interfaces on one activity. User can save an event with event details that is event

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.