Performance issue with SQLite database (.db)

Question

I am having a performance issue with SQLite database (.db)

I am trying to update 1,00,000 records in database (.db) which taking around 50 minutes. Too much slow.

my code is like below ::

        for (int q = 0; q < list.Count; q++) 
            { 
        ArrayList castarraylist = new ArrayList(); 
        castarraylist = (ArrayList)(list[q]); 

        using (var cmd = new SQLiteCommand(con)) 

            using (var transaction = con.BeginTransaction()) 
            { 
                cmd.Transaction = transaction; 

                for (int y = 0; y < castarraylist.Count; y++) 
                { 
                        cmd.CommandText = Convert.ToString(castarraylist[y]); 
                           cmd.ExecuteNonQuery(); 
                } 
                transaction.Commit(); 
                GC.Collect(); 
            } 
        } 

Here each castarraylist contains 5000 records. which updating into database with transaction. so loop go through 20 times and complete the update all. While I manually check the time it's increasing the time at each iteration for 5000 records. like

1st 5000 records processing time > 1:11 minute

2nd 5000 records processing time > 1:25 minute

3rd  5000 records processing time > 1:32 minute 

4th 5000 records processing time > 1:40 minute 

5th 5000 records processing time > 1:47 minute 

6th 5000 records processing time > 1:52 minute 

...

... 

... 

17th 5000 records processing time > 3:32 minute 

18th 5000 records processing time > 3:44 minute

19th 5000 records processing time > 4:02 minute 

20th 5000 records processing time> 4:56 minute 

Why this happening I don't able to understand. My sourcecode written in C# and my laptop configuration is i5 2.6 GHz, 4 GB RAM, 500 GB HD.

I made connection like below ::

SQLiteConnection con = new SQLiteConnection("Data Source=" + fullPath + ";Version=3;Count Changes=off;Journal Mode=off;Pooling=true;Cache Size=10000;Page Size=4096;Synchronous=off"); 

(*fullpath - is my database path)

I am creating table like below...

sqlquery2="Select LINK_ID from RDF_LINK string createLinkToPoly = "create table temp2 AS " + sqlquery2;

This would creating a table and inserting records which are get through by sqlquery2.

Below statement extends Spatialite on SQLite

ExecuteStatement("select load_extension('spatialite.dll')", con);

My Update statement is like below ::

UPDATE temp2 SET GEOM = Transform(LineStringFromText('LINESTRING(4.38368 51.18109,4.38427 51.18165)',4326),32632)WHERE LINK_ID= 53841546

so This kind of 100000 statement building in different threads and inserting into LIST

at last executing UPDATE statements in above code (now using code of Larry suggested)

Answer 1

Currencly, the transaction is run per query, which makes no sense.

Enclose your main loop code in the transaction, and remove this GC.Collect().

EDIT:

As I understood, you dont want the global update to be rolled back in case of an error. So I changed the code a bit.

Additionally, I am not sure that the command object can be reused by changing the CommandText and running queries again. That's why I suggest to create it every time.

using (var transaction = con.BeginTransaction()) 
{ 
    for (int q = 0; q < list.Count; q++) 
    { 
        var castarraylist = (ArrayList)(list[q]); 

        for (int y = 0; y < castarraylist.Count; y++) 
        { 
            using (var cmd = new SQLiteCommand(con)) 
            {
                cmd.Transaction = transaction; 
                cmd.CommandText = Convert.ToString(castarraylist[y]);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    // Log the update problem
                    Console.WriteLine("Update problem " + cmd.CommandText + " - Reason: " + ex.Message);
                }
            }
        }
    }

    transaction.Commit();
}