We are using MS SQL Server 2005.
Hi, i am performing UPDATE statement on a database table. Lets say this table has next colums:
int Id PK
int Column1
int Column2
It also has several Index:
Unique Clustered (Id)
Non-Unique Non-Clustered (Column1)
Non-Unique Non-Clustered (Column2)
I do next operation:
UPDATE [dbo].[Table]
SET Column1 = @Value1
WHERE Column1 = @Param1
AND Column2 = @Param2
Actual execution plan after that looks like this:
Which says that 86% of time was spent on updating clustered index, which does not include column i have just changed.
This operation should run hundreds of thousands times with web application disabled, which means it is very time critical.
So, does anybody have any idea why things are going this way and if it can be fixed somehow? Does this question make any sense? I am ready to provide more information if needed.
A clustered index is useful for range queries because the data is logically sorted on the key. You can move a table to another filegroup by recreating the clustered index on a different filegroup. You do not have to drop the table as you would to move a heap.
A non-clustered index doesn't sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another.
A clustered index is an index which defines the physical order in which table records are stored in a database. Since there can be only one way in which records are physically stored in a database table, there can be only one clustered index per table. By default a clustered index is created on a primary key column.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
The "clustered index" is the actual table. All of the columns of the table are in the "clustered index" (with some exceptions for "out of row" storage for lobs, etc.)
When you change the value of a column, it has to be changed in the table pages, as well as in any index that the column appears in.
In terms of performance for quickly locating the rows to be updated (for your particular query), an index on dbo.Table(Column1,Column2)
or dbo.Table(Column2,Column1)
would be the most appropriate.
If it's possible that the column being modified already has the value being assigned (i.e. @Param1 and @Value both represent the same value, then adding another predicate may improve performance by avoiding a lock being obtained on the row.
UPDATE [dbo].[Table]
SET Column1 = @Value1
WHERE Column1 = @Param1
AND Column2 = @Param2
AND Column1 <> @Value1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With