Home > Articles > Data > Access

Overcoming High-Latency Database Access with Java Stored Procedures

  • Print
  • + Share This
Java Stored Procedures: a way to put slow Java code in your otherwise fast database or a quick solution for certain database performance problems? They can be both, as it turns out. Tim Stevens takes a look at a situation in which the adoption of Java Stored Procedures quickly and effectively solved some serious performance issues in a database-driven system, all without a complete rewrite of existing database code.

The first rule in database programming is this: If you want your code to run quickly, minimize your trips to the database. Queries generally run blindingly quick on the server, but the time it takes to transmit the query one way across the network and the results the other way can be much slower. The standard way to avoid this problem is to write database code to group queries or results together, sometimes by writing database-specific stored procedures. But that's not always the best option. This article takes a look at another way to achieve the same results—Java Stored Procedures—and outlines a case in which they were used to great effect.

Overview of Java Stored Procedures

Java Stored Procedures (we'll call them JSPs in this article) are nothing new, dating to late 1998 or so. Although they offer the potential to run arbitrary Java code directly on the database server, they never exactly caught on in the development community as a whole. Just the same, they are supported by most of the major relational database vendors—including Oracle, IBM, and Sybase—as well as many of the Open Source databases such as HSQLDB.

A JSP is simply a way to run Java code on your database server. Java classes are loaded into the database, methods within that class are wrapped with the database's procedural language of choice, and then those methods can be executed by anything that can make SQL calls. There have a few advantages over standard stored procedures. For starters, JSPs can often be moved from one vendor's database to another with few if any code changes, whereas most database procedural languages are highly specialized. Also, JSPs are straight Java code, which can be an advantage if you have a fleet of Java experts, but nobody who knows PL/SQL (or your particular database's procedural language of choice). Finally, JSPs can work with standard Java objects and Collections, making it very easy to send batches of objects to the database and use their full functionality once they get there.

Many advantages—but JSPs have their disadvantages, too. To begin with, debugging can be an absolute nightmare. You can't step through code running on a database server, and any output generally winds up in an inaccessible log on the server somewhere, if anywhere at all. Secondly, performance is generally not as good as that from a straight stored procedure, but it doesn't have to be that much worse, either. And, finally, although your DBAs are probably masters at troubleshooting sluggish SQL code, chances are they're not Java coders. So when it comes to tuning your JSP for performance, the DBAs might not be able to help much.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.