Query with OR never completes

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 StupidLook 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.
rvgrahamsevatenein@.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
>
>|||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/d... />
1_5pde.asp
Tips on Optimizing SQL Server Indexes
http://www.sql-server-performance.c...ing_indexes.asp
<rvgrahamsevatenein@.sbcglobal.net> wrote in message
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
>|||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|||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...
"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.
> rvgrahamsevatenein@.sbcglobal.net wrote:
>|||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!|||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...