64

I have a view that returns 2 ints from a table using a CTE. If I query the view like this it runs in less than a second

SELECT * FROM view1 WHERE ID = 1

However if I query the view like this it takes 4 seconds.

DECLARE @id INT = 1 SELECT * FROM View1 WHERE ID = @id

I've checked the 2 query plans and the first query is performing a Clustered index seek on the main table returning 1 record then applying the rest of the view query to that result set, where as the second query is performing an index scan which is returning about 3000 records records rather than just the one I'm interested in and then later filtering the result set.

Is there anything obvious that I'm missing to try to get the second query to use the Index Seek rather than an index scan. I'm using SQL 2008 but anything I do needs to also run on SQL 2005. At first I thought it was some sort of parameter sniffing problem but I get the same results even if I clear the cache.

Improve this question
0

9 Answers 9

Reset to default
77

Probably it is because in the parameter case, the optimizer cannot know that the value is not null, so it needs to create a plan that returns correct results even when it is. If you have SQL Server 2008 SP1 you can try adding OPTION(RECOMPILE) to the query.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

+1 Works like a champ here (and much less manual than trying to force various joins/plans, etc.)
@pst - This is the right suggested solution but wrong reasoning. The OP has a variable not a parameter. And the issue is selectivity estimates as SQL Server doesn't do variable sniffing. Not "creating a plan that deals with NULL"
@MartinSmith All the little details that escape me :( All I know is that SQL Server was happily turning a 2 second (in SMSS query) into a I'm-gonna-stop-it-after-10-minute query an SqlCommand (with placeholders) as part of a Typed DataSet. I updated all the statistics (without any options) on the related tables before trying the OPTION(RECOMPILE) route, but that didn't seem to fix the issue. What is meant by the "creating a plan that deals with NULL"?
@MartinSmith Ahh. I just skipped to the ATTENTION(GRABBING) suggestion -- in my case the +1 was for a "my problem is fixed" :-)
The suggestion here regarding the optimiser allowing for the possibility of nulls helped me; I wrapped my variable with a COALESCE statement and the SP execution sped right up. I should mention I was generating dynamic SQL and was only adding that element of the where clause if the variable was not null.
|
9

You could add an OPTIMIZE FOR hint to your query, e.g.

DECLARE @id INT = 1 SELECT * FROM View1 WHERE ID = @id OPTION (OPTIMIZE FOR (@ID = 1))
Improve this answer

1 Comment

Sometimes it doesn't help...but I can't figure out why. Seems like it should, but I just tried one where had four WHERE clauses with literals that was cost = 0.2 and one with four WHERE clauses with parameters cost = 17. Going instead with OPTION(RECOMPILE) fixes it back to 0.2 cost. Any idea why?
6

In my case in DB table column type was defined as VarChar and in parameterized query parameter type was defined as NVarChar, this introduced CONVERT_IMPLICIT in the actual execution plan to match data type before comparing and that was culprit for sow performance, 2 sec vs 11 sec. Just correcting parameter type made parameterized query as fast as non parameterized version.

One possible way to do that is to CAST the parameters, as such:

SELECT ... FROM ... WHERE name = CAST(:name AS varchar)

Hope this may help someone with similar issue.

Improve this answer

Comments

2

I ran into this problem myself with a view that ran < 10ms with a direct assignment (WHERE UtilAcctId=12345), but took over 100 times as long with a variable assignment (WHERE UtilAcctId = @UtilAcctId).
The execution-plan for the latter was no different than if I had run the view on the entire table.

My solution didn't require tons of indexes, optimizer-hints, or a long-statistics-update.

Instead I converted the view into a User-Table-Function where the parameter was the value needed on the WHERE clause. In fact this WHERE clause was nested 3 queries deep and it still worked and it was back to the < 10ms speed.

Eventually I changed the parameter to be a TYPE that is a table of UtilAcctIds (int). Then I can limit the WHERE clause to a list from the table. WHERE UtilAcctId = [parameter-List].UtilAcctId. This works even better. I think the user-table-functions are pre-compiled.

Improve this answer

1 Comment

Hah, I wrote this question 8 years ago and I found it again.. for the same issue a few SQL Server versions later. SQL Server is not correctly promoting the filter into the nested table queries in my view when a parameter is used - a SPROC (or TVF) with this promotion done manually results in expected fast performance.
1

When SQL starts to optimize the query plan for the query with the variable it will match the available index against the column. In this case there was an index so SQL figured it would just scan the index looking for the value. When SQL made the plan for the query with the column and a literal value it could look at the statistics and the value to decide if it should scan the index or if a seek would be correct.

Using the optimize hint and a value tells SQL that “this is the value which will be used most of the time so optimize for this value” and a plan is stored as if this literal value was used. Using the optimize hint and the sub-hint of UNKNOWN tells SQL you do not know what the value will be, so SQL looks at the statistics for the column and decides what, seek or scan, will be best and makes the plan accordingly.

Improve this answer

Comments

1

I know this is long since answered, but I came across this same issue and have a fairly simple solution that doesn't require hints, statistics-updates, additional indexes, forcing plans etc.

Based on the comment above that "the optimizer cannot know that the value is not null", I decided to move the values from a variable into a table:

Original Code:

declare @StartTime datetime2(0) = '10/23/2020 00:00:00' declare @EndTime datetime2(0) = '10/23/2020 01:00:00' SELECT * FROM ... WHERE C.CreateDtTm >= @StartTime AND C.CreateDtTm < @EndTime

New Code:

declare @StartTime datetime2(0) = '10/23/2020 00:00:00' declare @EndTime datetime2(0) = '10/23/2020 01:00:00' CREATE TABLE #Times (StartTime datetime2(0) NOT NULL, EndTime datetime2(0) NOT NULL) INSERT INTO #Times(StartTime, EndTime) VALUES(@StartTime, @EndTime) SELECT * FROM ... WHERE C.CreateDtTm >= (SELECT MAX(StartTime) FROM #Times) AND C.CreateDtTm < (SELECT MAX(EndTime) FROM #Times)

This performed instantly as opposed to several minutes for the original code (obviously your results may vary) .

I assume if I changed my data type in my main table to be NOT NULL, it would work as well, but I was not able to test this at this time due to system constraints.

Improve this answer

Comments

0

I had a similar problem but with a join. This worked for me.

Without using a case, it counted with the option that @id can be null. This way, I forced it to never be null.

declare @id bigint table.nameid = (SELECT CASE WHEN @id IS not NULL THEN @id ELSE 0 END)
Improve this answer

Comments

-2

Came across this same issue myself and it turned out to be a missing index involving a (left) join on the result of a subquery.

select * from foo A left outer join ( select x, count(*) from bar group by x ) B on A.x = B.x

Added an index named bar_x for bar.x

Improve this answer

Comments

-2 
DECLARE @id INT = 1 SELECT * FROM View1 WHERE ID = @id

Do this

DECLARE @sql varchar(max) SET @sql = 'SELECT * FROM View1 WHERE ID =' + CAST(@id as varchar) EXEC (@sql)

Solves your problem

Improve this answer

3 Comments

Please elaborate what your snippet does and why it solves the problem.
Please add some explanation
For an explanation why it works: Because there the id value will be injected into the statement string. Thus SQL 'knows' the variable is not null. If you print the select statement text you'll see something like SELECT * FROM View1 WHERE ID =123 However ,its not a preferable scenario. To begin with, if the query is complex it soon creates a maintenance problem. A different solution, e.g. option (recompile) would be far preferable in majority of situations.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.