RE: sql - why doesn't the query engine know this???

I'm no expert on SQL performance issues, but for a WAG I'd suggest that if you now take the parens away and try your original statement again w/o them, you might find it acceptably speedy. Thinking that your 30 minute run sufficiently trained the SQL engine. Which is why you had such amazing results with the other variants?

Maybe?

Enjoy,
--Michael Koester

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Gerald Magnuson
Sent: Thursday, June 18, 2015 2:02 PM
To: midrange-l@xxxxxxxxxxxx
Subject: sql - why doesn't the query engine know this???

so I have a bill of material query (parent, child) joining to a item
master file (twice, once on parent, once on child) and a status file(
joined on parent)... I want the list ordered by parent...

select parent,child,t02.desc,t03.desc,osipo# from
plan_bill t01
join items t02 on t02.item = t01.parent
join items t03 on t03.item = chlid
join ordstatus t04 on ositm# = t01.parent
where
parent like '%MTG%' and
child like 'S%' and
osid = 'OS'
order by bprod ;

I have built all advised indexes, and when I run it takes 30 minutes to
return 800 records.
however, when I take off the order by, it runs in 2 seconds.
then, I tried: (parens around select statement)

(select parent,child,t02.desc,t03.desc,osipo# from
plan_bill t01
join items t02 on t02.item = t01.parent
join items t03 on t03.item = chlid
join ordstatus t04 on ositm# = t01.parent
where
parent like '%MTG%' and
child like 'S%' and
osid = 'OS' )
order by bprod ;

and that ran in 2 seconds...
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.