Performance improvements to EoD SQL 2.0

Having released EoD SQL 2.0, I thought I should take another look at the performance of the new code. Although the API does run a lot faster once you have a Query object in memory, constructing the Query in the first place is not as fast as the EoD SQL 1.0 branch. There are two reasons for this:

  1. EoD SQL 1.0 Lazy-Loaded all of the method implementations (and in fact almost all of the data)
    1. Although faster to construct an object, the actual execution was much slower
    2. EoD SQL 2.0 eager builds as much data as possible
    3. EoD SQL 2.0 also works with tighter bindings under the hood than EoD SQL 1.0 did
      1. Takes the guess work out of turning a row into an object
  2. EoD SQL 1.0 Cached it’s implementation objects and reused them extensively
    1. EoD SQL 2.0’s close bindings makes it much harder to cache the data for re-use
    2. However, much of the data is very lightweight to construct

With these two factors in mind, I climbed into the code and added an internal cache structure (the first part of EoD SQL 2.1). The new cache is very different to those used in EoD SQL 1.0:

  • The new cache doesn’t store actual bindings, but rather all the data required to create a binding
    • Once a method is called, the implementation will store the resulting binding for that Query instance forever
    • Thus caching is not as useful, especiall since two different methods can’t share bindings anymore
  • The new cache makes use of SoftReferences instead of being a permanent cache
    • Follows the caching technique used by java.lang.reflect
    • If you create static references to your Queries, the cache can be garbage collected
    • If you manage your Connections by hand, the parts of the cache that are in more use will stick around

This change marks the first part of EoD SQL 2.1, the new code is in Subversion if you want to try it out for yourself.

Easy GWT Database Binding with EoD SQL

Connecting Java to a Database effectively can be a lot of work at the best of times. Sure JDBC does make life easy for us, but theres still turning to ResultSet into nice Java Objects. When you throw GWT into the mix, what was a lot of work becomes a bit of a nightmare (especially when you’re trying to work with JPA or Hibernate). Fortunately EoD SQL comes to the rescue!

For those of you who are either new to my blog or new to EoD SQL, go check-out this little snippet of code over on the EoD SQL homepage. You can think of EoD SQL as a very lightweight Hibernate. It’s also the perfect way to connect you GWT application to your database. Unlike Hibernate and JPA, EoD SQL requires no modification or work-arounds to get it to work perfectly with GWT.

You do need to keep to 2 very simple rules however:

  1. Your Query interfaces may not return DataSet or DataIterator objects (since they require a working JDBC Connection)
  2. You will still need an RPC Servlet between GWT and EoD SQL

That said, there are strong upsides to these “limitations”:

Instead of returning a DataSet or DataIterator: have your Query interfaces return single objects, or arrays of objects. Arrays are heavily optimized by EoD SQL and since your application is actually running in a browser somewhere, you want to provide it with the entire ResultSet (and not lazy-load the data, which is where the default Hibernate and JPA models fall over).

Instead of exposing your database methods directly to the client: you should place the functionality behind a RemoveServiceServlet anyways! Why? Because it gives you a good chance to verify that the user is allowed to take the given action on the database. If, for example, you wrote a forum application, and you had an admin-only “deleteForum” method that you exposed. If the RPC Servlet just deleted the forum and returned, anyone who got hold of the URL and knew anything about GWT RPC could delete every forum in the database. Not really something you want to allow.

EoD SQL works with GWT without any modifications. This is mostly due to the fact that EoD SQL is designed to be a “what you see is what you get” API. The fact that it doesn’t manage parent-child relationships automatically is also advantages in that it forces you to think about what data is going to be needed on the client side. Bare in mind that GWT by it’s very nature runs in a limited environment on the client, sending and receiving complex object graphs is never a good idea. It’s better to lazy-load child relationships when there is a strong chance the child data will not be needed.

I may consider an eod4gwt API which includes a special RPC Servlet implementation for handling EoD SQL requests (which would allow you to put the @Select and @Update annotations in your RemoteService interface). There would need to be a demand for it though 😉