Today I let out a dejected sigh after reading a response to my latest post on the Joel on Software message board. My post was just a reminder about the BostonScalability User Group meeting that takes place next Wednesday the 28th. I post the meeting announcements on JoS and a good number of people hear about the meetings from the JoS board. Every month a few new people tell me they found the group through JoS. So why did I sigh after reading the response to the meeting reminder?
(more…)
The Boston Scalability User Group is hosting a technology round-table meeting on Wednesday January 28th at 6 p.m. The meeting is at the IBM Innovation Center in Waltham, MA.
This is the first time we’ve done the round-table style meeting and I’m excited to see how it goes. Guests are encouraged to come prepared with questions, answers and opinions on application scalability tools, strategies and designs. Hot topics will include platform and software stack, cloud computing and resources, vendor tools and support and CDNs. Those are my guesses about hot topics doesn’t mean the meeting is limited to those topics.
Guests, be the regular or first-timers, will drive the direction of the discussion. We’ll talk about solving technical problems based on past experience or serve as an advisory panel on where and when to use a particular tool.
Full meeting details are at the BostonSUG web site. We ask that you sign up for the meeting at the meeting registration page so we have an idea of how much food to buy. There will be snacks and bottled water at this meeting.
Hope to see everyone on the 28th at 6 p.m.!
Tags:
criteria,
database,
hibernate,
Java,
mysql,
query,
sql Posted under
Java,
database,
hibernate,
scalability,
sql by
Anthony Chaves on March 18th, 2008.
Comments OffHere 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”.
