I got an interesting question a few days ago that I thought I’d share:

My application has thousands of records in one particular table that I need to populate the display with. I was wondering if I can implement paging to speed up the retrieval of those records?

In fact he specifically wanted to know if it was possible to do data paging with the SQLRunner class in my air-sqlite library. The answer is yes it works, without even needing any changes to the library. See below for how to do that.

The easiest way to implement data paging (in other words, getting only a subset of a query’s results at a time) in a SELECT statement is to use the LIMIT..OFFSET clause in the SELECT statement.

In summary, you can put a LIMIT clause at the end of a SELECT statement and the result set will only include the specified number of rows:

SELECT * FROM myTable
LIMIT 3

Then to get the next 3 rows, you add an OFFSET value:

SELECT * FROM myTable
LIMIT 3 OFFSET 3

Fortunately, you can use statement parameters for the LIMIT and OFFSET values (although the docs don’t mention this – shame on me!):

SELECT * FROM myTable
LIMIT :limit OFFSET :offset

Then you just specify values for those parameters and you’re good to go.

I tested this using the SQLRunner class by adding a new set of unit tests and it worked just fine without any changes to the library. (Hooray for unit tests and FlexUnit support in Flash Builder 4 – they made it nice and easy to test this out since I already had the infrastructure in place.)

Here is the SQL statement I used for the unit test using LIMIT..OFFSET with parameters:

SELECT colString,
colInt
FROM main.testTable
LIMIT :limit OFFSET :offset

And here is the line of code that calls the statement (modified slightly for readability). test_result is the result handler function. The parameters object specifies that I want 7 result rows, starting with row number 3 (i.e. skipping 2):

_sqlRunner.execute(SQL, {limit:7, offset:2}, test_result);

As Peter points out in the comments, another approach to do something similar to data paging with AIR is to specify a number of rows to retrieve as the first argument to the SQLStatement.execute() method, then call the SQLStatement.next() method to retrieve additional rows from the same statement. This technique is described in the documentation so I won’t go into more detail about it here except to say that it does have a couple of drawbacks (mentioned in my comment below) that make it less suitable for data paging (but still very useful).