Test Results

Throughout this book, you'll see sets of SQL statements that show a test we ran against the Big Eight. The second statement in each example includes a note at the bottom that states: GAIN: x/8. That's an important number. The gain shows how many of the Big Eight run faster when an SQL statement is optimized by implementing the syntax shown in the second example. We recorded a gain for a DBMS if performance improved by 5% or greater. Mileage varies with different data and different machines, of course. We're only reporting what our tests showed.

"GAIN: 0/8" means "you'd be wasting your time if you rearranged this particular SQL statement into optimum order because the DBMS does this for you." "GAIN: 4/8," on the other hand, means that half of the Big Eight performed better when the suggested syntax was used, while the other half executed both statements equally well. Even if the gain is only 1/8 (meaning only one of the Big Eight improved on the second statement), you'll be better off using our suggested syntax because this means you'll improve performance some of the time without ever harming performance the rest of the time. That is, none of our suggestions will cause a performance decline on any of the Big Eightwith one exception.

The exception is that, in a few cases, one DBMS showed aberrant behavior and declined in performance on the second statement, while all the rest showed impressive gains. In such cases, we felt the possible improvement was worthy of mention anyway. Each exception notes the DBMS with which you should not use our improved syntax.

All tests were run on a single CPU Windows NT machine, with no other jobs running at the same time. The main test program was written in C and used ODBC calls to communicate with the DBMSs. A second test program used JDBC to test the DBMSs' response to specific calls. Unless otherwise indicated, each performance test was run three times for 10,000 rows of randomly inserted data, with the relevant column(s) indexed as well as without indexes. The gain for each DBMS was then calculated as the average of the three test runs.

We want to emphasize that our gain figures do not show absolute performance benchmark results. That is, a "GAIN: 4/8" note does not mean that any or all DBMSs ran 50% faster. It merely means that 50% of the DBMSs ran faster, and the rest showed no change.