Query with OR never completes

4 Nov 2005 5:40 PM

rvgrahamsevatenein

This runs 'Instantly':
SELECT     J.JobID, J.JobName, J.CustName
FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
    WHERE J2.Jobid IN (SELECT jobid
          FROM jobs WHERE jobname IN
    (SELECT parent FROM Jobs WHERE
    parent IS NOT NULL AND
         schedtdate BETWEEN '1/1/2005' AND '11/4/2005')

and this runs 'Instantly':
SELECT     J.JobID, J.JobName, J.CustName
FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
    WHERE J2.Jobid IN (SELECT jobid
          FROM jobs WHERE jobname IN
    (SELECT parent FROM Jobs WHERE
    parent IS NOT NULL AND
         schedtdate BETWEEN '1/1/2005' AND '11/4/2005')

So why does this check ok but never complete when I run it:
SELECT     J.JobID, J.JobName, J.CustName
FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
    WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
'11/4/2005')
        OR J2.Jobid IN (SELECT jobid
          FROM jobs WHERE jobname IN
    (SELECT parent FROM Jobs WHERE
    parent IS NOT NULL AND schedtdate
         BETWEEN '1/1/2005' AND '11/4/2005')

Same exact where clauses OR'd

JobId is Identity and Primary

Bob Confused and Stupid

4 Nov 2005 5:53 PM

JT

SQL gets compiled into an execution plan (how the processor navigates
through tables and indexes), before it is run, and even seemingly
insignificant changes in the SQL can result in an entirely different plan.
Using the Show Execution Plan feature of Query Analyzer, see how the plan is
changed when you instroduce the OR condition. If table scans are being
performed, then you may need to implement a new index.

Graphically Displaying the Execution Plan Using SQL Query Analyzer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5pde.asp
Tips on Optimizing SQL Server Indexes
http://www.sql-server-performance.com/optimizing_indexes.asp

<rvgrahamsevaten***@sbcglobal.net> wrote in message
Show quote

news:1131126018.213438.13380@g43g2000cwa.googlegroups.com...
> This runs 'Instantly':
> SELECT     J.JobID, J.JobName, J.CustName
> FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
>          FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
>         schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>
> and this runs 'Instantly':
> SELECT     J.JobID, J.JobName, J.CustName
> FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
>          FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
>         schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>
> So why does this check ok but never complete when I run it:
> SELECT     J.JobID, J.JobName, J.CustName
> FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
> '11/4/2005')
> OR J2.Jobid IN (SELECT jobid
>          FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND schedtdate
>         BETWEEN '1/1/2005' AND '11/4/2005')
>
> Same exact where clauses OR'd
>
> JobId is Identity and Primary
>
> Bob Confused and Stupid
>

4 Nov 2005 5:54 PM

Trey Walpole

Look at the estimated execution plan. That should tell you what you need
to know.

one alternate way to do it, if the OR won't optimize is to UNION the two
queries together.

rvgrahamsevaten***@sbcglobal.net wrote:

Show quote

>This runs 'Instantly':
>SELECT     J.JobID, J.JobName, J.CustName
>FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
>    WHERE J2.Jobid IN (SELECT jobid
>          FROM jobs WHERE jobname IN
>    (SELECT parent FROM Jobs WHERE
>    parent IS NOT NULL AND
>         schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>
>and this runs 'Instantly':
>SELECT     J.JobID, J.JobName, J.CustName
>FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
>    WHERE J2.Jobid IN (SELECT jobid
>          FROM jobs WHERE jobname IN
>    (SELECT parent FROM Jobs WHERE
>    parent IS NOT NULL AND
>         schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>
>So why does this check ok but never complete when I run it:
>SELECT     J.JobID, J.JobName, J.CustName
>FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
>    WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
>'11/4/2005')
>        OR J2.Jobid IN (SELECT jobid
>          FROM jobs WHERE jobname IN
>    (SELECT parent FROM Jobs WHERE
>    parent IS NOT NULL AND schedtdate
>         BETWEEN '1/1/2005' AND '11/4/2005')
>
>Same exact where clauses OR'd
>
>JobId is Identity and Primary
>
>Bob Confused and Stupid
>
>
>

4 Nov 2005 6:12 PM

Raymond D'Anjou

My co-worker here had the same problem yesterday in ORACLE.
She had a query with a few ORs and NOT INs.
After running for about 7 minutes she cancelled the query and called me
over.
I suggested changing the NOT INs to NOT EXISTs but still the same problem.
The next suggestion was separate the query in 3 and UNION them.
It ran in under 2 seconds.
Go figure, Microsoft and Oracle agree on something...

Show quote

"Trey Walpole" <treypoNOle@comSPAMcast.net> wrote in message
news:uOy8QhW4FHA.2888@tk2msftngp13.phx.gbl...
> Look at the estimated execution plan. That should tell you what you need
> to know.
>
> one alternate way to do it, if the OR won't optimize is to UNION the two
> queries together.
>
> rvgrahamsevaten***@sbcglobal.net wrote:
>
>>This runs 'Instantly':
>>SELECT     J.JobID, J.JobName, J.CustName
>>FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
>> WHERE J2.Jobid IN (SELECT jobid
>>          FROM jobs WHERE jobname IN
>> (SELECT parent FROM Jobs WHERE
>> parent IS NOT NULL AND
>>         schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>>
>>and this runs 'Instantly':
>>SELECT     J.JobID, J.JobName, J.CustName
>>FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
>> WHERE J2.Jobid IN (SELECT jobid
>>          FROM jobs WHERE jobname IN
>> (SELECT parent FROM Jobs WHERE
>> parent IS NOT NULL AND
>>         schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>>
>>So why does this check ok but never complete when I run it:
>>SELECT     J.JobID, J.JobName, J.CustName
>>FROM         JOBS J Join Jobs J2 on J.Jobid = J2.JobID
>> WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
>>'11/4/2005')
>> OR J2.Jobid IN (SELECT jobid
>>          FROM jobs WHERE jobname IN
>> (SELECT parent FROM Jobs WHERE
>> parent IS NOT NULL AND schedtdate
>>         BETWEEN '1/1/2005' AND '11/4/2005')
>>
>>Same exact where clauses OR'd
>>
>>JobId is Identity and Primary
>>
>>Bob Confused and Stupid
>>
>>

4 Nov 2005 6:15 PM

rvgrahamsevatenein

Hmmm, Union worked well. Wish I had time to learn more about what was
wrong with the OR version, but must press on.... Thank You!

4 Nov 2005 8:14 PM

Alexander Kuznetsov

I've seen a lot of things like that. Looks like the optimizer likes to
go for a table scan when it encounters an OR.
Yet it seems to be more willing to find a good plan for every branch of
a UNION...

4 Nov 2005 6:09 PM

rvgrahamsevatenein

I changed a couple of things and execution came down from 55 seconds (I
thought it was never completing, but it was) to about 5 seconds.

Changing the "Between" on the dates to ">=...and <+" seemed to result
in a completely different execution plan. Strange since in the
"Between" version Sql was using ">=...and <+" anyway!

Bob Graham