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

bill (billmaclean1 (AT) gmail (DOT) com) writes:

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.

This is more surprising. Since the view is replaced with its definition
before optimization, it should not make any difference.

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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:

Thanks all for the feedback and ideas. I am going to try the de-
parallelization option when I come up for air. I'll let you know what
happens.

Thanks,

Bill