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:
- The time difference is only about 1300ms on average.
- Only 16 rows are returned in the filtered version.
- The column that I am filtering is an int column, that has proper indexes.
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:
- What can cause this?
- What should I be looking for to try and fix this?
Here are the anonymous execution plans (.sqlplan in .zip):