First, I created a simple table with a VARCHAR- and a NUMBER column:
CREATE TABLE ordertest ( ordercolumn VARCHAR2; idcolumn NUMBER(10) );
I wrote the following simple Java method (boilerplate to connect to the database has been omitted) to retrieve data from this table and measure the time which is used to execute the query itself and the time which is necessary to print the first 100 elements of the result set:
private void selectData() { long step1 = 0; System.out.println("Executing SELECT ..."); long start = System.currentTimeMillis(); try { Statement stmt = con.createStatement(); //ResultSet res = stmt.executeQuery("SELECT * FROM ordertest"); //ResultSet res = stmt.executeQuery("SELECT * FROM ordertest ORDER BY ordercolumn"); ResultSet res = stmt.executeQuery("SELECT * FROM ordertest WHERE ordercolumn IS NOT NULL ORDER BY ordercolumn"); step1 = System.currentTimeMillis(); for (int i = 0; i < 100 && res.next(); i++) { System.out.println(i + "." + res.getString(1)); } } catch (SQLException e) { e.printStackTrace(); } long stop = System.currentTimeMillis(); System.out.println("Duration for SELECT:" + (step1 - start) + " ms"); System.out.println("Duration for PRINT:" + (stop - step1) + " ms"); }
It turned out that the time to print the data is of almost no interest (since it is similar across all queries), so the remaining article will focus on the time which is required to execute the statement (“Duration for SELECT”). I inserted 1.000.000 records into the table, where idcolumn has been set to an ascending number starting from 1, and ordercolumn has been set to random strings of 5 characters each between ‚A‘ and ‚Z‘. Creating the data through JDBC took approx. 6:14 minutes.