How (Not) To Use Prepared Statement in Java

Recently I was given a task to implement a new use case in a fairly old Java application. The task didn’t seem to be interesting at fist. There was an existing method to fetch a Task entity by its ID from a database and then it was enriched with a list of Owners fetched by the foreign key stored in the owner table. No Hibernate or another persistence framework was used at all. The underlying Sybase ASE database was accessed using plain old java.sql.PreparedStatement and the method in question looked something like this:

Task getTask(Long id) {
     Task task = null;
     Connection conn = openConnection();
     String sql = "SELECT * FROM TASK WHERE ID = ?";
     PreparedStatement stmt = conn.prepareStatement(sql);
     stmt.setLong(1, id);
     ResultSet rs = stmt.executeQuery();
     while(rs.next()) {
          task = createTask(rs);
          task.setOwners(getOwners(task.getId()));
     }
     return task;
}


The getTask(Long) functionality was exposed as web service. It worked reasonably well for many years until a new client application started calling this service in a loop to get back multiple tasks by a list of IDs. The task table contained approximately 1 million rows each task having 2-3 owners on average. The performance was horrible, it took around 60 seconds to get back 20+/- tasks with their owners.

I said: “Nothing is easier, I’ll do it in a batch plus I’ll pre-fetch all the owners in one call!”. There was one little hiccup namely that java.sql API does not support passing down a list of parameters to use them in an IN (…) clause. “No worries, I’ll implement a helper function to generate as many parameter placeholders (?) as needed and then another for setting a value for each of them”. So, my initial solution looked like this:

List<Task> getTasks(List<Long> ids) {
     List tasks = new ArrayList<>(ids.size());
     Connection conn = openConnection();
     String sql = "SELECT * FROM TASK WHERE ID IN ("+generatePlaceholders(ids)+")";
     PreparedStatement stmt = conn.prepareStatement(sql);
     setLongParams(stmt, ids);
     ResultSet rs = stmt.executeQuery();
     Map<Long, List<Owner>> ownersMap = getOwners(ids);
     while(rs.next()) {
          Task task = createTask(rs);
          task.setOwners(ownersMap.get(task.getId()))
          tasks.add(task);
     }
     return tasks;
}


The performance was much better. But still, it took 10-15 seconds to get back the results for the same list of IDs as before. It was very suspicious given that the same queries executed from a desktop SQL client returned under a second. Then I started thinking: “How does that prepare statement work?”. It turned out that in Sybase ASE prepared statements are compiled into a stored procedure. Which obviously takes time plus the number of input parameters (number of IDs) varies between invocations and that cripples the reusability. The solution I came up with was to fall back to use a simple java.sql.Statement with hard-coded values:

String sql = "SELECT * FROM TASK WHERE ID IN (" + join(",", ids) + ")";

With these modifications and proper indices, the queries return data in 50 – 60 milliseconds which is a 1000 fold improvement compared to the initial state – don’t take this the wrong way but I think it’s pretty cool.

One thing to note: parameterized prepared statements are often used to prevent SQL injection – or at least make really hard for hackers to pull off one. Using a simple statement is less secure but in this case the IDs are converted into Long values before they are passed down in the query. If we were to use String parameters we would have to take additional measures to make our code more secure.

To summarize my little story I think the takeaway message is that interfaces are good, but when it comes to performance optimization you have to brace yourself and look behind the curtain.

KAPTAR / co-working experience

What should a developer with a full-time job do on his day off? Of course, to grab his laptop and head to the nearest co-working space to work on a side-project. Let me share my experiences about working a full day at Kaptar (bee hive).

Kaptar is located right in the center of Budapest within walking distance from major sights like the Basilica or the parliament. It’s not a big place but you have everything you need: tables, meeting rooms up to 12 people, sound proof boxes for skype calls. I can definitely recommend the table counter, there is also a kitchen (with very good coffee) and even a shower which is very handy if you ride a bike. Kaptar has a very nice modern atmosphere with a Scandinavian twist to it.

Kaptars DNA is not defined but its superb location or by its cozy furniture, but by the people who work there. I had a chance to meet interesting people from Toronto, Russia, and Germany working on very different projects. The staff is extremely friendly and they really go the extra mile. Kaptar isn’t just about strangers working in the same space but about creating a community. Every day a so-called community lunch is organized where you have a chance to know your co-workers a little better. The staff even ordered lunch for us all I had to do is to select 3 courses from a menu.

Kaptar offers a wide range of services. You can reserve a meeting room in case you have an important client to meet. Believe me, it’s much better than a noisy coffee place. There is a possibility to prepay for fix places, but Kaptar is very friendly towards occasional co-workers too. You can pop in any time you wish even only for half a day. So, what are you waiting for? Grab your notebook, phone and whatever else you need during your daily work and give Kaptar a chance.