MS SQL 2005: Where clause and optimizer

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.

Boaboa sema (boasema@.gmail.com) writes:

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

boa sema (boasema@.gmail.com) writes:
>

Quote:

Originally Posted by

>>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]