6c8d435a   
   From: esquel@sommarskog.se   
      
   lsllcm (lsllcm@gmail.com) writes:   
   > I have one question about bind variable cause different execution   
   > plan.   
   >   
   > When I run the query without bind variable, and hard - coded   
   > parameter, it runs very quickly. After I change the query as below. It   
   > runs very slow. Do you have any idea?   
      
   First of all, I'm afraid that I did not make the effort of trying to   
   reconstruct the .sqlplan file from your three posts. You mentioned a   
   link in your post, but I did not see any. Maybe that is only one that   
   is visible on Google News from where you appear to post. If you are not   
   able to make proper attachments, the best is to make the file available   
   for download and post the URL.   
      
   It also helps, if you can post CREATE TABLE and CREATE INDEX statements   
   for your tables.   
      
   So to your question, when you hard-code value, the optimizer has more   
   information than when you use parameters. When you use parameters the   
   optimizer must account for all possible values the parameters may have,   
   as the query plan may be reused with other values. This can affect the   
   outcome.   
      
   Next, I don't really understand why you make this as dynamic SQL, but   
   maybe this is sent from your client in your application.   
      
   Anyway, I notice this condition:   
      
   > '   AND ((RE.SERV_PROV_CODE = @spc1 AND RE.RECEIPT_NBR = @nbr1 ) OR  '   
   > +   
   > '       (RE.SERV_PROV_CODE =  @spc2 AND RE.RECEIPT_CUSTOMIZED_NBR LIKE   
   > @nbr2 )) ' +   
   > ' ORDER BY A.B1_FILE_DD DESC ';   
      
   Conditions with OR is always a challenge for the optimizer. You may   
   help the optimizer by writing the query as:   
      
     SELECT TOP 101 *   
     FROM   (SELECT  ...   
             ...   
               AND RE.SERV_PROV_CODE = @spc1 AND RE.RECEIPT_NBR = @nbr1   
             UNION   
             SELECT ...   
               AND RE.SERV_PROV_CODE =  @spc2 AND   
                   RE.RECEIPT_CUSTOMIZED_NBR LIKE) AS u   
     ORDER BY B1_FILE_DD   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: 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)