slow performance on specific query

[Not Me]

Hi,

Can't post specifics at the moment but if this seems like a common
problem any help would be appreciated.

When querying with ~6 tables, using mostly left outer joins, I get
standard performance with the where clause

where XXX is not null

however, if I try the clause

where XXX = 4

the query takes upwards of 5-6 minutes (I just stop it running at that
point.

The field XXX is in the 'main' table (to which the joins attach), it's
an integer field too so I can't see too many problems there.

There's no index on the XXX field, but if I remove the joins, I get
standard performance doing either query.

So why would the second query's performance differ so significantly?

Cheers,
Chris

[Erland Sommarskog]

Not Me (no*****@da.com.hk.org) writes:

Can't post specifics at the moment but if this seems like a common
problem any help would be appreciated.
...
So why would the second query's performance differ so significantly?

Well, if you can post the specifics, the answer you get will neither
be very specific.

All I can say is that SQL Server uses a cost-based optimizer that makes
it descisions from statistics collected about the data in the table. From
these statistics it makes estimates about which plan is the best. Statistics
can be out of date, and even if they are not, the statistics are samples
that can be skewed. And, in the course of computing plans for a six-way
join a small error in an initial estimate can give a huge error in the end.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Not Me]

Erland Sommarskog wrote:

Not Me (no*****@da.com.hk.org) writes:

Can't post specifics at the moment but if this seems like a common
problem any help would be appreciated.
...
So why would the second query's performance differ so significantly?

Well, if you can post the specifics, the answer you get will neither
be very specific.

Yup, I understand that.

Well I first tried putting the where clause into a subquery where that
table joined the others, this didn't affect the results. Then I tried
performing that query seperately, creating a temporary table, then using
that in the join for the second query. This gave me what I wanted, a
result in seconds rather than minutes.

Cheers,
Chris

[Erland Sommarskog]

Not Me (no*****@da.com.hk.org) writes:

Well I first tried putting the where clause into a subquery where that
table joined the others, this didn't affect the results. Then I tried
performing that query seperately, creating a temporary table, then using
that in the join for the second query. This gave me what I wanted, a
result in seconds rather than minutes.

Yes, sometimes that can be a useful strategy to impose a join order that
the optimizer does not find itself. When you do this you should be fairly
confident that this is the right order today, but also tomorrow.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp