UPDATE faster in SQLite + BEGIN TRANSACTION

Question

This one is related to spatilite also (not only SQLite)

I have a file database (xyz.db) which I am using by SQLiteconnection (SQLiteconnection is extends to spatialite).

I have so many records needs to update into database.

                for (int y = 0; y < castarraylist.Count; y++)
                {
                    string s = Convert.ToString(castarraylist[y]);

                    string[] h = s.Split(':');

                    SQLiteCommand sqlqctSQL4 = new SQLiteCommand("UPDATE temp2 SET GEOM = " + h[0] + "WHERE " + dtsqlquery2.Columns[0] + "=" + h[1] + "", con);
                    sqlqctSQL4.ExecuteNonQuery();

                    x = x + 1;
                }

At above logic castarraylist is Arraylist which contains value which need to process into database.

When I checked above code updating around 400 records in 1 minute.

Is there any way by which I can able to improve performance ?

NOTE :: (File database is not thread-safe)

2. BEGIN TRANSACTION

Let's suppose I like to run two (or millions) update statement with single transaction in Spatialite.. is it possible ?

I read online and prepare below statement for me (but not get success)

BEGIN TRANSACTION;
UPDATE builtuparea_luxbel SET ADMIN_LEVEL = 6 where PK_UID = 2;
UPDATE builtuparea_luxbel SET ADMIN_LEVEL = 6 where PK_UID = 3;
COMMIT TRANSACTION; 

Above statement not updating records in my database. is SQLite not support BEGIN TRANSACTION ? is there anything which I missing ?

And If I need to run individual statement then it's taking too much time to update as said above...

Durante

Answer 1

SQLite support Transaction, you can try below code.

using (var cmd = new SQLiteCommand(conn))
using (var transaction = conn.BeginTransaction())
{
    for (int y = 0; y < castarraylist.Count; y++)
    {
        //Add your query here.
        cmd.CommandText = "INSERT INTO TABLE (Field1,Field2) VALUES ('A', 'B');";
        cmd.ExecuteNonQuery();
    }
    transaction.Commit();
}

Augmentation

how this logic is differ then above ? as here we also running for loop and it's looping for 1000 times – Durante

you can see above query will execute for all records in castarraylist, after completion transaction will commit, you can try above code and see result. – Augmentation

yes it's improved but still it's not effective. For 100000 records it taking >50 minutes. So I have manually gathered time for executing each bunch of 5000 records. Where I noticed it's incrementing time while each bunch. 5000 records processing time 1st > 1:11 minute 2nd > 1:25 minute 3rd > 1:32 minute 4th > 1:40 minute 5th > 1:47 minute 6th > 1:52 minute ... ... ... 17th > 3:32 minute 18th > 3:44 munite 19th > 4:02 minute 20th > 4:56 minute why is it so ? need to run some other memory increment command ? – Durante

You should not be reusing the SQLiteCommand object for such an important amount of queries to perform: it causes a memory overhead. – Gigantic

Answer 2

• The primary goal of a database transaction to get everything done, or nothing if something fails inside;

• Reusing the same SQLiteCommand object by changing its CommandText property and execute it again and again might be faster, but leads to a memory overhead: If you have an important amount of queries to perform, the best is to dispose the object after use and create a new one;

A common pattern for an ADO.NET transaction is:

using (var tra = cn.BeginTransaction())
{
    try
    { 
        foreach(var myQuery in myQueries)
        { 
            using (var cd = new SQLiteCommand(myQuery, cn, tra))
            {
                cd.ExecuteNonQuery();
            }
        }

        tra.Commit();
    }
    catch(Exception ex)
    {
        tra.Rollback();
        Console.Error.Writeline("I did nothing, because something wrong happened: {0}", ex);
        throw;
    }
}

Gigantic

How do you know it leads to a memory overhead? – Remittent

The private memory usage in task manager. It does not occur when I dispose the SQLiteCommand properly. This behavior has been also seen here. Also here. By the way, I am interrested if there is a solution to this :) – Gigantic