Hey,

Main SQL dialects have paging functionality to minimize amount of data sent from DB server to app. This example MSSQL towards AdventureWorks database where the SELECT returns almost 20'000 rows - unless you do paging.

/* Retrieval args:
   1) pageNo: number
   2) rowsPerPage: number
*/
SELECT P.FirstName, P.MiddleName, P.LastName, E.JobTitle 
FROM Person.Person P 
LEFT JOIN HumanResources.Employee E ON (P.BusinessEntityID = E.BusinessEntityID) 
ORDER BY P.LastName, P.FirstName, P.MiddleName, E.JobTitle 
/* PAGING */
OFFSET :rowsPerPage * IIF(:pageNo > 0, (:pageNo - 1), 0) ROWS 
FETCH NEXT IIF(:rowsPerPage > 0, :rowsPerPage, 1) ROWS ONLY

This I just tested in DW painter to be sure. The IIF-calls are required for DW painter's check of SELECT statement in MS SQL.

Oracle PL/SQL: Samee OFFSET; FETCH syntax. However, no IFF. Instead use DECODE or CASE.

That SQL is the best performing SQL as data volume increases because it never moves more data from DB server to "client" code that absolutely necessary.

Entity Framework can't do it much better.

HTH / Michael

Hi Oliver,

For the .NET DataStore, it has some sorting or filtering APIs, but lacks a paging API. You can implement paging by retrieving all rows and taking out some of the rows needed for the page, but this is not efficient. I recommend that you submit an enhancement request.

Alternatively, you can also try SqlModelMapper, which has the ISqlModelMapper.LoadByPage <TModel>(int currentIndex, int pageSize, params object [] parameter) method:

https://docs.appeon.com/appeon_online_help/snapobjects/api_reference/SnapObjects.Data/SqlModelMapper/ISqlModelMapper/Method/LoadByPage.html

Regards, Logan