Cursor Management

A database cursor is the resulting set of records returned by an SQL query.  The cursor allows the application developer to request each record in sequence so that the query results may be displayed in a report or otherwise operated upon.  For example, as an application user moves from page to page in an on-screen report, the subsequent page’s data is retrieved from the cursor and displayed.  It’s also commonplace for applications to iterate over the result set, performing some calculation or other update to each of the records in the result set.

One issue that emerges with cursors is the recency of the data in the result set.  As long as the cursor is in use, it contains the set of records retrieved when the query was originally performed.  However, this data may grow stale over time, especially if the query result is being displayed in a report that an application user is reviewing over a period of time – minutes or hours.  The application user is viewing the result set that was current when the report was first created.

In applications where the underlying data changes frequently, it may be desirable to rerun the SQL query as the application user moves from page to page in their report.  This ensures that the currently displayed page has the freshest data.  Of course, this places an extra load on the database because it must rerun the query more frequently.  However, all modern relational databases have very good data cache management that significantly reduces the cost of frequently rerunning queries.

A second issue to consider is whether the entire result set is pulled from the database into the Data Access Layer.  When a result set contains only a few hundred records, moving all of the data into the application program requires minimal bandwidth and memory to store and manipulate the result set in the application.  However, when the result set has hundreds of thousands or millions of records, it becomes nearly impossible to move all of that data into the application program.  Very few application programs are structured to manipulate that quantity of data in memory without additional file management logic, which defeats the purpose of using the underlying database which provides exactly these facilities.  So, in applications where the result sets can be large, it’s best for applications to retrieve only a manageable subset of the result set from a cursor, operate on that data, and then request the next subset.

In keeping with this philosophy, applications built with Iron Speed Designer use database cursors to retrieve data from the database.  The cursor is used to retrieve the number of records being displayed on the Table Report pages.  Only one page of data is retrieved from the database and displayed at a time, minimizing the data transmission load and the application's memory storage requirements.  Query results are not stored in a temporary database table.  They are retrieved directly from the database and displayed to the user.

The application program does not maintain the state of the cursor.  When the next page (or any other page) is requested, the SQL query is run again to request the needed page's data.  The query parameters include the page number requested and the batch size, which is the number of records per page.  The SQL query is re-run so that any new records added since the first page was displayed are retrieved.  By contrast, if the cursor were simply maintained, any new records, record updates, and record deletions would not be reflected when going from page to page.  Moreover, maintaining the cursor requires the application to keep the database connection open, which is not an efficient use of the available database connections.

See Also

Transaction Management