Looping taking too long to execute

Please find part of my code below, but it seems the looping part of it is taking too much time to execute, how can I improve the speed.

string gss = "select sn,hcpcode from capitation";
SqlCommand cgg = new SqlCommand(gss, gap);
reader = cgg.ExecuteReader();
while (reader.Read())
{
ar.Add(reader["sn"].ToString());
arHosp.Add(reader["hcpcode"].ToString());
}
reader.Close();

for (int x = 0; x < ar. count; x++)

{

string sss = "update capitation set surname = null where surname like '%Page%' or surname like '%healthcare%' ";
SqlCommand ccc = new SqlCommand(sss, gap);
ccc.ExecuteNonQuery();

string kss = "update capitation set hcpcode = (select oldnhisno from capitation where oldnhisno like '%/%' and sn = @hs) where sn = @ssn";
SqlCommand ckk = new SqlCommand(kss, gap);
ckk.Parameters.AddWithValue("@hs", ar[x].ToString());
ckk.Parameters.AddWithValue("@ssn", ar[x].ToString());

ckk.ExecuteNonQuery();

}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra9y ago
You are executing two sql statements inside for loop. Secondly, it is hitting database multiple times and it takes time. So toavoid the problem, create SP(Stored Procedure) and pass necessary information. Inside SP, you need to write all logic to insert/update database.
+1