In Lowcoder, queries are returned to the browser as they're executed, regardless of how many rows are displayed in a table page (for instance). In order to improve performance, it is often necessary to paginate the SQL queries behind our applications. The exact syntax for doing this varies across DB platforms, and we'll hit two main syntax forms here.

We'll start with a table displaying the results of a simple query:

Here we have three columns and several rows. If this were a dataset of many thousands or millions of rows, we wouldn't want to have all of that be loaded to the browser. Instead, we need a way to query just enough to fit on the page. Enter select offset/limit/fetch.

SQL Query Pagination

Using SQL offset/fetch(limit) in combination with the Lowcoder table properties pageNo and pageSize, we can generate SQL that will automatically query only the rows we need for the display. In our example here, we'll use SQL that looks like one of these examples.

Below is SQL Server and Oracle syntax, using OFFSET/FETCH:

SELECT*from USPopORDER BYdateOFFSET {{(table1.pageNo-1)*table1.pageSize}} ROWSFETCH next {{table1.pageSize}} ROWS ONLY

MySQL/PostgreSQL would look similar, using LIMTI/OFFSET:

SELECT*FROM USPopORDER BYdateLIMIT {{table1.pageSize}} OFFSET {{(table1.pageNo-1)*table1.pageSize}}

Lowcoder table properties

In addition to the SQL changes, Lowcoder also needs to know how many rows there are, total, so as to know that there will be multiple pages of data. If we don't do this, the SQL query will only ever return the number of rows that will display in first page of the table, and there will be no way to paginate through them, since Lowcoder will recognize (correctly) that it has displayed all the data from the query.

To address this, Lowcoder tables have a "Total Row Count" property that we can populate using a count(*) SQL query:

In our case, our query looks like this:

selectcount(*)as TotalRowsfrom USPop

pageSize and pageNo population

One problem we will run into with default settings on these queries is that the queries may be executed before the table is populated.
This means that our pageSize and pageNo values may be null.
When this happens, we get an error like this:

qryAllRowsPaginated run failed: {"code":"QUERY_EXECUTION_ERROR","message":"Query execution error: The number of rows provided for a OFFSET clause must be an integer.."}

This doesn't actually break the application, but it does present a message that we'd like to avoid.

There are a variety of ways of handling this, but one of the simplest is to use the JS /or/ construct in our SQL handlebars for null handling. Doing this, we can hard code offset and fetch values in our SQL for those times when the query is executed before the table is rendered.
That modified SQL looks like this, replacing null values with 0 and 5 as appropriate:

MSSQL/Oracle:

select*from USPoporder bydateoffset {{(table1.pageNo-1)||0*table1.pageSize||5}} rowsfetch next {{table1.pageSize||5}} rows only

MySQL/PostgreSQL

SELECT*FROM USPopORDER BYdateLIMIT {{table1.pageSize||5}} OFFSET {{(table1.pageNo-1)||0*table1.pageSize||5}}

With these changes in place, we have a responsive app that will display large datasets more efficiently.