System.Data.SQLite

hello all

I have been benchmarking SQLite with Access 2003 lately and have found some very problems with SQLite/ADO.NET wrapper or hopefully the way I am doing it is wrong. Here is the code in question:

SQLite code:

slTransaction = slConnection.BeginTransaction

slCmdInsert.ExecuteNonQuery()

slTransaction.Commit()

I'm not sure I follow.  You're comparing the speed of Access to SQLite by inserting a single row into a table?  You should try inserting 100 values instead, or even 100,000 ...

Robert

Reposted from my reply on in the Benchmarking page:

If you don't care about ACID compliance, then add the following to your ConnectionString property:  "Synchronous=OFF"

Also, I recommend you create a SQLiteCommand *one time* and use parameters in it for the insert.  Then every time you want to insert, you simply change the value of the parameters and re-execute the command.  Do not keep building a new SQLiteCommand every time you want to insert.

Robert

Thank you for replying so fast. No actually I am not inserting 1 row and comparing speed. Actually its quite the contrary. Here is what i am doing -

1. Make two databases one of access and other of SQLite
2. Start two threads on these databases (with independent connection objects). One thread for insert and other for selects.
3. Measure time taken to do inserts locally

Here is the complete source code.

http://pastebin.com/508231

Thanks and awating your reply.

Problem #1:  Your Page Size should be no larger than 4096.  512000 is way beyond what's necessary and optimal.  Page Size is for the on-disk pages.  If you want to tweak cache speed, set the Cache Size to something larger -- but Page Size is the physical page storage on disk.

Problem #2:  You are starting a transaction, inserting, and committing the transaction INSIDE the while loop.  You need to start the transaction before the loop and commit it after the loop ends.  Right now nearly 99% of the work is beginning and committing the transaction.

Ok about the page size, it was one of my desparate and illogical attemps to get things speeded up.

And on transaction inside while loops, the reason I am doing it is because in the real application where SQLite is going to be used, there is one thread that is getting data packets from internet in a arbitrary way and is inserting them in the database.

The other thread can read this database anytime and will need the most up-to-date values.

Now because the data packets arrive in a random fashion and always in single units each insert statement needs to commited independently.

Right now I am going to remove the transaction blocks and see how much performance improvments i can get.

Also I am using Access connections/transactions in a similar fashion, but still access is much faster. How does access do this?

The simple reason JET is faster than SQLite at arbitrary single-line inserts is because JET is not ACID compliant.  In order to be ACID compliant, there's a lot more work involved preparing for the write, setting up the rollback journal, syncing the disk, etc.

If you remove the transaction entirely and issue a raw INSERT statement, it will be the same resulting speed.  SQLite will automatically surround the INSERT with a transaction for you in order to ensure ACID compliance.

Robert

Also keep in mind that SQLite locks the entire database for the duration of a write.  Any readers wanting to read the database need to get in and get out quick, as they will block any writes until they are finished.  Likewise, all readers will be blocked until a write is finished.

One way to get around this limitation is to attach another database file to your connection using the ATTACH SQLite command.  You can then issue writes to tables in the attached database and then periodically sync them to the main database.  All the readers read from the main database, and then nothing is blocked except on the sync which is pretty fast.

Additionally, if you don't NEED ACID compliance, you can do the same scenario, except attach a :memory: database to the main database.  By doing this and writing to memory, your inserts will be instantaneous, and then you just periodically sync to the main database via an INSERT INTO (...) SELECT (...) statement that copies the data from the memory database to the main database.

Robert

Ok. So it boils down to that if ACID is not what is required then Access is better suited than SQLite?

Anyways i will try keeping memory tables for inserts and sync-ing them to disk for selects and see if it has a better performance and fault tolerance than Access.

I will post my results in benchmark section.

Thanx

Too broad :)  Have you tried adding "Synchronous=OFF" to your SQLite connectionstring yet?

If you are doing single inserts at unknown time intervals and are unable to bundle them together inside a transaction and do not require ACID compliance, then Access's caching will make the program appear to run faster than SQLite with less work.

The easiest alternative in SQLite is to create a temp table to store the inserts, then every couple seconds, issue an:
INSERT INTO mymaintable SELECT * FROM temp.mytemptable; DELETE FROM temp.mytemptable;

Robert

Can a in-memory table created in one thread accessed from another thread.

I tried doing it and exceptions tell me its not. Can any one conform this?

Thanx

An in-memory database only exists on the connection it was created on.  You cannot clone a memory database.  Since it is also illegal to share a connection across threads, the short answer is "no" you can't do that.

Robert

I have been working on these lines and i have a small problem. I am not able to comprehend this

INSERT INTO mymaintable SELECT * FROM temp.mytemptable; DELETE FROM temp.mytemptable;

I have created two SQLiteConnections memoryCon and diskCon and inserting in memory one and reading from diskCon. but i dont know how to get data from memory db and shift to disk db in a single sql query as you have written above, as both of them are completly different databases with one database as :memory: (which is mandatory).

Only way i see it happending is using a intermediate DataReader, which i dont want to as i suspect that it will decrease performance. another possibility is to write a wrapper class for inserting into the database in which all the inserts will be buffered for some time and then will be written out in a transaction after some time say 500ms.

Here's how you do it ... Lets assume the memory database has a table called "mytemptable" and the main database has a table called "mypermtable"

SQliteConnection a = new SQLiteConnection("Data Source=c:\\file.db"); // Your file-based database, all your readers read from this connection
SQLiteConnection b = new SQLiteConnection("Data Source=:memory:"); // your memory based connection -- all your writers write to this connection

using (SQLiteCommand cmd = new SQLiteCommand("ATTACH DATABASE c:\\file.db AS perm", b))
{
  cmd.ExecuteNonQuery(); // Our file database is now attached to the memory connection
}

// Insert the stuff into "mytemptable" on connection b all you like ... when ready to commit it to disk, use:

using (SQLiteCommand cmd = new SQLiteCommand("INSERT INTO perm.mypermtable SELECT * FROM mytemptable; DELETE FROM mytemptable", b)
{
  cmd.ExecuteNonQuery();
}

Hope that helps

Hello...

For quite some time now i have been trying to find ways to get maximum possible performance out of SQLite in my application. Here is the configuration of SQLite I am using, for benchmarking (Till now the road has been quite bumpy and it will be great if anyone has any idea about how to resolve it).

1. Data source is set to memory.
2. I have two command from the same connection, one command i use for inserting/updating and other for selecting.
3. One thread is continuously inserting records and the other one is continuously selecting. Metrics for performance are number of inserts per second, number of selects at that time and how low is the difference between the two.

Now i know that this not the right way of multithreading (rather it will give unexpected sequence of instructions exception :-s) but i am forced to use only one SQLConnection as same in-memory databases can't be shared accross multiple connections. So i have been trying to syncronize it using all i know and this is where all the peoblem is. Sometimes one thread simply hangs, sometimes both, sometimes inserting speed is too low, etc, etc etc....

Just beforing writing this i came accross Async command interface of ADO.NET 2.0 for sqlserver 2005 and i think this is what may be the solution (for sqlite). Following is the code i am using now and this code seems to give this exception

"System.Data.SQLite.SQLiteException: SQLite error SQL logic error or missing database"

Code is pasted here

• Wrapper class (http://pastebin.com/594291)

Please let me know what is the problem here.

PS: I have also tried using synclock instead of ReaderWriterLock but then problem of locking occurs.