StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdate table with index is too slow
primarykey
Id
2524234
data
AcceptedAnswerId
0
AnswerCount
2
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-03-26T15:06:41.303
FavoriteCount
1
LastActivityDate
2010-03-26T16:22:21.593
LastEditDate
2010-03-26T15:58:03.183
LastEditorUserId
44547
OwnerUserId
44547
ParentId
0
PostTypeId
1
Score
3
ViewCount
5078
LastEditorDisplayName
text
Body
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) <pre><code>UPDATE BufferTable SET LrbCount = LrbCount + 1, LrbUpdated = getdate() WHERE LrbId = @LrbId </code></pre> This is the table <pre><code>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, ) </code></pre> 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: <pre><code>DECLARE @LrbId as bigint SELECT TOP 1 @LrbId = LrbId FROM Buffertable WHERE LrbProcessed = 0 AND LrbCount < 5 ORDER BY LrbInserted </code></pre> 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.
Tags
<sql-server><performance><indexing>
Title
Update table with index is too slow
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USpauloya
UserOwnerUserId
1. USpauloya
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POUpdate table with index is too slow
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POUpdate table with index is too slow
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POUpdate table with index is too slow
 UserUserId
 USQuassnoi
 VoteTypeVoteTypeId
 VTFavorite
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.