CODEWITHSUNDEEP
c#sql
SupaOden
SupaOden

Reputation: 742

SQL code is timing out

My SQL code inserts 10,000 records into a table from list. If record already exists, it updates a few fields.

Currently it taking more than 10 minutes and timing out unless I restrict the number of records to process. Is there anything in my code which I can do to solve this problem.

   foreach(RMSResponse rmsObj in rmsList) {
        try {
            string connectionString = @"server=localhost\sqlexpress;" + "Trusted_Connection=yes;" + "database=MyDB; " + "connection timeout=30";

            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            string str = "";
            str += "SELECT * ";
            str += "FROM RMSResponse ";
            str += "WHERE LeadID = @RecruitID";
            int LeadID;
            Boolean IsPositive = true;

            SqlCommand selectCommand = new SqlCommand(str, conn);
            selectCommand.CommandType = CommandType.Text;
            selectCommand.Parameters.Add(new SqlParameter("@RecruitID", rmsObj.RecruitID));
            SqlDataReader sqlDataReader = selectCommand.ExecuteReader();
            bool hasRows = sqlDataReader.HasRows;

            while (sqlDataReader.Read()) {
                LeadID = sqlDataReader.GetInt32(1);
                IsPositive = sqlDataReader.GetBoolean(2);
                IsPositive = (IsPositive == true) ? false : true;
                Console.WriteLine("Lead ID: " + LeadID + " IsPositive: " + IsPositive);
            }

            sqlDataReader.Close();

            if (hasRows) {
                SqlCommand updateCommand = new SqlCommand("UPDATE RMSResponse set IsPositive=@IsPositive, OptOutDate=@OptOutDate where LeadID=@LeadID", conn);
                updateCommand.Parameters.AddWithValue("@LeadID", rmsObj.RecruitID);
                updateCommand.Parameters.AddWithValue("@IsPositive", IsPositive);
                updateCommand.Parameters.AddWithValue("@OptOutDate", DateTime.Now);
                updateCommand.ExecuteNonQuery();
                sqlDataReader.Close();
            }

            if (!hasRows) {
                SqlCommand insertCommand = new SqlCommand("INSERT INTO RMSResponse (LeadID, IsPositive, ReceivedDate) " + "VALUES(@LeadID, @IsPositive, @ReceivedDate)", conn);
                insertCommand.Parameters.AddWithValue("@LeadID", rmsObj.RecruitID);
                insertCommand.Parameters.AddWithValue("@IsPositive", true);
                insertCommand.Parameters.AddWithValue("@ReceivedDate", DateTime.Now);
                int rows = insertCommand.ExecuteNonQuery();
            }
        } catch (SqlException ex) {
            Console.WriteLine(ex.Message);
        }
    }

Upvotes: 1

Views: 229

Answers (2)

nunespascal
nunespascal

Reputation: 17724

A simple update or insert statement should never take 10 mins.

SqlServer is a very good database.

Create an index on LeadID on your table RMSResponse.

If your foreach is looping over many records, definitely consider a stored procedure, stored procedures will reduces a lot of the time taken.

If you want to Update or Insert(UPSERT) look at the Merge command added in SqlServer 2008.

Upvotes: 2

cjk
cjk

Reputation: 46415

You can move the update to SQL - all you are doing is setting the OptOutDate to be today. You can pass in the list of lead IDs to a batch update statement.

To insert records, you could bulk insert into a staging table, then execute SQL to insert the data for IDs that aren't already in the table.

There isn't much logic in your C#, so pulling the data out then putting it back in is making it unnecessarily slow.

If you don't want to go down this root, then other tips include:

  • Open one connection outside of the loop
  • Create one SqlCommand object outside of the loop and reuse it by resetting the paramenters
  • Change your select SQL to only select the columns you need, not *

Upvotes: 2

Related Questions