How to optimize 1000 sql query for update?

Question

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?

Can you also post the `_connectorFunctions.UpdatePermissionResult` code? — Sowmyadhar Gourishetty, Aug 21 '20 at 14:35

score 4 · Accepted Answer · answered Aug 21 '20 at 14:50

4

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

answered Aug 21 '20 at 14:50

Sowmyadhar Gourishetty

1,843
1
8
15

2

This is particularly good because you can create an index on the type. This THEN allows the update to actually use the index statistics to find an optimal approach. – TomTom Aug 21 '20 at 14:55
Thank you @sowmyadhar, it is a good idea. I create a temp table and update the main table with join temp table. The performance was increased. – OguzKaanAkyalcin Aug 24 '20 at 07:16

score 1 · Answer 2 · answered Aug 21 '20 at 14:05

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.

Congrats on your 1M rep milestone. – Dan Guzman Aug 21 '20 at 15:13 — Dan Guzman, Aug 21 '20 at 15:13

How to optimize 1000 sql query for update?

2 Answers2