Update table with index is too slow

Question

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!

EDIT

I just realized I was assuming the wrong thing.
The complete query has another part that is responsible for the delay:

DECLARE @LrbId as bigint
SELECT TOP 1 @LrbId = LrbId
FROM Buffertable
WHERE LrbProcessed = 0
    AND LrbCount < 5
ORDER BY LrbInserted

So, most probably it's related with a bad decision from the Sql engine on which index to use.
Sorry for the confusion. I guess we can close this question.

Answer 1

Can someone explain why index2 is much heavier to update then index1?

index2 is much longer: key size is 10 bytes (8 + 1 + 1) rather than 2 (1 + 1)

Probably it does not fit into the cache and a page lookup is needed to locate the records.

How large your table is?

You may also want to enable I/O statistics:

SET STATISTICS IO ON

, run the query a couple of times and see the number of physical page reads in the output.

Update:

For this query:

SELECT TOP 1 @LrbId = LrbId
FROM   Buffertable
WHERE  LrbProcessed = 0
       AND LrbCount < 5
ORDER BY
       LrbInserted

to work fast, create the following index:

CREATE INDEX ix_buffertable_p_c_i ON BufferTable (LrbProcessed, lrbCount, LrbInserted)

and rewrite the query:

WITH    cts (cnt) AS
        (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    cts
        WHERE   cnt < 5
        )
SELECT  TOP 1 bt.*
FROM    cts
CROSS APPLY
        (
        SELECT  TOP 1 bti.*
        FROM    BufferTable bti
        WHERE   LrbProcessed = 0
                AND LrbCount = cts.cnt
        ORDER BY
                LrbInserted
        ) bt
ORDER BY
        LrbInserted

Answer 2

Is there an index on LrbId, or is it a primary key? If not, adding one should improve updates in general.

Note that if you get multiple updates in different sessions, there may be some concurrency issues as the updates modify the indexes.

Index 2 is also larger to update, as Quassnoi mentioned.