bfa7d031   
   From: sorrytoomuchspamalready@xs4all.nl   
      
   Bill,   
      
   When the view is very complex, sometimes the query optimizer chooses to   
   not expand the view, causing it to handle the view like a derived table.   
   For example, this could happen when you join two (complex) views in a   
   query.   
      
   In this case, it obviously shouldn't do that.   
      
   You could try to disable parallellism for the query. That might pursuade   
   the optimizer to change it's plan. Add "OPTION (maxdop 1)" to do this.   
      
   --   
   Gert-Jan   
      
      
   bill wrote:   
   >   
   > I much prefer examples and apologize that this question doesn't   
   > have one.   
   >   
   > I unfortunately can't post CREATE TABLE statements   
   > for this problem becuase the issue doesn't show up until   
   > you get lots of rows in the tables, and the tables themselves   
   > are quite convoluted in structure (commercial system, I can't   
   > do anything about that).   
   >   
   > My query joins six tables, the largest of which   
   > (call it Driver_Table) is about 5.4 million rows.   
   > This is the "driving" table, because the other tables   
   > are OUTER joined to the driver, by which   
   > I mean that the rows in Driver_Table are preserved.   
   >   
   > Driver_Table has a two part composite primary key.   
   > The first column in this key is called "invoice_no".   
   > I limit the result set with a WHERE clause   
   > that specifies the invoice_no.   
   >   
   > A typical query returns 30 to 100 rows based on the   
   > WHERE clause, so invoice_no is nice and   
   > selective.   
   >   
   > I made the query into a view, and that's where things   
   > got weird:   
   >   
   > 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.   
   >   
   > 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.   
   >   
   > 4.Put the view SQL into an in-line function that takes   
   > @invoice_no as an argument.   
   > Performs FABULOUSLY, just like number 1.   
   >   
   > I made sure the @invoice_no datatype matches the datatype   
   > of the underlying column.   
   >   
   > The plan for options 1,3, and 4 is the same, starting with a   
   > SEEK against the big driver table.   
   >   
   > The plan for option 2 is completely different.  It   
   > to parallelizes (the other plan doesn't), but it is   
   > doing a SCAN (not a seek) against the large   
   > driver table.   
   >   
   > Does anyone have any ideas to account for this   
   > behavior?   
   >   
   > Why does the optimizer seem to freak out   
   > when using a variable in a WHERE clause?   
   >   
   > Thanks,   
   >   
   > Bill   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)