Re: SQL performance

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