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 “); 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”.