SqlLite?

nitro · Dec 20, 2011

I wrote the following test in C# to test SqlLite which I hope would simmulate inserting high frequency data in realtime into an sqllite in memory db. The line

Console.WriteLine(sw.ElapsedMilliseconds);

returns 2200 or 2.2 seconds on my machine (nothing to speak of in terms of speed it is just a dual core with 4GB memory running Windows 7 x64.) I was surprised, this seems slow to me for an in memory database doing a bunch of inserts, 3 * 65,000 to be exact. That only comes to ~87,000 inserts a second. Seems slow...

Note that the program is not multithreaded and I don't know if that would make it faster or not.

Code:

       static void TestInMemory()
        {
            SQLiteConnection _cnn;
            _cnn = new SQLiteConnection("data source=:memory:");
            _cnn.Open();

            string schema = GetSchema();
            string sql = GetSql();

            SQLiteCommand mySchemaCommand = new SQLiteCommand(schema, _cnn);
            SQLiteCommand mySqlCommand = new SQLiteCommand(sql, _cnn);

            mySchemaCommand.ExecuteNonQuery();

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

            for(int i = 0; i < 65000; i++)
                mySqlCommand.ExecuteNonQuery();
            
            sw.Stop();
                  
            Console.WriteLine(sw.ElapsedMilliseconds);
            Console.ReadLine();           
        }

        static string GetSchema()
        {
            return @"
            CREATE TABLE [Employees] (
            [EmpID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            [EmpName] NVARCHAR(128) NOT NULL,
            [EmpSalary] FLOAT NOT NULL
            ); 
        ";
        }

        static string GetSql()
        {
            return @"
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('Joe Bloggs', 5000);
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('Tim Jones', 4000);
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('John Smith', 4500);
                    ";
        }

tradelink · Dec 20, 2011

30k writes/second is pretty fast for sql.

here is ms-sql 2008 at 6k/second for financial data

http://www.microsoft.com/casestudie...-2008-Tops-6-000-Writes-per-Second/4000006202

nitro · Dec 20, 2011

Quote from tradelink:

30k writes/second is pretty fast for sql.

here is ms-sql 2008 at 6k/second for financial data

http://www.microsoft.com/casestudie...-2008-Tops-6-000-Writes-per-Second/4000006202

Thanks, hmmmm.

I added the following BEGIN/COMMIT

Code:

...
       static string GetSql()
        {
            return @"
            BEGIN;
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('Joe Bloggs', 5000);
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('Tim Jones', 4000);
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('John Smith', 4500);
            COMMIT;
                    ";
        }

as per

http://www.sqlite.org/faq.html#q19

and that gives me some performance increase, now at 1.9 seconds instead of 2.2.

nitro · Dec 20, 2011

Interesting recommendations here:

http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite

PocketChange · Dec 20, 2011

Batch and Commit greatly improves performance of disk based db's but not nearly as significant with in memory DB.

Using asynchronous IO can cause SQLite to appear more responsive by delegating database writes to a background thread.
http://sqlite.org/asyncvfs.html

A bit dated: http://sqlite.org/speed.html

rdg · Dec 20, 2011

nitro, try moving the begin/commit out of the loop. I get about 2x speedup from your original.

Code:

            SQLiteTransaction transaction = _cnn.BeginTransaction();
            for (int i = 0; i < 65000; i++)
            {
                mySqlCommand.ExecuteNonQuery();
            }
            transaction.Commit();

        static string GetSql()
        {
            return @"
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('Joe Bloggs', 5000);
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('Tim Jones', 4000);
            INSERT INTO Employees (EmpName, EmpSalary) VALUES ('John Smith', 4500);
                    ";
        }

nitro · Dec 20, 2011

Quote from PocketChange:

Batch and Commit greatly improves performance of disk based db's but not nearly as significant with in memory DB.

Using asynchronous IO can cause SQLite to appear more responsive by delegating database writes to a background thread.
http://sqlite.org/asyncvfs.html

A bit dated: http://sqlite.org/speed.html

Thanks,

This gave me the idea of saying:

Code:

            _cnn = new SQLiteConnection("Data Source=:memory:;Journal Mode=Off;Version=3;Page Size=1024;Cache Size=2000;Synchronous=Full;UseUTF16Encoding=True;Legacy Format=True");

I played around with the different settings and now I am donw to 1500 milliseconds. The key one appears to be turning Journal Mode = off.

I think I need to say Synchronous=Off or Synchronous=Full ?

nitro · Dec 20, 2011

Thanks!

This idea further reduces to 1300 milliseconds. Now we are getting close to 150,000 inserts per second! If I put this on a much faster machine, I am sure I can easily double the throughput.

Quote from rdg:

nitro, try moving the begin/commit out of the loop. I get about 2x speedup from your original.

Code:

SQLiteTransaction transaction = _cnn.BeginTransaction(); for (int i = 0; i < 65000; i++) { mySqlCommand.ExecuteNonQuery(); } transaction.Commit(); static string GetSql() { return @" INSERT INTO Employees (EmpName, EmpSalary) VALUES ('Joe Bloggs', 5000); INSERT INTO Employees (EmpName, EmpSalary) VALUES ('Tim Jones', 4000); INSERT INTO Employees (EmpName, EmpSalary) VALUES ('John Smith', 4500); "; }

nitro · Dec 20, 2011

Does anyone know if Sqlite (.Net) can handle inserts from multiple threads?

PocketChange · Dec 21, 2011

Quote from nitro:

I think I need to say Synchronous=Off or Synchronous=Full ? [/B]

PRAGMA synchronous = OFF

By default SQLite will pause after issuing a OS-level write command. This guarantees that the data is written to the disk. By setting synchronous = OFF, we are instructing SQLite to simply hand-off the data to the OS for writing and then continue. There's a chance that the database file may become corrupted if the computer suffers a catastrophic crash (or power failure) before the data is written to the platter: In Memory DB... Set it Off

Also: try making the key an INTEGER PRIMARY KEY if possible, which will replace the implied unique row number column in the table.

Re: Multithreading

From: http://stackoverflow.com/questions/...qlite-and-file-locking-on-different-platforms

"In my experience (although based on somewhat dated SQLite versions, i.e. 3.0.x - 3.2.x) you don't want do rely on SQLite's default locking implementation when there is some kind of contention (even if it's just multiple threads in the same process).

The main problem is that SQLite just sleeps for a little while and then tries again if the file has been locked (until the busy wait timeout expires) - this is likely to result in "database locked" errors in the application. The workaround I have implemented was to use my own locking (using a mutex in a multi-threaded process) in addition to SQLite's own locking."