CODEWITHSUNDEEP
sql-server
Muthukrishnan R
Muthukrishnan R

Reputation: 159

Update query is taking 2 seconds to update the table

I have a table in SQL DB Server. (Table Name : Material, 6 columns). It contains 2.6 million records. I need to update this table based on two column values. For update, system is taking 2 seconds.

Please help me how optimize below query.

UPDATE Material
    SET Value = @Value,
        Format = @Format,
        SValue = @SValue,
        CGroup = @CGroup
WHERE 
    SM = @SM
    AND Characteristic = @Characteristic

Upvotes: 1

Views: 2093

Answers (4)

dean
dean

Reputation: 10098

2.6 mil rows is not that much. 2 secs for an update is probably too much.

Having said that, the update times could depend on two things.

First, how many rows are being updated with a single update command, ie is it just one row or some larger set? You can't really do much about that, just saying it should be taken into consideration.

The other thing are indexes - you could either have too many of then or not enough.

If the table is missing an index on (SM, Characteristic) -- or (Characteristic, SM), depending on the selectivity -- then it's probably a full table scan every time. If the update touches only a couple of rows, it's waste of time. So, it's the first thing to check.

If there are too many indexes on the affected columns, this could slow down updates as well, because those indexes have to be maintained with every change of data. You can check the usefulness of indexes by querying the sys.dm_db_index_usage_stats DMV (plenty of explanation on the internet, so I won't get into it here) and remove the unused ones. Just be carefull with this and test thoroughly.

One other thing to check is whether the affected columns are part of some foreign key constraint. In that case, the engine must check the validity of the constraint every time (iow, check if the new value exists in the referenced table, or check if there's data in referencing tables, depending on which side of the FK the column is). If there are no supporting indexes for this check, it would cause (again) a scan on the other tables involved.

But to really make sure, check the exec plan and IO stats (SET STATISTICS IO ON), it will tell you exactly what is going on.

Upvotes: 1

Midhun m k
Midhun m k

Reputation: 66

Try to put composite index for SM & Characteristic .By doing this, the sql server will be able to select records more easily. Operational wise, Update is a combination of insert & delete.If your table is having more columns, it may slow down your update even if you are not try to update all the columns.

Steps i prefer

  1. Try to put composite index with SM & Characteristic
  2. Try to re create a table with required columns & use joins where ever needed.

Upvotes: 1

Jeff
Jeff

Reputation: 12785

You really need to provide the query plan before we can tell you with any certainty what, if anything, might help.

Having said that, the first thing I would check is whether the plan is showing a great deal of time doing a table scan. If so, you could improve performance substantially if it is a large table by adding an index on SM and Characteristic - that will allow the profiler to use the index to perform an index seek instead of a table scan, and could improve performance dramatically.

Upvotes: 2

Sami
Sami

Reputation: 3800

As you got big data few tweaks can increase query performance

(1) If column to be updated is indexed, remove index

(2) Executing the update in smaller batches 

DECLARE @i INT=1 
WHILE( @i <= 10 ) 
  BEGIN
      UPDATE TOP(20000) Material
    SET Value = @Value,
        Format = @Format,
        SValue = @SValue,
        CGroup = @CGroup
WHERE 
    SM = @SM
    AND Characteristic = @Characteristic

    SET @i=@i + 1 
  END

(3) Disabling Delete triggers (if any)

Hope this helps !

Upvotes: 2

Related Questions