Most effective ways to return data from the JSON DB API

How best to return query results one page at a time or all at once

Abstract

The JSON DB API provides "getRecords..." actions to query your data. Each action returns data directly or through a cursor. When query results are in the tens of thousands of records, it is most efficient to return all records at once. For larger results, it is most efficient to return a cursor and use the "getRecordsFromCursor" action to paginate through the results.

Most effective way to paginate data

When you plan on retrieving a large amount of data one page at a time, a cursor is the most efficient approach.

You can call a "getRecords" action once to return a "cursorId" that is a reference to the query results. You can then use the "getRecordsFromCursor" action with the "cursorId" property to page through the data starting from the beginning or end of the results and skip through the data forward and backward.

A cursor is efficient because the server generates the query results just once and then tracks the cursor's current record position within the results. Each time you fetch records from the cursor, the server moves the current position. The next time you fetch records from the cursor, the server starts from the new position. You can repeatedly walk the result set forward or backward without regenerating it, which is especially efficient for SQL because that means you only have to run the SQL query once.

Tip

To use a cursor in a stateless app, save the "cursorId" in a cookie, URL, or payload.

It is safe to transmit "cursorId" and "authToken" between clients and app servers because they are randomly generated 64-character strings, contain no identifying information, have a limited lifespan, and cannot be predicted by malicious attackers. An example of this would be "he705YCHoAsIBJsMFxYS8esR8IM8DU2mr7GxUXvtjdLqo3a0h3P4KHGi5jN5HgqH".

Cursor code examples

Example 1. Create a cursor from a "getRecords..." action

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "athlete",
    "returnCursor": true
  },
  "authToken": "anAuthorizationTokenFromTheServer"
}

Most "getRecords..." actions return a cursor by setting the "returnCursor" property to true. The server ignores the "skipRecords", "maxRecords", and "reverseOrder" properties when returning a cursor.

Example 2. Response from the "getRecords..." action

{
  "authToken": "anAuthorizationTokenFromTheServer",
  "result": {
    "cursorId": "aCursorIdFromTheServer",
    "totalRecordCount": 6
  },
  "errorCode": 0,
  "errorMessage": ""
}

The response contains a "cursorId" property containing a unique cursor identifier. Use this cursor ID each time you call "getRecordsFromCursor". It also contains a "totalRecordCount" property that contains the number of records in the cursor or -1 when the server does not know the record count.

Example 3. Return the next batch of 50 records from a cursor

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "aCursorIdFromTheServer",
    "fetchRecords": 50
  },
  "authToken": "anAuthorizationTokenFromTheServer"
}

Each time you send this request, the server returns the next set of records. The server makes pagination easy because it tracks the cursor position for you.

Ways you can use a cursor

You can start at the beginning and work your way forward returning as many records as you want in each request.
You can also start at the end and work your way backward in reverse order.
You can skip forward and backward from the current position.
You can also start at the beginning and skip forward to any record in the results.
Likewise, you can start at the end and skip backward to any record.
In a single request, you can return a specified number of records before and after a position.

Closing a cursor

When a cursor is closed, the server frees up the resources used by the cursor. These resources include the memory used to store the cursor position, idle timeout, and status. A SQL cursor also uses a temporary table.

When you are done using a cursor, you can call "closeCursor" to release its resources, but it is not required since the database automatically releases cursors when they become idle.

Cursors automatically expire when there is no activity for the number of seconds defined by the session's "idleCursorTimeoutSeconds" property. Each call to "getRecordsFromCursor" extends a cursor's life by the number of seconds in "idleCursorTimeoutSeconds" .

When you create or alter a session, you can set the "idleCursorTimeoutSeconds" property to the maximum number of seconds a cursor can be idle before the server closes it.

If you set "idleCursorTimeoutSeconds" to 0 to disable the timeout, you must close the cursor when you no longer need it; otherwise, the server will keep it open indefinitely.

Calling "getRecordsFromCursor" with an expired cursor, returns an error response. Calling "closeCursor" with an expired cursor, does not return an error response because the cursor is already closed.

Most effective way to return all records

When a query returns tens of thousands of records, it is most efficient to return all records at once.

By default, most "getRecords" actions return data directly, but limit the results to 20 records. This small default protects new users from accidentally returning too many records.

You can change the default by adding the "maxRecords" property to your request and set it to the maximum number of records you want the query to return.

To return all possible records, set "maxRecords" to -1.

To efficiently reverse the sort order of the results, you can optionally set "reverseOrder": true.

To skip over a few records, set "skipRecords" to the number of records you want to skip. Do not paginate over the results using "skipRecords" and "maxRecords" because it is inefficient.

Example 4. Return all records

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "my_table",
    "maxRecords": -1,
    "returnCursor": false,
    "reverseOrder": false
  },
  "authToken": "anAuthorizationTokenFromTheServer"
}

Ineffective ways to paginate

Returning data directly, instead of using a cursor, is an ineffective way to paginate query results, because each call to a "getRecords..." action starts from the beginning of the results and walks each record to the starting record before it fetches records up to the "maxRecords" count. As you paginate deeper into the results, it takes longer to return records because the server must start from the first record and walk each record until it gets to the starting record.

For example, if you want to paginate 1000 records at a time, set "maxRecords" to 1000 in each call to a "getRecords..." action. For the first page of records, set "skipRecords" to 0 and the server starts at the first record and returns 1000 records. For the second page of records, set "skipRecords" to 1000 and the server starts at the first record, walks 1000 records, and returns the next 1000 records. For the tenth page of records, set "skipRecords" to 10000 and the server starts at the first record, walks 10,000 records, and returns the next 1000 records. Paginating this way is slow and expensive.

The problem is worse when you run a SQL query, because each time you call the "getRecordsUsingSql" action, the server parses the SQL statement, creates an execution plan, runs the plan to find matching records, skips records, returns the records, and then discards the query results. The next time you call the "getRecordsUsingSql" action, the server repeats the same process from the beginning.