I've found that instead of do a =select max... type logic, that
pre-selecting the max records results in much faster performance. I
changed a query from:
select
a.ediname, a.edists, max(a.edits)
from prod_mod/edistspf a
where a.edits =
(select max(z.edits) from prod_mod/edistspf z
where a.ediname=z.ediname)
group by ediname, edists
order by ediname
to
select
a.ediname, a.edists, a.edits
from prod_dta/edistspf a
inner join (select ediname, max(edits) as lastdate
from prod_dta/edistspf group by ediname) lastping
on a.ediname=lastping.ediname and a.edits=lastping.lastdate
order by a.ediname
This greatly reduced the query runtime.
Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Saturday, September 15, 2007 12:39
To: 'Midrange Systems Technical Discussion'
Subject: RE: Need ideas for SQL join select to replace logical view
From: bill.blalock@xxxxxxxx
Hi Joe:
The SQL you suggested didn't quiet do the job when I tested it under
query
manager. It started looping and reprocessing records. I am still
working
with it.
Actually, Bill, it works fine for me. It just takes a LOOOONG time
because
of the nature of the query. Here's what worked:
select I_ID, I_EVENT, I_TMSP, coalesce(O_OWNER, '*NONE')
from invent left outer join owner on I_ID = O_ID
where O_TMSP = (select MAX(O_TMSP) from owner
where O_ID = I_ID and O_TMSP <= I_TMSP)
I named my tables OWNER and INVENT, and I called my control number field
ID
and my status field EVENT.
O_ID O_OWNER O_TMSP
1 JOE 80915120100
1 BILL 80915121000
I_ID I_EVENT I_TMSP
1 EVENT 1 80915120200
1 EVENT 2 80915120300
1 EVENT 3 80915120400
1 EVENT 4 80915121200
1 EVENT 5 80915121300
I get this:
I_ID I_EVENT I_TMSP COALESCE
1 EVENT 1 80,915,120,200 JOE
1 EVENT 2 80,915,120,300 JOE
1 EVENT 3 80,915,120,400 JOE
1 EVENT 4 80,915,121,200 BILL
1 EVENT 5 80,915,121,300 BILL
Exactly what you wanted. I do have an issue that the LEFT OUTER JOIN
isn't
working quite the way I thought it would, and frankly I don't have a
good
answer for it. Without going into a lot of detail, the only way around
this
that I can find is using a CTE with a dummy first record UNIONed to the
OWNER file.
I suppose the easier way would just be an exception SELECT:
Select * from INVENT where I_TMSP <
(SELECT MIN(O_TMSP) FROM OWNER WHERE I_ID = O_ID)
Then UNION the results of that with the original. Man this stuff gets
ugly
quick. I hope an SQL expert can show us a better way!
Building a work file to represent the data needed is very easy with
RPG
for this problem. Using level breaks and matching records is even
less IO
than my proposed solution.
Yup. It's definitely the right tool for the job.
I am trying to wrap my head around SQL. This is a very basic problem
in
my inventory project ... matching the owner of an inventory item at
the
point in time with what happened to that item. In this case the
ownership
of the item is one process (hence the owner file) and what happens to
inventory is another process (hence the status file).
And it's not one that SQL is particularly well suited for: trying to
correlate non-matching tables. It can be done, of course, but if you
don't
have the right indexes in place, it could take a really long time.
Joe
As an Amazon Associate we earn from qualifying purchases.