Re: SQL performance

row_number() usually has to be applied to the entire set of source rows...

I'd simply do it
SELECT f.fhpro,f.fhbl,f.fhpo,ROW_NUMBER() OVER (
ORDER BY f.fhpro
offset 110 rows
limit 100 rows

Charles



On Fri, Feb 26, 2021 at 3:08 PM Reeve <rfritchman@xxxxxxxxx> wrote:

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