Hibernate hmm!

I have not had the pleasure of using Hibernate before on a project until now. Recently I needed to retrieve email addresses for certain types of users. The types of users would be sent in from a Flex client. The database tables look similar to this.

Normally I would run a simple query like this to retrieve these email addresses

select email_id from USER, user_type_app_assn
where user.rec_del_ind = 0
and user.user_id = user_type_app_assn.user_id
and user_type_app_assn.app_id = 1
and user_type_app_assn.user_type_cd in (3,4,5)

Since the project uses Hibernate I looked at samples that other team members had done, so I started mimicking what they did. I decided to write a set of criteria with restrictions linking it to the proper objects. The code looked similar to this.

Criteria criteria = session.createCriteria(User.class);
criteria.add(Expression.eq(User.REC_DEL_IND_FIELD_NAME, "0"));
Criteria crit = criteria.createCriteria("userTypeAppAssns");
crit.createCriteria("appCode").add (Restrictions.eq ("appId", new Long (appId)));
crit.createCriteria("userTypeCode").add(Restrictions.in("userTypeCd", userTypeCodes));
tx = session.beginTransaction();
List userList = criteria.list();

You are saying it looks OK based on the database schema, what is your issue. Well, the issue is performance I found that it took forever and was running tons of minor queries and dumping out lots of logging statements. This all caused my Flex client to time out (subject of a future post). I sent my code off to a couple of fellow developers for them to do a code review. They were going to have to research it more not being familiar with the way I was doing things. The did offer up using HQL as this is the primary way that they were trained to use Hibernate.

Cool, I can code SQL how hard can this be. So I rewrote the above code to look like this.

tx = session.beginTransaction();
List userList = session.createSQLQuery("select email_id from UAE_USER, uae_user_type_app_assn" +
" where uae_user.rec_del_ind = 0" +
" and uae_user.user_id = uae_user_type_app_assn.user_id" +
" and uae_user_type_app_assn.app_id = " + appId +
" and uae_user_type_app_assn.user_type_cd in (" + userTypesSQL + ")"
).addScalar("EMAIL_ID", Hibernate.STRING).list();
;
                               
tx.commit();

After deploying and running the above I noticed a huge difference in performance (no time outs and the logs were cleaner). I have some theories as to why this happening. When you are grabbing the User object it is going to all of the other associated tables and pulling back data, thus the minor calls I was seeing and when you are talking 1000s of records this can take time. So I can see that using this second approach when you are not needing all of that data maybe a better approach.

I still have a ton to learn about Hibernate so if there is away to use the Criteria and not have it perform the other searches, I would love to hear about it.