Update table with index is too slow
- by pauloya
Hi,
I was watching the Profiler on a live system of our application and I saw that there was an update instruction that we run periodically (every second) that was quite slow. It took around 400ms every time.
The query includes this update (which is the slow part)
UPDATE BufferTable
SET LrbCount = LrbCount + 1,
LrbUpdated = getdate()
WHERE LrbId = @LrbId
This is the table
CREATE TABLE BufferTable(
LrbId [bigint] IDENTITY(1,1) NOT NULL,
...
LrbInserted [datetime] NOT NULL,
LrbProcessed [bit] NOT NULL,
LrbUpdated [datetime] NOT NULL,
LrbCount [tinyint] NOT NULL,
)
The table has 2 indexes (non unique and non clustered) with the fields by this order:
* Index1 - (LrbProcessed, LrbCount)
* Index2 - (LrbInserted, LrbCount, LrbProcessed)
When I looked at this I thought that the problem would come from Index1 since LrbCount is changing a lot and it changes the order of the data in the index.
But after desactivating index1 I saw the query was taking the same time as initially.
Then I rebuilt index1 and desactivated index2, this time the query was very fast.
It seems to me that Index2 should be faster to update, the order of the data shouldn't change since the LrbInserted time is not changed.
Can someone explain why index2 is much heavier to update then index1?
Thank you!
