Some background first. When writing a WebObjects / EOF application there are many, many situations where you want to run a query against the database, but you really don’t need all the matching rows. Most typical is when your end user does a query against a huge pile of data and you want to limit what is displayed to a single page of results (trust me - they always find a way to run a fetch that returns the whole database). EOFetchSpecification allows you to add a fetch limit to queries and this does what you want - only returns (at most) that many matching objects.

So we are good, right?

Well, there is a little problem hiding in the magic of automatic generation of SQL by EOF. EOF generally does a great job when the SQL being generated is “standard” - i.e. part of the SQL 92 standard implemented by most database vendors and well handled by JDBC. Unfortunately the concept of fetch limits was, umm, overlooked by the standards folks. This meant that the database vendors all chose their own syntax and now the commands for limiting results are quite different from vendor to vendor. In Sybase you send a separate command first: SET ROWCOUNT XXX. In Oracle there are several approaches, all with some tradeoffs. OpenBase SQL uses RETURN RESULTS XXX at the end of the SQL statement. In Postgres it is LIMIT XXX.

To work around this, EOF takes an approach that is simple and effective, but less than optimal. EOF fetches all the results from the data server and then just turns the requested subset into actual EO objects and discards the rest of the data. This works, and uses less memory than turning all the rows of data into objects, but thats all the good things you can really say. And on the bad side, the database server may need to marshal all the results that match and may end up shipping all the data across the network to your application.

I recently was working on a project that allowed end users to search through a very large amount of data. Not only was this allowed, it was the primary form of user interaction with our application. Most of the users would be searching, most of the time. In all cases we needed to limit their results to a single page of potential matches. As the number of users in our application scaled up, the server ended up spending most of its time handling these searches and the memory footprint of the dataserver grew unacceptably large as it tried to process all these fetches at once. Compounding this was the fact that often seemingly innocuous queries from the users actually matched tens of thousands of records, from which we needed at most 100.

The database server we were using was OpenBase. In order to increase our capacity we needed to lower the load on the dataserver. Working with the excellent team at OpenBase we figured out that we could get a big boost if we could shift from the default EOF client-side fetch limit approach to a server side solution by using the RETURN RESULTS XXX syntax and having the dataserver limit the results before data was sent back to the client.

This was a large application with a complex data model and many places where queries were done. We immediately rejected the approach of switching from dynamically generated SQL to hand coded SQL just to be able to add the fetch limit clause to the end of the command. Additionally, we had a large code-base already in production and we wanted a solution that involved changing as little of the existing code as possible. The solution was to modify the JDBC plugin that actually generated the database vendor-specific SQL commands from the generic activities of the EOF layer.

I obtained the latest version of the OpenBase plugin from the downloads area of the OpenBase site. This is kindly distributed in source form. This plugin does a lot more than just support fetching - it handles inserts, updates, primary key generation and table creation. (NOTE: You should also be linking to the OpenBasePKPlugin framework for optimal primary key generation with OpenBase.) At first blush this looked to be an easy upgrade - there is an obvious method called assembleSelectStatementWithAttributes that creates the select string from various attributes passed in. Unfortunately, the attributes do not include the fetch limit number or the FetchSpecification. After much more digging into this area of EOF that I had never really investigated before, I discovered there was another method that gets called on the plugin’s JDBCExpression subclass that was defined but not overridden by OpenBase’s existing implementation called prepareSelectExpressionWithAttributes that does get called with the original EOFetchSpecification. Problem solved! I grab the fetch limit number there (if there is one) and hold on to it until assembleSelectStatementWithAttributes gets called a few steps later in the SQL generation.

The modified plugin code has been returned to OpenBase and will be available for download from them soon. It will also eventually replace the version currently included with WebObjects. For now, you have permission from OpenBase to download it from here as well.

NOTE from the README: “This plugin code replaces the OpenBasePlugin inside WebObjects. Since it is a replacement and not a subclass, it needs to be included inside your application jar. The only problem with this approach is that it will not work as part of a framework.”

Download the plugin: OpenBasePlugin.java

Download the README: OpenBasePlugIn_README.rtf