MS SQL 2005: Where clause and optimizer

[boa sema]

Way back when, and at least in version 7 IIRC, the query optimizer gave
up when the where clause in a statement contained more than 4 search
conditions.

Does anyone know if such a limitation still exist in MS SQL 2005? The
BOL seems to be silent on the issue.

Boa

[Erland Sommarskog]

boa sema (bo*****@gmail.com) writes:

Way back when, and at least in version 7 IIRC, the query optimizer gave
up when the where clause in a statement contained more than 4 search
conditions.

This is a complete misunderstanding. What you might be thinking of is
that in SQL 6.5 and earlier version, the optimizer would only consider
four tables at a time, so if you had a query like:

SELECT ...
FROM a, b, c, d, e
WHERE ....

the optimizer would first look at (a, b, c, d) as a group, and then
(b, c, d, e) as a group. This mean that if the best plan was to start
with e and then take a next, the optimizer would most likely not find
plan.

Does anyone know if such a limitation still exist in MS SQL 2005? The
BOL seems to be silent on the issue.

The behaviour I describe above was abolished in SQL 7. The optimizer does
still not consider all possible plans - because for 12-table query it could
spend all day optimising. Instead it considers it task finished when the
estimated cost is good enough.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

[boa sema]

Erland Sommarskog wrote:

boa sema (bo*****@gmail.com) writes:

>>Way back when, and at least in version 7 IIRC, the query optimizer gave
up when the where clause in a statement contained more than 4 search
conditions.

This is a complete misunderstanding. What you might be thinking of is
that in SQL 6.5 and earlier version, the optimizer would only consider
four tables at a time, so if you had a query like:

SELECT ...
FROM a, b, c, d, e
WHERE ....

the optimizer would first look at (a, b, c, d) as a group, and then
(b, c, d, e) as a group. This mean that if the best plan was to start
with e and then take a next, the optimizer would most likely not find
plan.

That's what I was thinking of. It was a very, very long time ago,
impressing that you still remember the details as well as you do.

Now that you've clarified it so well, this is not an issue anymore. I
remembered incorrectly.

Thanks.
Boa

[snip]