Re: SQL performance

[Charles Wilt <charles.wilt@xxxxxxxxx>]

sorry, should have removed row_number
SELECT f.fhpro,f.fhbl
       ORDER BY f.fhpro
offset 110 rows
limit 100 rows

Charles


On Fri, Feb 26, 2021 at 3:30 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

> 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