UPDATE is faster in SQLite + BEGIN TRANSACTION

Question

This is also related to space (not just SQLite)

I have a file database ( xyz.db ) that I use SQLiteconnection ( SQLiteconnection continues to spatial).

I have so many records that need to be updated in the 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; } 

In the above logic, castarraylist has an Arraylist that contains the value that needs to be processed in the database.

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

Is there a way to improve performance?

NOTE :: (The file database is not thread safe)

2. BEGINNING OF THE OPERATION

Suppose I like to run two (or millionth) update statements with a single transaction in Spatialite .. is this possible?

I read online and prepare the below expression for me (but not successful)

 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; 

The above statement does not update records in my database. SQLite does not support BEGIN TRANSACTION? is there anything i don't see?

And if I need to run a separate instruction, then it will take too much time to update, as mentioned above ...

Answer 1

SQLite transaction support, you can try to execute the code below.

 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(); } 

Answer 2

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

• Reusing this SQLiteCommand object , changing its CommandText property and executing it again and again, may be faster, but will run out of memory . If you have a large number of requests to execute, it’s best to delete the object after use and create a new one,

Common ADO.NET transaction template:

 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; } }