1

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
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Muthukrishnan R
  • 159
  • 1
  • 15
  • so what is the problem, if you million data, then simply logic it take time to update? and 2 second is not too much time. Are you use indexed columns like primary-key column in your where clause. if not, that will give the benefit. – Ajay2707 May 30 '16 at 05:48
  • You could improve the chance of getting a useful answer by providing the query plan. – Jeff May 30 '16 at 05:57
  • Because I will run SQL query command continuously. Per day I need to run 88000 query to update or insert row(s) in this table. If one single query takes 2 seconds then I cant run 88000 queries per day. Please help me on this. (To Ajay2707) – Muthukrishnan R May 30 '16 at 06:05
  • @MuthukrishnanR can you queue the changes and run them in batches, rather than one at a time? If you could reduce it to just, say, 88 runs of 1000 updates (or even 880 runs of 100 updates) it could avert the problem completely. – Jeff May 30 '16 at 06:17
  • you can try batch update – bmsqldev May 30 '16 at 06:51
  • 2 seconds for a single update is well off the pace. Can you publish your table definitions and declaratives please. Nothing to do with the problem but if you are going to use reserved words such as format in your tables it would be better if you escaped them - [Format]. – P.Salmon May 30 '16 at 06:54

4 Answers4

2

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.

Jeff
  • 12,555
  • 5
  • 33
  • 60
  • Should I use NON-CLUSTER index for SM and Charactersric? – Muthukrishnan R May 30 '16 at 06:17
  • My Query Plan I will run SQL query command continuously. Per day I need to run 88000 query to update or insert row(s) in this table. If one single query takes 2 seconds then I cant run 88000 queries per day. Please help me on this – Muthukrishnan R May 30 '16 at 06:18
  • @MuthukrishnanR I don't know. You'll have to look into whether the table is queried frequently in other situations, whether it currently has a clustered index, and whether it even makes sense to add an index to them. If they aren't unique, though, I would be **very** cautious about making it a clustered index, and even if they are I would be cautious. – Jeff May 30 '16 at 06:19
  • @MuthukrishnanR that's not what I mean by a query plan. SQL server management studio can typically generate a query plan for you - have a google about how to do it - it should look like an image of some kind. – Jeff May 30 '16 at 06:19
  • I have applied Clustered Index for "SM" and Non-Clustered Index for "Characterstic". Now query execution is very fast. Thank you Jeff. But I have one doubt, should clustered and non clustered index column be unique? – Muthukrishnan R May 30 '16 at 06:59
  • Clustered should really be unique. If it's not, you should create a single index across **both** columns - if the combination of the two is unique, then you can make it clustered, otherwise make it non-clustered. **If you don't know what you're doing**, make it non-clustered. take a look at [this question](http://stackoverflow.com/q/179085/781965) for some useful context around multi-column indexes – Jeff May 30 '16 at 07:06
1

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 !

Sami
  • 3,686
  • 4
  • 17
  • 28
  • 1
    Take care with that loop - since `SM` and `Characteristic` aren't being updated, it's likely to just continuously update the same rows. Deleting indexes and triggers are obviously risky moves, and you should make sure you know all the ramifications before you do it - a compromise might be to disable the index while doing updates, then re-enable it after, if you're making a lot of updates. – Jeff May 30 '16 at 05:59
  • But In this query execution, System always give First 20000 matched records always right. How will it give other record matches? – Muthukrishnan R May 30 '16 at 06:02
  • Does "Where" clause assume to be changed on every update cycle ?? When first 20000 rows are updated, those rows skipped from the result-set picked up for the next update batch. Hope this makes sense or correct me if I am missing something. – Sami May 30 '16 at 06:04
  • Sami,, I have updated what u have given and ran the query. Its taking more than 3 seconds to complete. – Muthukrishnan R May 30 '16 at 06:13
1

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.
Midhun m k
  • 66
  • 3
1

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.

dean
  • 9,960
  • 2
  • 25
  • 26