← Spring Web MVC and the benefits of pagination

Terracotta Tech Talk →

The Evil Query

March 18th, 2008 | Java, database, hibernate, scalability, sql

Here is the use case: I want to search all of my bookmarks by multiple tags with an “and” operation. That means I want to see bookmarks that are tagged with Java AND JavaEE, not just Java and not just JavaEE. I want to see bookmarks tagged with C# AND 3.5 not just C# and not just 3.5. For most of the project I’m using Hibernate and the Criteria API with a lot of success. In this case I was not able to massage Hibernate into creating the correct query and that’s fine. Hibernate has its uses and it is acceptable to write custom SQL when the need arises. I wrote a method to build up the query and execute it. Here is part of the method that builds up the query:

StringBuilder sqlString =
 new StringBuilder(
 “select distinct(b.id)
 from bookmarks b “);
int counter = 0;
for(Tag tag : tags) {
 String alias = “j” + Integer.toString(counter);
 String joinFragment = ” inner join bookmarks_to_tags {alias}
 on b.id = {alias}.bookmark_id
 and {alias}.tag_id = ? “;
 joinFragment = joinFragment.replace(“{alias}”, alias);
 sqlString.append(joinFragment);
 counter++;
}

Right away it looks like this query will get very expensive for large tag sets. For smaller sets the query performs in a reasonable amount of time but more than 12 tags makes the query take quite a while. This is exacerbated by the fact that this query will be executed multiple times if there is a sufficiently large result set (default is over 25) to give the user a paginated view. This totally flies in the face of being nice to the database. Hammering it with a complex query over and over won’t do the users any favors.

What are my options here? I can work on making this query less complex (something that I will certainly do), but there is still the problem that, no matter how nice this query becomes, the query will be executed over and over as a user pages through the results. The complexity of the query and its repeated execution makes the result set a good candidate for caching. Caching the result set in memory with a key tied to the user or their session will allow the expensive query to be executed once with the bookmarks stored in a cache. When the user pages through the results the cache will be consulted first where the result set will be stored. I’m in the process of selecting a caching package for this project so I will post my selection and integration process soon.

Until then, I’d be happy to hear any suggestions on making this query a little nicer while preserving its “andness”.

0 comments ↓

There are no comments yet...Kick things off by filling out the form below.

Leave a Comment