SQLite, ADO.NET, Prepared Statements, Transactions and Enterprise Manager

Anybody who has read some of my "stuff" (particularly, articles at eggheadcafe.com) will know that I am a big aficionado of the SQLite database engine. Open source and Free, extreme portability, blazing speed, no installation, and many other features make this a really competitive solution for almost any project except the largest databases. I don't usually recommend software and add-ons, but recently I came across the SQLITE PRO Enterprise Manager.
NOTE: The above link is dead, apparently the guy gave up his domain. I'm posting the below link which is to the 3.61MB download of my original free version on my SkyDrive public folder.


Now this is the equivalent of Enterprise Manager for SQLite 3 databases, and it is slick. Import and Export from Access or even SQL Server and the guy even put in a little "blob viewer" so you can look at pictures you've stored in your tables. Oh, and did I mention the price? It's free.

Another issue with SQLite that comes up often (as well as with other Databases) is the issue of using ADO.NET Transactions and prepared statements for inserts.

Rather than launching into a lengthy discussion, I think it would be better to share the comments of Robert Simpson, the developer who single-handedly brought us System.Data.SQLite for ADO.NET 2.0. Robert and I have corresponded frequently; this comes directly out of his help file for the provider, and I am sure he would approve; here it is:

The Importance of Transactions

If you are inserting data in SQLite without first starting a transaction: DO NOT PASS GO! Call BeginTransaction() right now, and finish with Commit()! If you think I'm kidding, think again. SQLite's A.C.I.D. design means that every single time you insert any data outside a transaction, an implicit transaction is constructed, the insert made, and the transaction destructed. EVERY TIME. If you're wondering why in the world your inserts are taking 100x longer than you think they should, look no further.

Prepared Statements
Lets have a quick look at the following code and evaluate its performance:

using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
int n;

for (n = 0; n < 100000; n ++)
{
mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);
mycommand.ExecuteNonQuery();
}
}
This code seems pretty tight, but if you think it performs well, you're dead wrong. Here's what's wrong with it:

I didn't start a transaction first! This insert is dog slow!
The CLR is calling "new" implicitly 100,000 times because I am formatting a string in the loop for every insert Since SQLite precompiles SQL statements, the engine is constructing and deconstructing 100,000 SQL statements and allocating/deallocating their memory All this construction and destruction is involving about 300,000 more native to managed interop calls than an optimized insert.
So lets rewrite that code slightly:

using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
SQLiteParameter myparam = new SQLiteParameter();
int n;

mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
mycommand.Parameters.Add(myparam);

for (n = 0; n < 100000; n ++)
{
myparam.Value = n + 1;
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
Now this is a blazing fast insert for any database engine, not just SQLite. The SQL statement is prepared one time -- on the first call to ExecuteNonQuery(). Once prepared, it never needs re-evaluating. Furthermore, we're allocating no memory in the loop and doing a very minimal number of interop transitions. Surround the entire thing with a transaction, and the performance of this insert is so far and away faster than the original that it merits a hands-on-the-hips pirate-like laugh.

Every database engine worth its salt utilizes prepared statements. If you're not coding for this, you're not writing optimized SQL, and that's the bottom line.

---

Many developers do not fully understand the importance of using prepared statements with parameters where stored procedures (the preferred method in almost all cases) cannot or should not be used. The importance of this, combined with the practice of wrapping repeating inserts or updates in a transaction, cannot be underestimated. If the above all makes sense, then you'll like my next installment, "Did Chuck Norris kill SOA?".