bfa7d031   
   From: esquel@sommarskog.se   
      
   bill (billmaclean1@gmail.com) writes:   
   > 1.   
   > SELECT * FROM  WHERE invoice_no = '01234';   
   > Performs FABULOUSLY, instant response.   
   >   
   > 2.   
   > DECLARE @invoice_no nvarchar(20) = '01234';   
   > SELECT * FROM  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@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)