Javazoid Links

Javazoid

Amnesty International USA

Optimizing SQLView

By Gervase Gallant

I initially wrote the little application I call SQLView to explore JDBC. I wanted to be able to go against any JDBC compliant data source, as long as I could find a driver for it. I tested it with Sybase, DB2, SQL Server 7 and 2000 and mySQL. This utility really served to prove how versatile the JDBC interface is.

Meanwhile, I built SQLView to resemble the query features of Toad, DB2 Command Center and SQL Server’s Enterprise Manager. So, in one evening, I wrote an application that would let me type SQL statements in the top half of the screen and view resultsets in the bottom half. Driver properties were passed in on the command line. Inserts and updates got the exact same attention as Selects (which wasn’t admirable, but seemed to work despite the cryptic return messages…)

I discovered after almost a year of visiting a variety of database products is that the utility was handy and easy to set up. I liked the interface with only one button, but there useful features I figured I could build with a couple of night’s work.

New Features

user can now work with multiple SQL statements. To run one of them, you select the statement and click the Query/Run button at the bottom of the window. If nothing is selected, a query is attempted from whatever is typed in the text box.

Driver properties can now be loaded from a properties file called “SQLview.properties” which resides in the directory you invoke java from.

I added a JSplitPane to let the user size the upper window. This was very handy when I needed a few monster SQL strings.

A variable was added to let the user establish the maximum number of rows to return from a query.

The results window formatting code was modified to keep columns aligned properly. The results window now displays a monospaced font and pads each column item with an appropriate number of spaces.

Small glitch

This last feature was a wonderful thing to behold. Now columns lined up correctly and the display was suddenly very readable. The drawback was that the resultsets were now taking much longer to appear in the results text area. A quick check to re-run the old code showed me that my method to pad spaces to each resultset item was slowing the display down. Even small resultsets were taking up to 6 seconds to display when they were previously consuming about 2.

Jack Shirazi to the rescue….

A while back, I delved into Jack Shirazi’s Java Performance Tuning (O’Reilly 2000). It confirmed one thing about how I developed SQLView. I wrote the thing paying absolutely no attention to performance. I only opened this book again when I realized I had a bottleneck in performance that would render SQLView useless for large resultsets.

My check to revert to the old code is right out of Java Performance Tuning. I was able to intuit the bottleneck and then I began scratching my head for some alternatives to the String-padding method. Jack devotes a full chapter to Strings and I found some great suggestions.

My first pad code was a method that returned a string which I built by passing an int parameter and then looping to add a char array filled with space characters.

/* VERSION 1: add spaces in a loop */

char[] chars = new char[length];

for (int i = 0; i < chars.length; i++) {

chars[i] = ' ';

}

return String.valueOf(chars);

Shirazi states that is it much better to create Strings that do not copy the characters of the string. He mentions substring as an illustration of this. Substring works on the the String internal char array and references a portion of it.

This lead me to think that I might pre-create a String of spaces and return a subset of it. My new code worked like this:

/* VERSION 2: keep a string of spaces handy */

if (length < spaces.length()) {

return spaces.substring(0, length);

} else {

return spaces.substring(0, spaces.length());

}

A string of about 255 characters was created in the class constructor and each time the pad method was called, it would return a small subset.

Methodology

Of course, to find out if the little tweak would work any better, I needed some comparison, so I created a new method in the SqlData class that could call either one of these techniques by passing in an arg parameter. For completeness, I also added the ability to call the original technique (i.e, no padding at all.)

private String addSpaces(int length, int args) {

if (args == 0) {

/* VERSION 1: add spaces in a loop */

char[] chars = new char[length];

for (int i = 0; i < chars.length; i++) {

chars[i] = ' ';

}

return String.valueOf(chars);

} else if (args == 1) {

/* VERSION 2: keep a string of spaces handy */

if (length < spaces.length()) {

return spaces.substring(0, length);

} else {

return spaces.substring(0, spaces.length());

}

} else {

//do nothing

return " ";

}

I thought I might try this as a Junit test (I have been playing extensively with Junit at work lately….) but decided some code in the main() method would work just as well.

To get around the possibility that network traffic or even the database could skew results, I wrote some code in the main method to create a large number of strings in a loop, then in another loop, pass this to the addSpaces() method.

SQLData d = new SQLData();

//create a series of String to test the pad method

String[] s = new String[1000];

for (int i = 0; i < 1000; i++) {

s[i] = String.valueOf(i);

}

System.out.println("METHOD 1 (loop chars) *******************************");

long tickCount = System.currentTimeMillis();

for (int i = 0; i < 10000000; i++) {

d.addSpaces(20,0);

}

System.out.println(

"FETCH:" + (System.currentTimeMillis() - tickCount) + " ticks.");

System.out.println("METHOD 2 (static string) *******************************");

tickCount = System.currentTimeMillis();

for (int i = 0; i < 10000000; i++) {

d.addSpaces(20,1);

}

System.out.println(

"FETCH:" + (System.currentTimeMillis() - tickCount) + " ticks.");

System.out.println("METHOD 3 (do nothing) *******************************");

tickCount = System.currentTimeMillis();

for (int i = 0; i < 10000000; i++) {

d.addSpaces(20,2);

}

System.out.println(

"FETCH:" + (System.currentTimeMillis() - tickCount) + " ticks.");

This is pretty basic stuff, but the differences between the three arguments were startling, particularly in this large loop.

In testing the above from JDK 1.3, I got the following results:

METHOD 1 (loop chars) *******************************

FETCH:12127 ticks.

METHOD 2 (static string) *******************************

FETCH:1923 ticks.

METHOD 3 (do nothing) *******************************

FETCH:261 ticks.

I tested this a couple of time, but got essentially the same results. The original looping technique was 6 times slower than the static string. (Of course, doing nothing at all was even quicker, but that wasn’t really an option.)

In the real world, I’ve now noticed that the display is a little snappier. This because, I generally don’t work with resultsets as large as the test text. To be honest, I’m thinking about another optimization that would improve even these results: I’ve noticed that most SQLView type products generally display only a portion of the resultset in the results window. When you scroll, a new bunch of it gets inserted ( and presumably padded…).

This would allow the user to paste an enormous amount of data in the window without a great deal of delay.

However, to accomplish that, I would need to re-write the display class and turn a simple app into something much more complex. Perhaps on the next rev….

Resources

Jack Shirazi’s web site http://www.javaperformancetuning.com/

Profiling resources http://www.javaperformancetuning.com/resources.shtml