Why does my SQL update for 20.000 records take over 5 minutes?

user_1444175 0
Viewed 340

I have a piece of C# code, which updates two specific columns for ~1000x20 records in a database on the localhost. As I know (though I am really far from being a database expert), it should not take long, but it takes more than 5 minutes.

I tried SQL Transactions, with no luck. SqlBulkCopy seems a bit overkill, since it's a large table with dozens of columns, and I only have to update 1/2 column for a set of records, so I would like to keep it simple. Is there a better approach to improve efficiency?

The code itself:

public static bool UpdatePlayers(List<Match> matches)
    {
        using (var connection = new SqlConnection(Database.myConnectionString))
        {
            connection.Open();
            SqlCommand cmd = connection.CreateCommand();

            foreach (Match m in matches)
            {
                cmd.CommandText = "";
                foreach (Player p in m.Players)
                {
                    // Some player specific calculation, which takes almost no time.
                    p.Morale = SomeSpecificCalculationWhichMilisecond();
                    p.Condition = SomeSpecificCalculationWhichMilisecond();


                    cmd.CommandText += "UPDATE [Players] SET [Morale] = @morale, [Condition] = @condition WHERE [ID] = @id;";
                    cmd.Parameters.AddWithValue("@morale", p.Morale);
                    cmd.Parameters.AddWithValue("@condition", p.Condition);
                    cmd.Parameters.AddWithValue("@id", p.ID);
                }
                cmd.ExecuteNonQuery();
            }
        }
        return true;
    }
sql-server sql c#
2 Answers

Updating 20,000 records one at a time is a slow process, so taking over 5 minutes is to be expected.

From your query, I would suggest putting the data into a temp table, then joining the temp table to the update. This way it only has to scan the table to update once, and update all values.

Note: it could still take a while to do the update if you have indexes on the fields you are updating and/or there is a large amount of data in the table.

Example update query:

    UPDATE P
    SET [Morale] = TT.[Morale], [Condition] = TT.[Condition]
    FROM        [Players] AS P
    INNER JOIN  #TempTable AS TT ON TT.[ID] = P.[ID];

Populating the temp table

How to get the data into the temp table is up to you. I suspect you could use SqlBulkCopy but you might have to put it into an actual table, then delete the table once you are done.

If possible, I recommend putting a Primary Key on the ID column in the temp table. This may speed up the update process by making it faster to find the related ID in the temp table.

user_6418330

Minor improvements;

  • use a string builder for the command text
  • ensure your parameter names are actually unique
  • clear your parameters for the next use
  • depending on how many players in each match, batch N commands together rather than 1 match.

Bigger improvement;

  • use a table value as a parameter and a merge sql statement. Which should look something like this (untested);
CREATE TYPE [MoraleUpdate] AS TABLE (
    [Id] ...,
    [Condition] ...,
    [Morale] ...
)
GO

MERGE [dbo].[Players] AS [Target]
USING @Updates AS [Source]
ON [Target].[Id] = [Source].[Id]

WHEN MATCHED THEN
    UPDATE SET SET [Morale] = [Source].[Morale],
        [Condition]  = [Source].[Condition]

DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(...));
dt.Columns.Add("Morale", typeof(...));
dt.Columns.Add("Condition", typeof(...));

foreach(...){
    dt.Rows.Add(p.Id, p.Morale, p.Condition);
}

SqlParameter sqlParam = cmd.Parameters.AddWithValue("@Updates", dt);
sqlParam.SqlDbType = SqlDbType.Structured;
sqlParam.TypeName = "dbo.[MoraleUpdate]";

cmd.ExecuteNonQuery();

You could also implement a DbDatareader to stream the values to the server while you are calculating them.

user_41398090
Related