|
midrange.com
Loyd,
Do you need to know the transaction type? Your initial description of the
problem doesn't state so, but your query seems to imply it. If not try this
query:
Select czekcd,
czfktx,
(select max(dmatdt)
from dbdmrep
where dmekcd = czekcd
and dmazcd = czazcd
and dmcdcd not in ('ADJ',';PHY',';STD')
)
from dbczrep
where czazcd = 'WV'
order by czekcd
-Walden
------------
Walden H Leverich III
President
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com
Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
-----Original Message-----
From: Goodbar, Loyd (ETS - Water Valley) [mailto:LGoodbar@xxxxxxxxxxxxxx]
Sent: Thursday, June 12, 2003 2:06 PM
To: 'midrange-l@xxxxxxxxxxxx'
Subject: SQL vs native I/O round 19 (long)
Let me start by saying I'm not trying to argue for or against SQL (there are
others who do this better than I), but am relating "real world" experience
in using SQL versus native IO for AS/400 database access.
The goal is to find the last time an item was used, excluding adjustment,
cycle count, and standard cost transaction codes. If an item was not used, a
null value is returned. The reason two tables are used is additional
criteria will be added such as only items with on-hand inventory, or
filtering by class code (such as WIP, finished goods, raw material, etc.)
Vital statistics: item master DBCZREP (logical DBCZREL2) contains 5,876
records; transaction history DBDMREP (logical DBDMRELD) contains 3,147,346
records. DBCZREL2 is keyed by location (plant code) and item number;
DBDMRELD is keyed by location (plant code), item number, transaction date
descending (other key fields follow).
The first SQL that worked is:
select
a.czekcd, /* item */
a.czfktx, /* text */
b.dmcdcd /* transaction code */
b.dmatdt /* transaction date */
from
dbczrep a
left outer join dbdmrep b on (a.czazcd=b.dmazcd and
a.czekcd=b.dmekcd and (b.dmcdcd <> 'ADJ' and b.dmcdcd <> ';PHY'
and b.dmcdcd <> ';STD'))
where a.czazcd='WV '
and (b.dmatdt=
(select max(z.dmatdt) from dbdmrep z where b.dmazcd=
z.dmazcd and b.dmekcd=z.dmekcd)
or b.dmatdt is null)
order by 1
I picked the base tables to let the query optimizer determine the best way
to run the query. Runtime (wall time) was 15002 seconds (4 hours, 10
minutes, 2 seconds), using 3880 processing seconds (1 hour, 4 minutes, 40
seconds) per the submitted job log. The query optimizer did pick the best
access path for DBDMREP (which was DBDMRELD, also used in my RPG program).
My next thought was to create a temp table with the most recent transaction
date, then join that to the item master:
with temp as (select
*
from dbdmrep a
where a.dmazcd='WV '
and a.dmcdcd not in ('ADJ',';PHY',';STD')
and a.dmatdt =
(select max(z.dmatdt) from dbdmrep z where a.dmazcd=
z.dmazcd and a.dmekcd=z.dmekcd and z.dmcdcd not in
('ADJ',';PHY',';STD') )
)
select b.czekcd, b.czfktx, c.dmcdcd, c.dmatdt
from dbczrep b
left outer join temp c on (b.czazcd=c.dmazcd and
b.czekcd=c.dmekcd)
order by b.czekcd
This query took 905 wall seconds (15 minutes, 5 seconds), using 483
processing seconds (8 minutes, 3 seconds). This is a tremendous improvement,
but only because I knew to write the query in this manner.
This isn't a truly accurate query, because it gives me some false data. On
dates that have multiple transactions, those multiple transactions are
listed in the output file. I would need to add the transaction time to the
list of join criteria to get a truly accurate query, but it is close enough.
I can do better, but not with SQL.
RPG program:
fdbczrel2 if e k disk
fdbdmreld if e k disk
flastinvt o e printer oflind(*in66)
d LOC s 5a inz('WV ')
d isprinted s n inz(*off)
d tempdate s d inz datfmt(*iso)
c dmkl klist
c kfld LOC
c kfld czekcd
c eval *in66 = *on
c *start setll dbczrel2
c read @czref7
c dow not %eof
c eval oitem = czekcd
c eval otext = czfktx
c eval isprinted = *off
c dmkl setll @dmrey0
c dmkl reade @dmrey0
c dow not %eof and
c not isprinted
c if dmcdcd <> 'ADJ' and
c dmcdcd <> ';PHY' and
c dmcdcd <> ';STD'
c eval isprinted = *on
c *cymd move dmatdt tempdate
c *usa/ move tempdate odate
c eval otrans = dmcdcd
c exsr ofl
c write detail
c endif
c dmkl reade @dmrey0
c enddo
c if not isprinted
c eval otrans = *blanks
c eval odate = '-'
c exsr ofl
c write detail
c endif
c read @czref7
c enddo
c write footer
c eval *inlr = *on
c ofl begsr
c if *in66
c eval *in66 = *off
c write header
c endif
c endsr
Wall time of 155 seconds (2 minutes, 35 seconds) and processing time of 30
seconds.
This program also gives me "close enough" data, since the logical file
DBDMRELD does not have transaction time as one of its keys. This is the same
file the query optimizer picked, so I used it. No existing logical has both
transaction date and transaction time in its key list, so this Is a fair
comparison.
>From SQL 1 to SQL 2, the runtime is 16x faster (processing time 8x faster).
>From SQL 2 to RPG, the runtime is almost 6x faster (processing time 16x
faster). From SQL 1 to RPG, runtime is 96x faster, processing is 129x
faster.
So, queries with somewhat complex subselects are real performance killers,
and are better candidates for custom programming. However, how does this
affect other programs (such as ReportSmith or Crystal Reports) that only
process data by SQL? We use ReportSmith for some reporting here, and it
generates nasty SQL that are performance hogs. The last step I could do is
create a select/omit logical or create a view of DBDMREP, but that looks to
have a major impact on access path maintenance.
I don't really have a question about this, just observations. But I was
wondering why the query optimizer didn't see or do the second SQL with the
temp table. Are there any other SQL things I could do (without creating
views, etc) to improve this query? 15 minutes is good, and this will be an
"occasional" report, but for other live lookup type inquiries, 15 minutes is
too long. Two minutes is too long, but a couple of seconds would be OK to
populate a page-at-a-time subfile (or browser window). I'm also wondering
whether the query optimizer can take "shortcuts" through the data like I did
in the RPG (once I processed satisfied the DBDMREP criteria, I don't need to
read the rest of the records for the same item).
I suppose this is why we have "procedural" SQL (such as Transact-SQL and
SQR).
Loyd
--
Loyd Goodbar
Programmer/analyst
BorgWarner Incorporated
ETS/Water Valley
662-473-5713
lgoodbar@xxxxxxxxxxxxxx
_______________________________________________
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
