From: Erland Sommarskog on
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