CODEWITHSUNDEEP
c#sqldatabaseperformancems-access
user1205115
user1205115

Reputation:

Update MS Access db in C# best way

My update code:

 for (int i = this.MedaxilGridView1.CurrentCell.RowIndex; i < this.MedaxilGridView1.RowCount; i++)
 {
     // KartsifarishiGridView-dən id götürüb ona uyğun sorğumuzu yazırıq.
     sqlSorgu = "UPDATE customer set medaxil_status = '0' WHERE id = " + 
                 this.MedaxilGridView1.Rows[i].Cells["id"].Value;
     //Sorğunu icra edirk.
     Program.esas.sqlSorguCommand.CommandText = sqlSorgu;
     Program.esas.sqlSorguCommand.Connection = Program.esas.bazayaQosul;
     Program.esas.sqlSorguCommand.ExecuteNonQuery();
     MedaxilGridView1.Rows[i].DefaultCellStyle.BackColor = Color.Empty;

 }

Row count : 18286

This version takes 5 minutes

How do I make it faster?

Upvotes: 0

Views: 841

Answers (3)

Felice Pollano
Felice Pollano

Reputation: 33252

You should think which query you use to extract the id's and use it directly into the update, so you call the database once:

"UPDATE customer set medaxil_status = '0' WHERE id in (select xxx xxx xxx)"

if you want to update all the rows, just remove the where clause and call the statement just once. If you have just an id list, maybe chunking the calls by using alwais the IN clause will reduce the number of queryes and hopefully the overall execution time.

Upvotes: 1

Larry
Larry

Reputation: 18031

Have you tried to enclose your loop in a Transaction ? This speeds up MS Access update operations.

Take a look on this sample here that shows how to use MS Access Transaction in C#

Upvotes: 0

Shai
Shai

Reputation: 25619

You can use BeginExecuteNonQuery for that, that will save you the connection initiation overhead, by opening one connection, and batch executing all of your queries.

A full example from MSDN can be found here

Furthermore, I would advise you to start using SQL Parameters

Upvotes: 0

Related Questions