Possible Causes of a Slow Sql View When Filtered

Question

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):

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):

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)

Answer 1

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):

(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?