1

I have a view in Sql Server 2008 that when performing just a:

SELECT * FROM [Table]

Will return roughly 40000 rows by 130 columns. Here are 6 runs with client statistics on (roughly 7 seconds average):

Client Statistics (Unfiltered)

Let's just assume those times are fine, the problem comes when a filter is applied. Here are 6 runs with client statistics on when I filter (roughly 6 seconds average):

Client Statistics (Filtered)

There are a few things that are annoying / frustrating about this:

  1. The time difference is only about 1300ms on average.
  2. Only 16 rows are returned in the filtered version.
  3. The column that I am filtering is an int column, that has proper indexes.
  4. The filtered SQL literally looks like:

    SELECT * FROM [Table] Where [IntColumn] = 15 -- returns 16 rows
    

I am coming to you guys because I am lost as to why the filtered version could possibly take so long. It should be extremely simple for the SQL engine to find those 16 rows right from the start of the execution and then calculate / look-up the values for the 130 columns. My questions really are:

  1. What can cause this?
  2. What should I be looking for to try and fix this?

Here are the anonymous execution plans (.sqlplan in .zip):

  1. Anonymous Sql Plans (Zippyshare)
  2. Anonymous Sql Plans (Dropbox)
11
  • Execution plan of the view would help. Could you add the plan to the question?
    – Namphibian
    Jan 9, 2013 at 16:07
  • I will work on getting an execution plan with anonymous table names as I am sure my company won't like me just posting it as-is.
    – reed parkes
    Jan 9, 2013 at 16:08
  • Understood. Might I suggest good old paint to obscure the names.
    – Namphibian
    Jan 9, 2013 at 16:09
  • 1
    Paint won't help if you post what's really useful (the actual .sqlplan file rather than a barely useful screen shot). But search and replace should be easy. Jan 9, 2013 at 16:12
  • 2
    The filtering is likely not to be the most expensive part of the query. Returning one row probably doesn't come back any faster either. The main cost of the query is going to be resolving the joins, returning 130 columns... Jan 9, 2013 at 16:27

1 Answer 1

5

It seems like a small portion of the plan is different depending on whether the filter is present. I'm trying to piece together what is happening, but that is some serious obfuscation trying to match up everything. Without knowing the definitions of things like Table1.Index9 and Table2.Index38 it's going to be very difficult to get specific help with the most expensive operators you have here.

In the meantime I have a couple of generic performance suggestions, other than reducing the complexity of the view and maybe considering having simpler views that don't output 130 columns (try not to go cross-eyed looking at this join diagram):

enter image description here

(1) make sure your statistics are up to date on all the underlying tables. Almost every single operation is showing a massive gap between estimated and actual rows, which could help to explain suboptimal optimizer choices.

(2) get rid of two lookups by adding the following columns to the following indexes (probably as INCLUDE columns):

Table1.Column8  => Index3
Table1.Column43 => Index1

(3) some of these expressions are maddening, e.g.

CASE WHEN (
 Database0.Schema0.Table3.Column42 IS NOT NULL OR
 Database0.Schema0.Table3.Column42 IS NULL
)
AND Column56 IS NOT NULL
AND Database0.Schema0.Table3.Column55 IS NULL
AND Database0.Schema0.Table3.Column43 IS NULL
THEN (1) ELSE (0) END

Seriously? Look at that clause closely (and the view seems to be littered with dozens of these). No human could have done this on purpose. So what brain dead code generator produced this mess?

2
  • i was going to suggest updating stats, but then I always do, and thought 'nah!' Jan 11, 2013 at 2:24
  • Thank you for the response. We've gone through and updated / created some smarter indexes and that has had minor improvements. I think the problem might be (as mentioned) the resolution of joins. It explains why filtering takes similar amounts of time to select *.
    – test
    Jan 17, 2013 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.