Retrieve & update 20000 data records stops working

Question

Hi everyone,

I am using the following code do get all data records from a MS SQL database and I try to update every single record. The code is used in a WebService. The issue is, that the code runs fine if I have 1000 data records but now I have 20000 data records an the code first returned with an timeout. Then I set the cmd.CommandTimeout to zero to have no timeout. Now when I invoke the function in the IE WebSvc the IE window is still blank and still try to load something but nothing happens. Only 150 datarecords are updated.

Do you have any idea where the issue might be ? Is the code not the best, so what should I change ?

Thank you very much! WorldSignia

MyCode:

 private string AddNewOrgBez()
        {
            try
            {
                SqlConnection sqlconn = new SqlConnection(this.connectionString);
                SqlCommand cmd;
                SqlDataReader reader;
                sqlconn.Open();

                cmd = new SqlCommand("SELECT * FROM dbo.mydata", sqlconn);
                cmd.CommandTimeout = 0;
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    // Felder holen
                    string okuerzel = reader["O_KURZ"].ToString();
                    string bezeichnung = reader["O_BEZ"].ToString();

                    string[] lines = CreateNewOrgBez(bezeichnung);

                    string sqlcmd = "UPDATE dbo.mydata SET WEB_OBEZ1 = '" + lines[0] + "', WEB_OBEZ2 = '" + lines[1] + "', WEB_OBEZ3 = '" + lines[2] + "' WHERE O_KURZ = '" + okuerzel + "'";

                    SqlConnection sqlconn2 = new SqlConnection(this.connectionString);
                    sqlconn2.Open();
                    SqlCommand cmd2 = new SqlCommand(sqlcmd, sqlconn2);
                    cmd2.CommandTimeout = 0;
                    cmd2.ExecuteNonQuery();
                    sqlconn2.Close();
                }

                reader.Close();
                sqlconn.Close();

                return "OK";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

Answer 1

I doubt if your running into OutOfMemoryException. Can you profile your application and check the memory usage?

Since you are just overwriting the variables in While loop, why don't you try taking them out of the loop.

Answer 2

You are leaking every SqlCommand here - I suggest you review your use of SqlClient classes to find the ones that are IDisposable and restructure your code to ensure they are always freed, using the using construct.

For example, this ensures Dispose gets called even if there is an exception in the bracketed code:

using (SqlCommand cmd2 = new SqlCommand(sqlcmd, sqlconn2))
{
  cmd2.CommandTimeout = 0;
  cmd2.ExecuteNonQuery();
}

Using a new SqlConnection for every UPDATE is expensive too, this should be done outside the loop. Redundant connection establishment is likely the explanation for your timeout.

Take note of @ck's comment that for efficiency this type of piecemeal client-side operation is not as good as doing the heavy lifting server-side. You should be able to get this code working better, but that does not mean it's the ideal/fastest solution.

Answer 3

I found the issue. You need first to get all the data records, for example in a new DataTable. The structure I used does not work, because it reads data from the database and also updates the database. After changing it to a new structure it works.

Answer 4

You were using two different connections to read and to update, and one of them was blocking another. This is why when you read all your data first, it began to work.