SEO

#1 (**permalink**) 03-01-2008, 12:12 PM

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

#2 (**permalink**) 03-01-2008, 12:12 PM

boa sema (boasema@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, esquel@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

#3 (**permalink**) 03-01-2008, 12:12 PM

Erland Sommarskog wrote:
> boa sema (boasema@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]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

MS SQL 2005: Where clause and optimizer