This page demonstrates some of the variation found in using different approaches to gauge time required to fetch result sets from databases. In some cases, utilities may return very inaccurate results for the purpose of timing retrieval of full results as their functionality isn't oriented toward capturing this type of information.
Versions used in examples:
Oracle Express 11.2
Toad for Oracle: version 12.10
SQL Squirrel open source 3.6
JasperServer 6.3
A query returning five million rows from the SugarCRM sample database were used in all examples.
Oracle Toad:
Using the default settings, Oracle Toad returns information for retrieving only the first row of data when the query is executed normally. The utility goes on to fetch 500 rows. In the screen shot below, a recorded time of 15 milliseconds is reported for fetching the first row.
It is possible to return the full result set by running the query as a script, but the operation doesn't appear optimized for large result sets. The screen capture below shows ~2.5 million rows marshalled after 20 minutes.
SQL Squirrel:
SQL Squirrel has an option in the upper right hand corner of it's UI to remove the row limit when fetching result sets. With this option disabled, the same query requires 67 seconds.
Jasper Server:
Using the sample simple domain which ships with Jasper Server 6.3, a derived table was added representing the same query used in the examples above.
When opening a view containing fields from that table, the following timing of 19/62 seconds was captured in the adhoc cache for timings.
Simple Java Program:
Using a java program to open a connection and obtain and iterate through the results in a minimal fashion, the result set was obtained in 38 seconds. The source code is listed below the screen capture.
.png.ef6023687340eb754ad78062a3c36a0d.png)
SimpleQueryTiming.java source code:
package com.tibco.analytics.jaspersoft.cs.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class SimpleQueryTiming {
//jsw: change these values for different connections and queries.
String driverClassName = "oracle.jdbc.driver.OracleDriver"; // e.g. oracle.jdbc.driver.OracleDriver
String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:xe"; //e.g. jdbc:oracle:thin:@localhost:1521:orcl
String username = "sugarcrm";
String password = "sugarcrm";
String sqlString = "select a.name, a.industry, a.account_type, a.billing_address_state, a.billing_address_city, a.billing_address_street, o.id, s.id " +
"from accounts a, opportunities o, sales_location s " +
"where s.city = a.billing_address_city and ROWNUM <= 5000000";
boolean showOnlyTop10 = true;
public static void main(String [] args){
logInfo("Simple Query Timing - starting execution.");
long startTime = System.currentTimeMillis();
SimpleQueryTiming sqt = new SimpleQueryTiming();
sqt.execTest();
logInfo("Simple Query Timing completed in " + String.valueOf(System.currentTimeMillis() - startTime) + " ms.");
}
public void execTest(){
Connection conn = getConnection();
long startRsTime = System.currentTimeMillis();
try{
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sqlString);
logInfo("Resultset handle obtained in " + String.valueOf(System.currentTimeMillis() - startRsTime) + " ms.");
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
int rowCount = 0;
long startOutputTime = System.currentTimeMillis();
while (rs.next()){
rowCount ++;
if ((showOnlyTop10 == true)&&(rowCount>10)){
if (rowCount==11) logInfo("...");
for (int t=1; t<=colCount; t++){
Object obj = rs.getObject(t); //not doing anything with the value other than fetching.
}
} else {
StringBuffer sb = new StringBuffer();
sb.append(rowCount);
sb.append(") ");
for (int t=1; t<=colCount; t++){
if (t>1) sb.append(",");
sb.append(rs.getObject(t));
}
logInfo(sb.toString());
}
}
logInfo("ResultSet contained " + rowCount + " rows.");
logInfo("ResultSet traversed in " + String.valueOf(System.currentTimeMillis() - startOutputTime) + " ms.");
rs.close();
st.close();
conn.close();
} catch (SQLException sqle){
logInfo(sqle.getMessage());
}
}
protected Connection getConnection(){
long startConnectionTime = System.currentTimeMillis();
Connection connection = null;
try{
Class.forName(driverClassName);
connection = DriverManager.getConnection(
jdbcUrl, username, password);
} catch (ClassNotFoundException cnfe){
logInfo(cnfe.getMessage());
} catch (SQLException sqle){
logInfo(sqle.getMessage());
}
logInfo("Connection established in: " + String.valueOf(System.currentTimeMillis() - startConnectionTime) + " ms.");
return connection;
}
protected static void logInfo(String info){
System.out.println(info);
}
}
Recommended Comments
There are no comments to display.