skip to main content
Designing JDBC applications for performance optimization : Returning data : Returning long data
  

Try DataDirect Drivers Now

Returning long data

Because retrieving long data across a network is slow and resource intensive, applications should not request long data unless it is necessary.
Most users do not want to see long data. If the user does want to see these result items, then the application can query the database again, specifying only the long columns in the Select list. This method allows the average user to return the result set without having to pay a high performance penalty for network traffic.
Although the best method is to exclude long data from the Select list, some applications do not formulate the Select list before sending the query to the JDBC driver (that is, some applications SELECT * FROM table_name ...). If the Select list contains long data, most drivers are forced to return that long data at fetch time, even if the application does not ask for the long data in the result set. When possible, the designer should attempt to implement a method that does not return all columns of the table.
For example, consider the following code:
ResultSet rs = stmt.executeQuery(
"SELECT * FROM Employees WHERE SSID = '999-99-2222'");
rs.next();
string name = rs.getString(1);
Remember that a JDBC driver cannot interpret an application's final intention. When a query is executed, the driver has no way to know which result columns an application will use. A driver anticipates that an application can request any of the result columns that are returned. When the JDBC driver processes the rs.next request, it will probably return at least one, if not more, result rows from the database server across the network. In this case, a result row contains all the column values for each row, including an employee photograph if the Employees table contains such a column. If you limit the Select list to contain only the employee name column, it results in decreased network traffic and a faster performing query at runtime. For example:
ResultSet rs = stmt.executeQuery(
"SELECT name FROM Employees WHERE SSID = '999-99-2222'");
rs.next();
string name = rs.getString(1);
Additionally, although the getClob() and getBlob() methods allow the application to control how long data is returned in the application, the designer must realize that in many cases, the JDBC driver emulates these methods due to the lack of true Large Object (LOB) locator support in the DBMS. In such cases, the driver must return all the long data across the network before exposing the getClob() and getBlob() methods.