Tuesday, 16 April 2013

Hibernate exception timings vs SELECT COUNT(*) to check entity existance

This is something that I've been curious about for some time.  How much faster really is it to throw an exception rather than doing a SELECT COUNT(*) to check if an entity exists?

My initial suspicions are that the SELECT COUNT(*) would in fact be slower due to the database transactions.  However, the time to build up the exception stack for the NoEntityFound is no small fate either.  It's also worth considering if the numbers fluctuate based on the amount of data within the table at that point?

Obviously, the analysis could get much more complicated and it's not my intention to go into the realms of indexes / partitioning / whether the entity is presenting a view of two tables / hibernate caching.  It's more a general curiosity.

So lets begin, here's my entity..

Now I'm going to use an extremely simple HibernateUtil class to get me the session, shown below.

To fire the exception, I'm going to use the .load() method from org.hibernate.Session. I'll also run the following code to see how checking the SELECT COUNT(*) would take...

Here are the results! Using the load method, with the given entity above this takes 1328 milliseconds. SELECT COUNT(*) came in last with 1454 milliseconds, all hail load!
Now something that does seem important is as both methods were pretty dam close, which one is more readable? To be honest, the load does just seem to make sense and catch the exception but you could easily argue exceptions shouldn't control the flow of your application if you're checking existence of an object. Currently, I have 608 entries in the table that the Player entity is hooked up to. Below are the results for the different times.
.load()SELECT COUNT(*)number of values in table
What we can see from the results above, interestingly is the amount of data in the table, has pretty much no resemblance to the time either query takes to run.

No comments:

Post a Comment