2

I am having a performance issue with SQLite database (.db) which extends to SPATIALITE

I am trying to update Geometry of 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)

  • It is good to know that this question has been asked on the Spatialite-users group groups.google.com/forum/#!topic/spatialite-users/IZoJ8sy0wqc and that there is evidence that the slow behaviour comes somehow from using SQLite and Spatialite through C#. – user30184 Jul 4 '14 at 9:38
  • Could you elaborate where list comes from, what type it is and what kind of data is in it? – FObermaier Jul 4 '14 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.