SQL Limit / Offset

[a.pfau]

I'm currently implementing an REST-API on top of our application and currently I'm implementing pagination for some of the endpoints.

Some DBMS have a LIMIT / OFFSET syntax, and with those I could do something like

SELECT * FROM logs WHERE nl = :anl ORDER BY idx LIMIT 100 OFFSET :aoffset

It looks like NexusDB doesn't support LIMIT/OFFSET. How would I do this with NexusDB. My "workaround" would be always selecting the whole data (idx is not necessarily continous, or the table works with other indizes), and handling it in Delphi, but that seems wasteful.
I prefer to let the DB do the heavy lifting regarding the data...

SELECT TOP <count>[, <from>] ....


a.pfau wrote:

>
> I'm currently implementing an REST-API on top of our application and
> currently I'm implementing pagination for some of the endpoints.
>
> Some DBMS have a LIMIT / OFFSET syntax, and with those I could do
> something like
> Code:
> --------------------
> SELECT * FROM logs WHERE nl = :anl ORDER BY idx LIMIT 100 OFFSET :aoffset
> --------------------
>
> It looks like NexusDB doesn't support LIMIT/OFFSET. How would I do this
> with NexusDB. My "workaround" would be always selecting the whole data
> (idx is not necessarily continous, or the table works with other
> indizes), and handling it in Delphi, but that seems wasteful.
> I prefer to let the DB do the heavy lifting regarding the data...

[a.pfau]

Originally Posted by Thorsten Engler [NDA]

SELECT TOP <count>[, <from>] ....

Thanks, but those are always "from the top".
I have tables with millions of rows, a select could even with relevant criteria return thousands of entries. Pushing a thousand via JSON over the wire to the front end and let the (web) app do the pagination is not really feasible. Narrower select criteria won't necessarily help, because some of this data is log data that people want to be able to see in it's entirety for a given "high level" select criteria.

With a limit/offset select I could select the first 100 with the offset 0. Then the next 100 with an offset of 100 and so on. This is how pg does it: https://www.postgresql.org/docs/curr...ies-limit.html
mySQL has similar syntax.

It looks like I have to do it in my Delphi backend code. As a small optimisation I can always use "TOP" to only select to the end of my range (limit+offset), but I have to skip the records till I reach the offset in code.

What part of "<from>" isn't clear?

a.pfau wrote:

>
> 'Thorsten Engler [NDA Wrote:
> > ;114968']SELECT TOP <count>[, <from>] ....
> >
> Thanks, but those are always "from the top".
> I have tables with millions of rows, a select could even with relevant
> criteria return thousands of entries. Pushing a thousand via JSON over
> the wire to the front end and let the (web) app do the pagination is not
> really feasible. Narrower select criteria won't necessarily help,
> because some of this data is log data that people want to be able to see
> in it's entirety for a given "high level" select criteria.
>
> With a limit/offset select I could select the first 100 with the offset
> 0. Then the next 100 with an offset of 100 and so on. This is how pg
> does it: https://www.postgresql.org/docs/curr...ies-limit.html
> mySQL has similar syntax.
>
> It looks like I have to do it in my Delphi backend code. As a small
> optimisation I can always use "TOP" to only select to the end of my
> range (limit+offset), but I have to skip the records till I reach the
> offset in code.

[a.pfau]

Originally Posted by Thorsten Engler [NDA]

What part of "<from>" isn't clear?

Ah, I found it: https://www.nexusdb.com/support/inde...-specification
The "from" confused me. With your hint that I must have misunderstood something and the "start-position" in the documentation it's clear.

Goind to use

SELECT TOP 100 , :aoffset * FROM logs WHERE nl = :anl ORDER BY idx

It's a start position (1 based), not an offset (0 based). Keep that in mind.


a.pfau wrote:

>
> 'Thorsten Engler [NDA Wrote:
> > ;114970']What part of "<from>" isn't clear?
> >
>
> Ah, I found it: https://tinyurl.com/2z5l4pxl
> The "from" confused me. With your hint that I must have misunderstood
> something and the "start-position" in the documentation it's clear.
>
> Goind to use
>
> Code:
> --------------------
> SELECT TOP 100 , :aoffset * FROM logs WHERE nl = :anl ORDER BY idx
> --------------------

[keith Johnson NDX]

Ok, not been on here for a while..

But I also have another option for the poster.

Using TOP certainly works, but one issue I found is that your re-issuing the query again & again, so if that query take X seconds to execute then paginating can be sluggish. I also have infinite scroll tables, so it's even more noticeable here.

If your backend rest-api can keep a persistent (cached copy) of the query, you can enable recNo support on this resultset, and then handle the pagination manually, ideally you will want to implement some form of handle, to clean up dead query's. I have a system where a query is given a unique ID on first request, and subsequent requests only pass this and the Offsets, rather than the whole query again. If for some reason this unique ID query has been cleaned up, the server Rest api, sends a response to client to resend query again, this then means you can even restart the server, and from client's point of view everything keeps working.

Another tip also, if the data your querying has some form of unique ID, you only return this in the query, and then use another query during the Rest API request to link in the records, this keeps memory usage low, even if 1000's of records, and multiple connected users etc, so scales really nice.

IOW: Doing both the above gives you the benefits of stateless Web Technology, and state based cursor query's.

Hope my 2 cents are useful, like I say not been here for a while, so not even sure if NexuDB has features to make the above simpler..