SQL Server 2008 R2.
Running a simple Update command that looks like this
UPDATE [group_mtm] SET group = foobar where user in (u1,u2,u3,...u19)
The query only updates 1 row but takes in excess of 2 seconds. This is a 2 attribute table used as a many-to-many junction. Table only has about 300 records right now. Primary key = composite key on group and user attributes.
I've set stats on to examine it and the query execution involves a bunch of other tables that have no business being involved:
Table 'foobar'. Scan count 1, logical reads 21214, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1664, logical reads 42674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mail_mtm'. Scan count 1, logical reads 428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'event'. Scan count 1, logical reads 893, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'user'. Scan count 0, logical reads 91, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'addresses'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mail'. Scan count 1, logical reads 79, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'links'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'group_mtm'. Scan count 20, logical reads 120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(20 row(s) affected)
(1 row(s) affected)
The only thing i can think of is that foobar is and indexed view, and perhaps the cost is being spent on maintaining it, so i checked the execution plan; it does indeed come into play (but only an 11% cost).
Why is it taking 2+ seconds?
What am i over-looking here?
The execution plan advises that i add an index on event, but event shouldn't even be involved here and its only a 13% cost.
I feel like this should be lightning quick if it weren't for the other tables being pulled into this for some reason.
Someone is probably going to give me sage advice about indexed views increasing general DB overhead; but all other operations going down the toilet seems like a pretty steep cost for faster look ups on certain queries afforded by indexed views.
Teach me; please and thank you!
XML execution plan is way too big to post as text and I dont see an option to attach, so pastebin to the rescue: http://pastebin.com/BgjBxLfc
This might not be the whole answer, but expand the sql query to further qualify the field names: [table].[field]. I assume 'group' is a field in group_mtm, but it looks like you're trying to assign the 'foobar' table to it?