#1  
Old 29th May 2022, 05:19 AM
a.pfau a.pfau is offline
Junior Member
 
Join Date: Jun 2021
Posts: 28
Default SQL Limit / Offset

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...
  #2  
Old 29th May 2022, 02:09 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: SQL Limit / Offset

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...


  #3  
Old 29th May 2022, 07:36 PM
a.pfau a.pfau is offline
Junior Member
 
Join Date: Jun 2021
Posts: 28
Default

Quote:
Originally Posted by Thorsten Engler [NDA] View Post
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.
  #4  
Old 29th May 2022, 07:43 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: SQL Limit / Offset

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.


  #5  
Old 29th May 2022, 10:29 PM
a.pfau a.pfau is offline
Junior Member
 
Join Date: Jun 2021
Posts: 28
Default

Quote:
Originally Posted by Thorsten Engler [NDA] View Post
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
Code:
SELECT TOP 100 , :aoffset * FROM logs WHERE nl = :anl ORDER BY idx
  #6  
Old 29th May 2022, 11:10 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: SQL Limit / Offset

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
> --------------------


  #7  
Old 1st June 2022, 09:24 PM
keith Johnson NDX keith Johnson NDX is offline
Junior Member
 
Join Date: Sep 2010
Posts: 1
Default RecNo..

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..


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating the best offset for BatchAppend Clément Doss nexusdb.public.support 6 14th March 2016 12:50 PM
XP 10 connections limit Francisco Sanchez nexusdb.public.support 3 8th October 2008 07:44 PM
TCP Connection limit in XP/SP2 Colin Messitt nexusdb.public.support 14 1st September 2006 12:32 PM
how to limit users... Pierre Demers nexusdb.public.discussions 25 12th March 2004 08:55 AM
Way to limit Log Size? David Charron nexusdb.public.support 1 12th November 2003 10:12 PM


All times are GMT +11. The time now is 09:06 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.