Prev: Can Someone please help me with my Computer stuff? 21692
Next: Using @variable in WHERE clause when querying a view is horrible. Inline function is fine. Why?
From: bill on 28 Jan 2010 23:20
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 <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.



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
From: Gert-Jan Strik on 29 Jan 2010 04:36
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 <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.
>
> 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
 | 
Pages: 1
Prev: Can Someone please help me with my Computer stuff? 21692
Next: Using @variable in WHERE clause when querying a view is horrible. Inline function is fine. Why?