JDBC ResultSet Pagination

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

"Pagination" refers to the technique commonly used by web applications to display a section of a larger query result, and let the user jump backwards and forwards to particular sections. Google, for example, uses this technique to display search results.

This article assumes that your queries routinely return more row data than can fit into a single 1 MB response message sent from the Teradata Database to the Teradata JDBC Driver.

Pagination probably isn't needed if all your queries return less than 1 MB of row data. Your web application could simply retrieve all the data from the Teradata Database, store it as an HttpSession attribute for server-side paging, or send it back with the web page and use JavaScript client-side paging.

Stateless Model

"Stateless" refers to the model in which the JDBC Connection is leased from the connection pool by the servlet only for the duration of processing the servlet request.

With the stateless model, the JDBC Connection is retrieved from the connection pool each time the servlet is invoked, is used only for the duration of composing the page, and is immediately returned to the pool.

The first time the result set display servlet is invoked, the servlet should execute a select count(*) query and save the row count as an HttpSession attribute.

e.g.

select count(*) from DBC.ErrorMsgs

Then the servlet should use a PreparedStatement with the query structured to use the row_number function to select a particular set of rows. The servlet should store the current row number as an HttpSession attribute.

e.g.

PreparedStatement ps = con.prepareStatement("select row_number() over (order by ErrorCode) as RowNum, ErrorCode, ErrorText from DBC.ErrorMsgs qualify RowNum between ? and ?");

ps.setInt(1, 1); // first 10 rows

ps.setInt(2, 10);

ResultSet rs = ps.executeQuery();

The servlet can calculate the total number of pages from the count(*) query, and render the web page with "Page X of Y" verbiage.

As the user clicks Previous or Next links to page though the data, and invokes the servlet again, the servlet can refer to the current row number from a ServletRequest attribute, calculate the delta for another set of rows, prepare the same query, bind the parameter values, execute the query, and render the page.

Characteristics of the stateless model

  • Fewer simultaneous JDBC connections are needed to service a given number of web users, because connections are leased from the connection pool only for the duration of processing the servlet request.
  • Fewer Teradata Database sessions are needed to service a given number of web users, for the same reason.
  • Less application server memory used to service a given number of web users, because result set row data is discarded, and can be garbage collected, as soon as the web page is rendered.
  • Less result set spool used on the Teradata Database, because only small windowed result sets are generated. However, the intermediate spool usage may be significant.
  • More Teradata Database CPU and IO used, because queries are effectively executed over and over again, as web users page through row data. The row_number function can be an expensive all-AMP operation, in an otherwise simple query.
  • Suitable for relatively simple queries.
  • Not suitable for complex queries that take a significant amount of time to execute, because web users would have to wait for every page to be rendered.
  • Page views will always show the most recent information from the underlying table, and therefore may be inconsistent from one page view to another. This may be good or bad, depending on the application's requirements.

This article is focused on paging with a JDBC ResultSet, directly. Some of the stateless model's drawbacks can be avoided with a more sophisticated scheme, such as materializing your query results into a volatile table. Take care when using volatile tables in a pooled connection; they must be cleaned up or the connection may run out of spool space.

Stateful Model

"Stateful" refers to the model in which the JDBC Connection is leased from the connection pool by the servlet for a relatively long time, spanning the processing of multiple servlet requests.

The first time the result set display servlet is invoked, the servlet leases a JDBC Connection from the connection pool and saves it as an HttpSession attribute.

With the stateful model, the servlet avoids the need for a select count(*) query.

The servlet can use a Statement or a PreparedStatement to execute the actual query. The servlet should specify a Scrollable ResultSet (TYPE_SCROLL_INSENSITIVE), to enable jumping back and forward to specific rows in the ResultSet. Scrollable Result Sets have been supported since TTU 8.0 Teradata JDBC Driver + Teradata Database V2R5.1.

e.g.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

stmt.setFetchSize(10); // fetch 10 rows at a time

ResultSet rs = stmt.executeQuery("select ErrorCode, ErrorText from DBC.ErrorMsgs order by ErrorCode");

e.g.

PreparedStatement ps = con.prepareStatement("select ErrorCode, ErrorText from DBC.ErrorMsgs order by ErrorCode", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSet rs = ps.executeQuery();

rs.setFetchSize(10); // fetch 10 rows at a time

The fetch size can be specified for a regular Statement before executing the query. With a PreparedStatement, we must set the fetch size using the ResultSet, after the query was executed. The application-specified fetch size is respected beginning with Teradata JDBC Driver 13.10.00.25 and Teradata Database 13.10. (Fetch size is ignored/unused in older versions.)

The servlet saves the PreparedStatement and the ResultSet as HttpSession attributes; meaning the Connection, the PreparedStatement, the ResultSet, and the total row count all must be saved as HttpSession attributes.

Because the stateful approach uses a Scrollable result set, the servlet can calculate the total number of pages by jumping to the last row of the result set and obtaining the row number.

rs.last();

int nRowCount = rs.getRow();

If the result set is not already positioned near the last row, this operation will require a round-trip to the Teradata Database; however, it is a much less expensive operation than executing a select count(*) query. The servlet saves the row count as an HttpSession attribute, and renders the web page with "Page X of Y" verbiage.

As the user clicks Previous or Next links to page though the data, and invokes the servlet again, the servlet can refer to the current row number from a ServletRequest attribute, calculate the delta for another set of rows, retrieve the ResultSet as an HttpSession attribute, use the ResultSet.absolute method to jump to the desired ResultSet row, fetch the row data, and render the page. The query is not executed again.

The Connection will remain leased from the pool, and the PreparedStatement and ResultSet will remain open, for as long as the user is paging through the row data. The web application must be designed to provide a mechanism to close the ResultSet, PreparedStatement, and Connection when the user is done viewing the row data. (Closing the Connection will return it to the pool.) This is the most challenging and complicated part of the stateful model.

Characteristics of the stateful model

  • More simultaneous JDBC connections are needed to service a given number of web users, because connections are leased from the connection pool for long periods of time.
  • More Teradata Database sessions are needed to service a given number of web users, for the same reason.
  • More application server memory used to service a given number of web users, because result set rows are kept in memory between servlet invocations.
  • More spool used on the Teradata Database, because result sets are kept open for long periods of time.
  • Less Teradata Database CPU and IO used, because queries are only executed once.
  • Suitable for queries of any complexity.
  • The web application must be designed to close JDBC objects when they are no longer needed.
  • A web user's page views will all show the same consistent snapshot of data from the moment the query was executed. Updates to the underlying table will not be shown. This may be good or bad, depending on the application's requirements.
2 Comments
Teradata Employee

Thanks @tomnolan,

Both solutions are fine working for me, except after rs.setFetchSize(10) my ResultSet is holding all the records with it. Our requirement is like

1.    The result set should hold only 10 records at a time. (not possible with stateful model)

2.    We should not trigger sql again and again for every page. (not possible with stateful model)

 


We found that these both options are supported by all (even open source) drivers too (java.sql). Is there any others Teradata specific Pagination solution?

 

 

Teradata Employee

The application-specified fetch size is respected beginning with Teradata JDBC Driver 13.10.00.25 and Teradata Database 13.10. (Fetch size is ignored/unused in older versions.)