Hadn't thought of using that method (fetch first and offset) to improve 1 page generation in a subfile. Might try it out.
On the clause SQL_CALC_FOUND_ROWS, I haven't seen that available for DB2 for I, as its called on the web. You have to be careful because there is a version of DB2 not for i. Thank you IBM for naming our operating system with (1) letter "I". Anway, if you're only fetching a certain number of records, it would seem unlikely for performance reasons that the database would have figured out how many records it could select before they're selected if you're telling it you only want a certain number of records for performance.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don Brown via MIDRANGE-L
Sent: Tuesday, September 26, 2023 3:13 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: SQL Pagination
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
I have been reading up on options to implement pagination into some of our web enquiries.
Using Limit and Offset allow us to retrieve a specific number of records for a page.
Google also suggested a clause SQL_CALC_FOUND_ROWS but I can't see where this is supported for IBMi
But I also did not understand why there would be a specific clause where I would have thought
select count(*) from file_name where where-clause
would do the exact same thing.
All I could think of is for performance reasons it may have provided some better performance.
Just thought I would ask - you learn a lot in this group.
Thanks
Don
--
This email has been scanned for computer viruses. Although MSD has taken reasonable precautions to ensure no viruses are present in this email, MSD cannot accept responsibility for any loss or damage arising from the use of this email or attachments..
--
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.
midrange.com
