Hibernate and Database (Microsoft SQL Server) Indexes

UPDATED: 13 August 2014
Hibernate logo

Hibernate is an Object Relational Mapping (ORM) library used in java. This framework used to map relational database into Object Oriented domain model. This framework can handle small and enterprise level application both. Hibernate uses Prepared Statement (pre-compiled query) for data retrieval until unless you write hard coded native queries.

Database Index
A database index is a data structure that improves the speed of data retrieval operations on a database table at the minimal cost of additional writes and little more additional space to maintain a restricted set of selected data.

Hibernate and Microsoft SQL Server Index
Today we will intercept the interaction of Hibernate and SQL Server Index. We will see How actually hibernate queries stored in database.

In the following example we have table user_master. It has column user_name with datatype varchar(100). We will create an Index on column user_name and test it against Hibernate.

Step 1: Create an Index on column user_name.
Note: Index will be used only when table holds huge number of records minimum 1500-2000 records.
CREATE NONCLUSTERED INDEX [IXNc_user_master__user_name] ON [dbo].[user_master] 
 [user_name] ASC

Step 2: Executing simple select query in SQL Server Studio.
/* Press Ctrl + M or enable execution plan in SQL Studio. */
/* After execution of query you can see the execution plan, that shows Index Seek(Search in index) of 'IXNc_user_master__user_name' */
SELECT * FROM user_master WHERE user_name = 'vicky_thakor'

Step 3: We will verify that index used for seek (search) or scan. Seek cost is very low but scan cost lot more than regular. Execute following query that shows SEEK and SCAN count on Index and save/remember the details for reference. (*Change database name and table name.)
  si.name AS IndexName, sc.Name AS ColumnName, sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si 
 ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic 
 ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc 
 ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('database_name') AND sis.OBJECT_ID = OBJECT_ID('user_master');

Step 4: Now clear all available Prepared Statements from SQL Server cache.

Step 5: Execute same query using Hibernate Criteria
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.eq("Username", "vicky_thakor"));        

Step 6: Execute following query to check what query Hibernate generated to fetch the data.
SELECT usecounts, text 
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

/* Hibernate generated following query */
(@P0 nvarchar(4000))select this_.uid as uid0_0_, this_.Firstname as Firstname0_0_, this_.Lastname as Lastname0_0_, this_.user_name as user4_0_0_ from user_master this_ where this_.user_name= @P0

Hibernate and Microsoft SQL Server database indices

Important: We have column user_name with datatype varchar but hibernate fetching data using nvarchar datatype.

Step 7: Execute Step 3 to check index is used with SEEK or SCAN. You will find that index is scanned and that cost a lot.

Lets check how database act with different datatype for index. You are requested to turn on execution plan setting in Microsoft SQL Server [Read more: Execution Plan in MSSQL Server]. We will use Prepared Statement at database level with both datatype.
/* Execute following query which leads to Index Scan (Check attached image) */
EXECUTE sp_executesql
@statement = N'SELECT * FROM user_master WHERE user_name = @P0',
@parameters = N'@P0 nvarchar(4000)',
@P0 = 'vicky_thakor'

/* Execute following query which leads to Index Seek (Check attached image) */
EXECUTE sp_executesql
@statement = N'SELECT * FROM user_master WHERE user_name = @P0',
@parameters = N'@P0 varchar(4000)',
@P0 = 'vicky_thakor'

Hibernate and Microsoft SQL Server database indices

Hibernate used nvarchar to support Unicode characters and we have column with varchar datatype. Database always upgrade lower datatype to higher datatype to eliminate data lose. Datatype conversion leads to index scan rather index seek.


You are suggested to change your datatype from varchar to nvarchar. If you are not interested in datatype change I have another solution for it.

Add parameter called sendStringParametersAsUnicode in your connection string. It will force hibernate to use varchar instead nvarchar.
<property name="hibernate.connection.url">jdbc:jtds:sqlserver://;sendStringParametersAsUnicode=false</property>

Its not only the hibernate use nvarchar, You should check all other framework you are working with. Indexes are meant to increase the performance but this intermediate framework and poor database knowledge leads to low performance of application.