This month, I will give you empirical numbers comparing most access methods available for reading data from physical files on the AS/400 platform. I test ODBC from Visual Basic (VB), Java Database Connectivity (JDBC) in Java, precompiled SQL in ILE C, record- level access (RLA) in RPG, RLA in C, Call Level Interface (CLI) in C, RPG with embedded SQL, and OLE DB from VB. Sound like a lot of tests? It is, but my quest is for the king of access methods. Do you want to know who’s the king?

History Is Full of Losers

First, let me explain why I am doing this. I am so tired of clichés. They’re almost as bad as pregnant chads with dimples. To wit: I was at a conference a while back and had to listen to some industry “expert” tell everyone in the room that ODBC is slow, so you should always use JDBC. Then another pontificator extolled the virtues of RLA on the AS/400 and said that no one should be doing SQL-based development, not even in client/server or Web scenarios. The real insult was that none of these so-called experts had any empirical evidence for their claims. Their claims were all hearsay and innuendo. One “expert” said RLA is the de facto king of access methods.

Another “expert” said that JDBC is queen of the delta. Well, my buffer runneth over. I know for a fact that ODBC screams, that SQL is righteous, and that these so-called experts couldn’t scrape together a nickel to buy a clue. But I didn’t have the numbers, either. And I, unlike my brethren, wanted facts, not speculation and gossip. So I set out to compare all access methods. I will give you the numbers and the methods and allow you to judge which access method is appropriate for your project.

The Method to the Madness

Testing access methods presents two problems. First, you need data. Second, you need a test that is platform- and language-neutral. For the data portion, I began with a table on the Midrange Computing AS/400 called WEBTEMP2. This table contains 6,071 records and has a record length of 120 bytes. The table contains seven fields of varying data types and lengths that represent fictitious hits against my Web site over a period of two months. I used this table extensively in creating SQL queries for my upcoming book, so I figured it was adequate for testing access methods. Figure 1 shows the structure of the table.

The next logical question was how to fairly test performance between all these varying platforms, languages, and access methods. Late one evening, after much thought and consternation, I determined that a true measure of access-method performance would be records per second (RPS) of a nonsorted record set delivered to the application. I did consider other tests but decided the RPS measure would be best. Here are the other tests I considered and the reasons I rejected them:

• If I issue an SQL statement with ordering, I am including sort time on the AS/400.
• If I make a logical file to order the results, then I am including time spent on the AS/400 doing key-row positioning to retrieve the results.

• If I include calculated fields, I cannot emulate these fields using RLA in an RPG or C program.

Since all I wanted was to time the access methods, the final determiner came in the immortal words of Shakespeare: Sequentially reading records is easy; comedy is hard.

The fairest test that can be performed between platforms and languages to get a basic measure of performance is the following:
1. Get a time tick from the system clock.
2. Open the table or result set.
3. Read until the end of the set or table.
4. Get another time tick and calculate the elapsed time.
5. Calculate records per second as records read divided by elapsed time. In essence, each language and access method I wanted to test—RPG, C, Visual Basic, ODBC, JDBC—meets my new two-pronged criteria for testing:
1. Each can do the aforementioned steps.
2. I have enough meager programming ability to write those simple programs.

Off to the Races!

By later in the evening, I was on a roll. I decided to do my client testing first. I wrote a program in VB, which would use Microsoft’s ActiveX Data Object (ADO) to open a connection to the AS/400, execute a select statement against the WEBTEMP2 table, read the records, and do the timing. I then modified the program so that it could accept an argument for the driver that I wanted to test. I set about testing OLE DB and ODBC drivers under the ADO access methodology. I wrote a program that communicated directly with ODBC, bypassing ADO, in order to see how much overhead ADO added to the client program. Finally, my colleague Don Denoncourt helped me fix my Java environment, and I got the program running in a Java Virtual Machine (JVM) using JDBC to connect to the AS/400.

To make the tests most excellent and fair, I ran each program five times and picked the best time the program marshaled. The reason behind this was that network bandwidth, server job swapping, and other factors come into play; so, to be fair, I wanted each access method to have its best shot. Figure 2 (page 80) shows the results.

Race on the Server

Once I had the numbers, I really began to get cocky. Then, I remembered that some “expert” was still going to talk about record-level access, so I needed to compare the server-based access methods with the client-based methods. And what about the whole question of SQL on the AS/400 itself? This question meant I had to write a lot more programs, so I went to the store, stocked up on Jolt cola, and enlisted the help of my cohorts Ted Holt and Don Denoncourt. Ted was kind enough to write the RPG IV version of my test using record-level access and embedded SQL and then run the numbers. Don translated my weak Java into something that could run on the AS/400 JVM and then tested it in a compiled, interpreted native driver; a JDBC driver; and other modes that I had never even heard of. In addition, Don changed the program to use Java-based record-level access

so I could benchmark that, too. Finally, I created an ILE C program with embedded SQL, an ILE C program that uses CLI to execute SQL, and an ILE C program that did record- level access with the native C functions so that I could compare those access methods. Figure 3 (page 80) shows the results for all to see.

The Crux of the Biscuit

So, what do the numbers mean? Look at programs on the AS/400 first (Figure 3). RPG with embedded SQL returned an impressive 54,205 records per second. RLA in an ILE C program was the next fastest access method, coming in at 28,106 RPS. Next, ILE RPG using RLA came in at 26,511 RPS. This meant that RPG with embedded SQL was 92 percent faster than ILE C with RLA. Now for the big surprise: ILE RPG using embedded SQL was 246 percent faster than ILE C using embedded SQL.

Next, look at the Java performance numbers (Figure 3). On the server, Java’s best performance clocked in at 7,332 records per second. In fact, the best performance numbers that I was able to achieve were from the JDBC driver, not the DB2 native driver. To get these numbers, I set the JDBC connection properties to use extended dynamic. This causes the AS/400 to store SQL statements in an SQL package file on the AS/400. Then, when the program attempts to execute the statement, the query execution plan is retrieved from the package file instead of the optimizer having to figure a plan for the query. This saves time on the preparation step of the query, which leads to the better numbers for JDBC than the native driver. In fact, JDBC almost always took one half of the time to prepare an SQL query when compared with the native driver. However, you should note that the movement of records under the native driver was always faster than JDBC. Because my result-set size was small, my overall task was completed quicker because of the saved execution plans. If I were retrieving a large amount of records, the native driver would soon eclipse the JDBC driver in records per second. As for the abysmal numbers for Java with RLA, I just want to mention that I ran the tests over and over and the performance of Java with RLA was always terrible. There is definitely something not right when using RLA with Java.

Client/server Performance

Now, look at when the application is not on the AS/400 (Figure 2). First, programming directly to the ODBC API was the clear winner with 6,266 RPS. The next level down was using ODBC via the ADO interface at 5,734 RPS, which was 8.5 percent slower than going native to the ODBC API. ADO using the OLE DB provider came in at 4,625 RPS, which was a 26.2 percent performance penalty. Finally, JDBC weighed in at 2,296 RPS, which was roughly 63.3 percent of the performance of ODBC.

To really allow you to understand these numbers, I should point out that they express two things: First, there is network latency. Second, the more machinery the environment provides, the more processing will be performed, which will use more time. To wit: When I program directly to the ODBC API, I have to write a whole lot of code. Coding directly to ODBC takes almost 10 times the number of lines of code, as opposed to using ADO.

So, when do you write directly to ODBC? Only if you are trying to make the best performing application possible or you need to take advantage of special ODBC tricks, like bulk loading of records (more on that in an upcoming issue). The penalty for using ODBC via ADO was only 8.5 percent. This 8.5 percent makes no difference in the real world. However, note that the penalty for using OLE DB was 26.6 percent. If you are choosing to use OLE DB, you have to ask yourself if the extra machinery of the OLE DB provider is worth the performance hit. If I had written my test program in C directly to the ODBC API (instead of through VB class modules), I could probably pick up an additional 1,500 to 2,000 records per second. If I feel like a masochist, I may attempt this late one night.

The lowest results were for the client running Java. This is due in part to the overhead of Java and the fact that the JDBC components are not as mature as ODBC and

OLE DB. One thing to bear in mind as you look at these numbers is that all of these client programs are talking to the same server subsystems using the same protocols. The only overhead is network latency, client code overhead, and garbage collection.

What’s It All Mean, Mr. Natural?

At the base level, these numbers give you a way to compare the different methods of accessing data and judge them based on your project requirements. There is no one superior method of access, as each has its merits and drawbacks. If you just need to open a table and read a record, RLA is fine. If you need calculated fields or want to cut down on logical files, try SQL, since the performance with RPG is superior to RLA. If you need cross-platform capabilities, use JDBC or the native driver. Finally, if you are doing Active Server Pages on Windows NT, use ODBC, as ODBC is threadsafe and performs best via ADO. As I always say to my clients, pick the appropriate tool for the job at hand. If you only have a hammer, everything looks like a nail. Now you have an idea of how each of the tools in your toolbox ranks, so you can begin to pick the one that is best-suited to your job. Pick the appropriate access method for the job at hand, and leave the religious wars to the clueless pontificators.

Please note that these numbers only test read and memory movement/management performance. In forthcoming issues of MC, I will publish more benchmarks and tests that further explore the performance differences between keyed access, updating, and inserting records. For now, I invite you to download the code at www.midrangecomputing.com/mc that was used to create these numbers, run the tests on your hardware, and send me your results for publication in the future. I hope these numbers serve to enlighten the debate about access methods and help you make an intelligent decision about the database technology you need for your next project. Now that you have some empirical results, I expect that you can dispel some of the myths and rumors surrounding AS/400 data access.

Column Data Type Length

REQTYPE Char 10 REQFILE Char 80 BROWSER Char 10 REQTS Timestamp 10 REQSIZE Integer 4 REQUSER Integer 4 GEOID Smallint 2

120 bytes

Figure 1: The data structure of the host table, from which the applications read data, solves one of the problems of testing access methods.

6,266

26,511 28,106

6,000

5,000

4,000

3,000

2,000

1,000

60,000

50,000

40,000

30,000

20,000

10,000

RPG SQL ILE C RLA

RPG RLA ILE C SQL
ILE C CLI
Java JDBC Packaged
Java Native DB2

5,734

11,630

4,625

7,245 6,570 7,332

824

2,296

ODBC
ADO ODBC JDBC

ADO OLE DB

Figure 2: This graph shows the RPS access times from PC client applications.

54,205

14,594

Java JDBC No Package

Java RLA

Figure 3: These results show RPS access times of AS/400-based programs.