0

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

zentechinc
  • 369
  • 1
  • 5
  • 15
  • 1
    Are there any triggers on the table? sp_HelpTrigger 'group_mtm' – George Mastros Mar 02 '15 at 22:28
  • Can you publish the actual execution plan (Ctrl + M and Execute/ F5) as XML ? There are any *Scan, *Lookup, Sort operators ? – Bogdan Sahlean Mar 02 '15 at 22:33
  • negative. Only 2 triggers in the entire database and both of those are on a table not included in the x-plan. We've tried to keep the sucker pretty spartan. Good thought though. As per publishing the x-plan, its kinda hard to sanitize... – zentechinc Mar 02 '15 at 22:35
  • Wait a minute, think i've got sql sentry plan explorer on this machine, and that has a sanitize option. 1 sec. – zentechinc Mar 02 '15 at 22:44
  • Are there foreign keys? Cascades? – Laurence Mar 02 '15 at 22:53
  • Primary keys on many tables, but im still working on getting FK's installed. No foreigns on group_mtm or any of these involved tables. – zentechinc Mar 02 '15 at 23:00
  • Is `group_mtm` part of an indexed view itself? – Laurence Mar 02 '15 at 23:11
  • [foobar], which is the indexed view that is getting dragged into this plan, does indeed include an the group attribute from [group_mtm]. – zentechinc Mar 02 '15 at 23:49
  • 1
    It looks like these 2 seconds are needed to update the indexed view. It is easy to check - temporarily disable or drop indexes on that view and see if it makes any difference with your update. – Vladimir Baranov Mar 03 '15 at 01:12
  • You sir are correct. Disabled the index and things took off like a rocket. I guess i need to be more careful with how i design the indexed views. – zentechinc Mar 03 '15 at 16:24

1 Answers1

0

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?

Adam Miller
  • 767
  • 1
  • 9
  • 22