CODEWITHSUNDEEP
sqlsql-serverviewwhere-clause
L. Toto
L. Toto

Reputation: 33

SQL Server Query performance view used in where clause

I have a problem with a query in sql server.

I used a view in where clause like

SELECT ... 
FROM T1
WHERE 
(@param = 1 AND EXISTS (SELECT 1 FROM VIEW...)) // in this case => use view
OR
(@param = 2 AND T1.Id = ...) // in this case => no view

I saw in execution plan that view is always build. But I don't want that.

Any idea?

Upvotes: 2

Views: 229

Answers (2)

andrews
andrews

Reputation: 2173

If you don't want your view to build in case where you don't need it then use dynamic SQL and conditionally enable and disable the view part when it's needed or not. Something like that:

declare @useView nvarchar(max) = '--'

if (@param = 1)
set @useView = ''

declare @sql nvarchar(max) = '
SELECT ... 
 FROM T1
 WHERE 1=1 '
 + @useView ' AND EXISTS (SELECT 1 FROM VIEW...)) -- in this case => use view '
+'
 AND
 (@p_param = 2 AND T1.Id = ...) -- in this case => no view '

print @sql
exec sp_executesql @sql,N'@p_param INT',@p_param = @param

You need to make sure the whole SELECT referencing the VIEW fits 1 line to be covered by -- comment, otherwise use 2 comment variables for /* and */.

Upvotes: 1

Serge
Serge

Reputation: 4036

Move the parameter value check into the sub-query and the query optimizer should then be able to build a better plan:

SELECT ... 
FROM T1
WHERE 
(EXISTS (SELECT 1 FROM VIEW ... WHERE ... @param = 1)) // in this case => use view
OR
(@param = 2 AND T1.Id = ...) // in this case => no view

Upvotes: 0

Related Questions