Loop over and run SQL query several thousand times

Question

I've read several dozen posts, many dating back years, and cannot come up with a modern, safe and reliable way to update a special value in several thousand records as a single query.

I loop over all the records in the table, determine a DateTime value based on some special logic and then run this simple query to update that value... over 3500 times. That's a lot of trips over the wire.

UPDATE ScheduleTickets
    SET ScheduledStartUTC = @ScheduledStartUTC
    WHERE ScheduleId = @ScheduleId AND PatchSessionId = @PatchSessionId

I've seen comments to not waste memory by saving to and using a DataTable. I've seen solutions that use a StringBuilder to dynamically create an update query but that feels insecure/dirty. Sure, the entire process takes less than a minute but there must be a better way.

So, after figuring out the DateTime value, I call...

UpdateScheduleTicketStart(ScheduleId, PatchSessionId, scheduledDateTime);

Which looks like this...

private static void UpdateScheduleTicketStart(long scheduleId, long patchSessionId, DateTime scheduledStartUTC)
{
    using (SqlConnection c = ConnectVRS())
    {
        SqlCommand cmd = new SqlCommand(@"
            UPDATE ScheduleTickets
                SET ScheduledStartUTC = @ScheduledStartUTC
                WHERE ScheduleId = @ScheduleId AND PatchSessionId = @PatchSessionId
            ", c);
        cmd.Parameters.Add("@ScheduleId", SqlDbType.BigInt).Value = scheduleId;
        cmd.Parameters.Add("@PatchSessionId", SqlDbType.BigInt).Value = patchSessionId;
        cmd.Parameters.Add("@ScheduledStartUTC", SqlDbType.VarChar).Value = scheduledStartUTC;
        cmd.ExecuteNonQuery();
    }
}

How can I pass all the values to SQL Server in one call or how can I create a single SQL query to do the updates in one fell swoop?

Maybe a table valued parameter? Maybe a rethinking about how to determine the correct values to use? Hard to say without some details. — Sean Lange, Mar 16 '18 at 19:44
I am reading this as you don't want to have a loop (somewhere) and call your method for each insert? Your answer would be related to the Stackoverlflow question: https://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp — Epistaxis, Mar 16 '18 at 19:44
A compromise would be to do what the above code does, just in a single transaction instead of making a new connection each time. — Moby Disk, Mar 16 '18 at 19:48
@MobyDisk That's what I asked in my question. I want to perform the update of 3500+ records in a single transaction. Each one has a a different DateTime value but I also need to pass two values to determine which row in the table to update. The two values make up a unique key. — Connie DeCinko CS, Mar 16 '18 at 19:55
@SeanLange I used a TVP many years ago to solve the issue of passing many params to an IN but I don't recall the details of how I solved that. Will go read up on them. — Connie DeCinko CS, Mar 16 '18 at 19:58
You have to define the user defined table type. Then you populate it with data. Inside your procedure you can reference just like any other table. — Sean Lange, Mar 16 '18 at 19:59
@SeanLange Except when your DBA denies you create permissions... — Connie DeCinko CS, Mar 16 '18 at 20:07
Well that should be easy enough. Go to the DBA and say "hey I want to use a table valued parameter for this process instead of a cursor. Can you create this user defined table type for me since I don't have permission?". Either they will create it for you or give you permission. Unless they are clueless and don't understand the benefits of set based processing over RBAR. :) — Sean Lange, Mar 16 '18 at 20:23
@SeanLange Where can I find a good example that doesn't use a stored proc? All the examples so far use SP. — Connie DeCinko CS, Mar 16 '18 at 20:41
I would ask why you aren't using stored procedures for everything. It gives you separation of duties between the database and the application. — Sean Lange, Mar 16 '18 at 20:43
If you can't use a stored proc, your next best alternative is a DataAdapter, but a TVP and stored proc are preferred. — Kevin, Mar 16 '18 at 20:55

score 4 · Answer 1 · answered Mar 16 '18 at 20:45

Many people have suggested using a TableValueParameter, and I agree it would be a good method. Here is an example of how you could do that:

First Create a TVP and Stored Proc in SQL Server

CREATE TYPE [dbo].[SchdeuleTicketsType] As Table
(
    ScheduledStartUTC DATETIME NOT NULL
  , ScheduleId        INT      NOT NULL
  , PatchSessionId    INT      NOT NULL
)


CREATE PROCEDURE [dbo].[usp_UpdateTickets]
(
   @ScheduleUpdates As [dbo].[SchdeuleTicketsType] Readonly
)
   AS
   Begin
        UPDATE t1
        SET t1.ScheduledStartUTC = t2.ScheduledStartUTC
        FROM ScheduleTickets AS t1
            INNER JOIN @ScheduleUpdates AS t2
        ON t1.ScheduleId = t2.ScheduleId AND
           t1.PatchSessionId  = t2.PatchSessionId 
   End
)

Next Modify your code to populate a table and pass that as a parameter to the stored proc:

    private void Populate()
    {
        DataTable dataTable = new DataTable("SchdeuleTicketUpdates");

        //we create column names as per the type in DB 
        dataTable.Columns.Add("ScheduledStartUTC", typeof(DateTime));
        dataTable.Columns.Add("ScheduleId", typeof(Int32));
        dataTable.Columns.Add("PatchSessionId", typeof(Int32));

        //write you loop to populate here

        //call the stored proc
        using (var conn = new SqlConnection(connString))
        {
            var command = new SqlCommand("[usp_UpdateTickets]");
            command.CommandType = CommandType.StoredProcedure;

            var parameter = new SqlParameter();
            //The parameter for the SP must be of SqlDbType.Structured 
            parameter.ParameterName = "@ScheduleUpdates";
            parameter.SqlDbType = System.Data.SqlDbType.Structured;
            parameter.Value = dataTable;
            command.Parameters.Add(parameter);
            command.ExecuteNonQuery();

        }
    }

I agree the TVP is a good way, only way for something I did years ago. Just have to convince the DB guy to allow it. I see everyone suggesting to use a DataTable, wonder why I saw so many comments in other threads to avoid that. — Connie DeCinko CS, Mar 16 '18 at 21:02
There are different ways to do it with a DataTable. Most people would probably frown upon the DataAdapter method, but not the TVP method. — Kevin, Mar 16 '18 at 21:28

score 3 · Answer 2 · answered Mar 16 '18 at 19:44

3

If the values are in another table, use a join:

UPDATE st
    SET ScheduledStartUTC = ot.ScheduledStartUTC
    FROM ScheduleTickets st JOIN
         OtherTable ot
         ON st.ScheduleId = ot.ScheduleId AND st.PatchSessionId = ot.PatchSessionId;

You don't specify the special logic but you can probably express it in SQL.

answered Mar 16 '18 at 19:44

Gordon Linoff

1,242,037
58
646
786

It's a big while loop written by another. It works so don't wish to break it at this time. Will consider it later. – Connie DeCinko CS Mar 16 '18 at 19:46
Well I'll give Gordon a +1 for this as your question is *How can I pass all the values to SQL Server in one call or how can I create a single SQL query to do the updates in one fell swoop?* and this query does exactly that. – Jacob H Mar 16 '18 at 20:24
2

I won't. He says there is special logic in the C# app to calculate the date. What if that logic doesn't port to SQL Server? – Kevin Mar 16 '18 at 20:51

Loop over and run SQL query several thousand times

2 Answers2