How to optimize 1000 sql query for update?

user_13064668 0
Viewed 167

I have a console application based on .net standard. This console application sends a list to a Web API.

For example, my class is something like;

public class Person
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

The Web API accepts 1000 records in a single request. The process binds List variable and sends the web API. Web Api response is something like

public class PersonResponse
{
    public Guid PersonId { get; set; }
    public Guid ApiId{ get; set; }
}

Now, I need to update my SQL Server table and I create an update statement like

string query = "UPDATE Person SET ApiId = @apiId WHERE Id = @personId";

My main issue is, I have to execute this update statement for 1000 times. I open one SQL connection and start to update that 1000 record one by one. I don't like the way how I did this. What is the best way to update these 1000 records?

enter image description here

tsql sql-server sql
2 Answers

One thing I would suggest is to create a table type like

CREATE TYPE [dbo].[UpdatePersons] AS TABLE (
    ApiId INT
    ,PersonId INT
    )

Create a SPROC to update the data in it by passing this table type as a parameter

CREATE PROC dbo.UpdateApiIds @updates [dbo].[UpdatePersons] READONLY
AS
BEGIN
    UPDATE p
    SET p.ApiId = u.ApiId
    FROM Persons p
    JOIN @updates u ON p.PersonId = u.PersonId
END

From your code, you can create the data table for the records which you need to update and call the dbo.UpdateApiIds SPROC by passing the list.

View this link for passing the data table from c# while calling a Stored Procedure

user_81440160

The simplest method is to load the 1000 rows into a table and use:

UPDATE p
    SET ApiId = nv.apiId 
    FROM Person p JOIN
         new_values nv
         ON nv.id = p.id;

This is particularly useful if your new values come from an existing table or query. You can just include that in the query.

SQL Server also lets you specify the values directly:

UPDATE p
    SET ApiId = nv.apiId 
    FROM Person p JOIN
         (VALUES (?, ?),
                 (?, ?),
                 . . .
         ) nv(id, apiId)
         ON nv.id = p.id;

This is a little cumbersome to set up for 1000 rows, though.

user_11440350
Related