Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
Execution plan of the view would help. Could you add the plan to the question? –  Namphibian Jan 9 '13 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 '13 at 16:08
    
Understood. Might I suggest good old paint to obscure the names. –  Namphibian Jan 9 '13 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. –  Aaron Bertrand Jan 9 '13 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... –  Aaron Bertrand Jan 9 '13 at 16:27
show 6 more comments

migrated from stackoverflow.com Jan 11 '13 at 1:16

This question came from our site for professional and enthusiast programmers.

1 Answer

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?

share|improve this answer
    
i was going to suggest updating stats, but then I always do, and thought 'nah!' –  Mitch Wheat Jan 11 '13 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 *. –  reed parkes Jan 17 '13 at 16:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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