Case Study: Using JSPs to Overcome Network Latency
We recently deployed a batch application into production that made hundreds of thousands of hits to an Oracle database during the course of a normal run. These hits were both queries and insertions, separated by some logic that unfortunately kept us from grouping those queries and insertions into single statements. But testing showed that performance was more than acceptable, so the application went into production. However, a few months later, work began to utilize a part of the functionality of this application at a site remote from the database server. Early testing here indicated that application runs that took under 20 minutes locally were now taking many, many hours at the remote site. Tests quickly confirmed our fears that network latency was to blame. Because the hope was to roll this application out in a matter of months to this remote site, a reasonably quick solution was required.
In looking at the code, it was clear that there was no easy way through straight SQL to reduce our trips to the database by any great margin. The simple yet pesky business logic in the middle would require some thorough analysis and a complete rewrite of portions of our code if we moved to PL/SQL. After a little research, we came across Java Stored Procedures, which immediately looked promising. Using JSPs, we can reuse the majority of our existing code and run it directly on the database server, seemingly eliminating the latency issue. To test this, a simple application was developed to perform several thousand queries against the database, both one at a time via PreparedStatements and all at once using JSPs. As the number of queries was increased, the former method took exponentially longer and longer due to the compounding effects of latency, whereas the latter barely seemed affected. With this, we were confident that JSPs would eliminate our latency problems.