Using @variable in WHERE clause when querying a view is horrible. Inline function is fine. Why?
in [Using MS SQL Server]
|
Prev: Using @variable in WHERE clause when querying a view is horrible. Inline function is fine. Why?
Next: Using @variable in WHERE clause when querying a view is horrible. Inline function is fine. Why?
From: Erland Sommarskog on 29 Jan 2010 03:04 bill (billmaclean1(a)gmail.com) writes: > 1. > SELECT * FROM <view> WHERE invoice_no = '01234'; > Performs FABULOUSLY, instant response. > > 2. > DECLARE @invoice_no nvarchar(20) = '01234'; > SELECT * FROM <view> WHERE invoice_no = @invoice_no; > Performs HORRIBLY, 80 seconds to return data. This is not very strange. When you have a constant, the optimizer can make more accurate estimates of how many rows the condition will hit. When you have a variable, the optimizer does not know the value, but applies a standard assumption. > 3. > (Clip the SQL out of the view, put in a new window) > > DECLARE @invoice_no nvarchar(20)= '01234'; > [SELECT . . (logic that comes from view) ] > WHERE invoice_no = @invoice_no > Performs FABULOUSLY, just like number 1. This is more surprising. Since the view is replaced with its definition before optimization, it should not make any difference. > 4.Put the view SQL into an in-line function that takes > @invoice_no as an argument. > Performs FABULOUSLY, just like number 1. Exactly how did the batch look this time? I'm afraid that without full information about queries etc, it's difficult to explain the difference between 2 and 3. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |