RE: SQL performance

Thanks for your responses! The customer is still working through his set of
tests. He was unaware of the RRN function and that may help.

I've suggested reviewing the Index Advisor; adding indexes to joined-to
tables; deleting LF's, creating SQL indexes, and rebuilding the LF's;
deleting unused LF's; confirming there's enough free DASD; checking storage
pools and activity levels; RGZPFM'ing any tables with a significant number
of deleted records.

I'm wondering if any OS upgrades, PTF, or performance tweaks in the last 10
years or so have required recreating of PF objects. The customer has built
quite a lot on my original code base but I doubt they've ever changed the
record format. Some of their database objects be the original objects
created in 2000 and untouched since then.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin
Taylor
Sent: Monday, March 1, 2021 10:22 AM
To: MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL performance

What does Visual Explain show for your query?


date: Fri, 26 Feb 2021 14:08:17 -0800

from: Reeve <rfritchman@xxxxxxxxx>
subject: SQL performance

One of my customers has observed a significant performance difference
when running this query (written by an experienced SQL Server guy) on
SQL Server and DB2 for i: 62 ms on SQL server and four seconds on the
iSeries over a table with about 2,000,000 rows. I am satisfied the
disparity is unrelated to the execution environment; I ran this query
on a table with almost
6,000,000 records, on a different production box with plenty of
resources, and it took far more than five minutes.

*My guess is ROW_NUMBER() is the problem*...or is this an example of a
poorly-former query?

SELECT t.rownum,t.fhpro,t.fhbl,t.fhpo
FROM (
SELECT f.fhpro,f.fhbl,f.fhpo,ROW_NUMBER() OVER (
ORDER BY f.fhpro
) AS rownum
FROM fheader AS f
) AS t
WHERE (t.rownum > 110)
AND (t.rownum <= (210))

OTOH, I run this and get subsecond response:

SELECT rrn(fheader),fhpro,fhbl,fhpo

FROM fheader
WHERE rrn(fheader) between 110 and 210

Another question: message CPI432C suggests "The user may want to
delete any access paths no longer needed." This table has quite a few
access paths, some of which are unused. Is there a SWAG on how long
the optimizer takes to evaluate an existing access path?

Finally, they're saying they're using IBM's DotNET Core Entity
Framework library. Is this the best option for web access?

Thank you!

-xavier

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com