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

[Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>]

I was attempting to do a sub select, but I clicked run too soon.
the first time or two I thought it was erroring out.

btw...
this morning, I ran the query without parens, and it ran in 1 second,
using the SQE...  I do think that yesterday it was using CQE.

that has been the history of this query...  most days it runs in 1 second,
but every now and then (appears to be if we have had heavy activity on the
BOM file)
the jobs takes 30 to 45 minutes...



On Fri, Jun 19, 2015 at 7:29 AM, <darren@xxxxxxxxx> wrote:

> I didn't know putting parenthesis around a statement, excluding the order
> by, could make any difference. There have been times when I get a few
> results against a larger table, then I remember that I forgot the "order
> by", so I add it and I watch it take much longer to produce the results.
> In my mind, I say, why doesn't it just generate the results it did last
> time, and then order those results, rather than sorting the whole table to
> give me a sorted short list. Is this a trick I could use to improve query
> performance when needing an unindexed sort order with a small result list?
>
>
>
>
>
>
> From:   Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
> To:     midrange-l@xxxxxxxxxxxx
> Date:   06/18/2015 02:08 PM
> Subject:        sql - why doesn't the query engine know this???
> Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
> 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.
>
>
>
> --
> 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.