Proper selection of Hibernate Inheritance herirachy.

In this post, I will explain a scenario which could have been avoided if proper hibernate inheritance strategy was selected in the first place. To begin with it was not even identified as Hibernate inheritance strategy issue. Infact IT noticed site slowness and reported about it to Engineering that a query was executing slowly (over 40 seconds). From the query which looked as below:

select column1,....,column50
from (
select .. from table1
union
select .. from table2
union
select .. from table3 ) where xid=1;

Can above query be optimized? Lets observer the query first. The above queries three different tables, then unions them and then filters the result using where clause. As you guessed it, querying individual table, union them and then filtering doesn't seem right. What happens if each table has 10K records, wont we be gathering 30K records before filtering on xid! Think what happens if there are millions of records. Besides that the query would be inefficient if queried on un-indexed column. This behavior was being observed in production where we have lots of records, queried on un-indexed column and hence cause for the query to be slow. This was confirmed on explaining the query. Below is the screen shot which confirms the behavior.

Ideally we would want to have a query (also indexing the column being queried) which is as follows:

select column1,....,column50
from (
select .. from table1 where  xid=1
union
select .. from table2 where  xid=1
union
select .. from table3 where  xid=1
);

This improves the query. Can be confirmed by explain on the query. From the below screenshot, we can confirm that lot less records are fetched before union. Also this time, column is indexed and hence the less time! :) 

Again, this is was all Hibernate doing this stuff due to the fact that inheritance strategy selected was TABLE_PER_CLASS. As mentioned here, this strategy runs into join issue and hence the cause for being slow.

Solution adopted: Changed the strategy to SINGLE_TABLE (combined the data from three tables into one. The records are differentiated by a discriminator column). The only trade-off adopting SINGLE_TABLE strategy is that records can be sparse (i.e. not all columns will be populated and hence cannot enforce null constraint). Luckily that was not the case for us! :)