Left Join Will Not Work without {NO Order} Clause

[ERPgal]

Problem: I am joining 5 tables together. Two of the joins are left joins. One of the left joined tables only has 2400 records and works fine. However the other left joined table contains 101,000 records and it is NOT joining.

I am only able to get it to work by disabling the optimizer by using the {NO REORDER} clause. The problem is this makes a the query go from 43 seconds to 438 seconds (over 7 minutes)

Select v.name
, pr.duedate
, rd.receiptdate

FROM ((((pub.podetail as pd inner join pub.porel as pr
on ( pd.company = pr.company
and pd.ponum = pr.ponum
and pd.poline = pr.poline
and pr.porelnum > 0
and pr.voidrelease = 0
and pd.ordernum = pr.ordernum
and pd.orderline = pr.orderline
)
) --1
inner join pub.poheader as ph
on ( pd.company = ph.company
and pd.ponum = ph.ponum
and pd.ordernum = ph.ordernum
and pd.vendornum = ph.vendornum
and ph.voidorder = 0
and pd.voidline = 0
)
) --2
left join pub.vendor as v
on (pd.vendornum = v.VendorNum
and pd.company = v.company
)
) --4
left join pub.rcvdtl as rd
on ( ph.company = rd.company
and pd.vendorNum = rd.VendorNum
and rd.purpoint <> 'xxx'
and rd.packslip <> 'xxx'
and rd.packline > 0
and pd.partnum = rd.PartNum
and pr.poline = rd.poline
and pr.porelnum = rd.porelnum
and pr.ponum = rd.PONum
)
) --3
{NO REORDER}
WHERE ph.company = 'ACME'
and ph.voidorder = 0
and pd.voidline = 0
AND (pr.duedate between to_date('1/1/2006') AND to_date('2/2/2009'))
ORDER BY pr.duedate desc, v.vendorid desc

_______________ BASIC Table Stats ___________
select count(*) from pub.rcvDtl rd -- 101,416 records
select count(*) from pub.vendor -- 3,346 records
select count(*) from pub.porel -- 99,901 records
select count(*) from pub.podetail -- 86,441 records
select count(*) from pub.poheader -- 45,119 records

 

[Casper]

What is your progress version?

Do you do update statistics periodically?

And what query takes 43 and what query takes 437 seconds. (if you can't get a query to work then you don't know how much time it will take without no-reorder. :awink.

No reorder is almost never good. The sql engine is pretty smart (ok, you have to run updtae statistics).

What indexes does the table with the 101000 records have?

It can be very tricky sometimes to 'tell' the sql engine what to do. Sometimes it is just as easy as ginving an index hint, sometimes it requires rewriting the whole query.

Casper.

 

[tamhas]

Fixing things with NO REORDER strongly suggests an old version of Progress. It seemed to be a necessary fix on certain complex queries ... although there were times one could come at the problem from a different direction instead ... but it has been many years and versions since that was true.

 

[ERPgal]

I am on the latest patch of 9.1D (old version ).

I tried to run the following two statements against the table in question
and it came back with "0 Rows Affected"

Here are the two statements I executed:

UPDATE STATISTICS for pub.rcvdtl
UPDATE INDEX STATISTICS for pub.rcvdtl
​

 

[ERPgal]

What is your progress version?

Do you do update statistics periodically?

And what query takes 43 and what query takes 437 seconds. (if you can't get a query to work then you don't know how much time it will take without no-reorder. :awink.

No reorder is almost never good. The sql engine is pretty smart (ok, you have to run updtae statistics).

What indexes does the table with the 101000 records have?

It can be very tricky sometimes to 'tell' the sql engine what to do. Sometimes it is just as easy as ginving an index hint, sometimes it requires rewriting the whole query.

Casper.

I tried running statistics and that does not affect any records.

When I say the query works at 43 seconds, I mean that all the other 4 tables' data are pulled, but the 5th table is completely ignored. Any columns representing the 5th table show as null.

However, with {NO REORDER}, the 5th table is not ignored, and data is present in the columns representing the 5th table.

.i.e At 43 seconds receiptDate is NULL
At 438 seconds receiptDate is NOT NULLL


I am also going against atleast 2 indices in the 5th table...