hibernate.criteriaImpl.list() very slow when SQL has LIKE operator

Hibernate ORM
1

Hi Hibernate Experts,

Please help resolve this performance issue. Scenario is detailed below:
Test case #1. Search (exact match) - PO Number EQUALS XYZ (1.8 s)
Test case #2. Search (like operator) - PO Number STARTS WITH XYZ (6.5 s)

The search results are exactly the same. The DB queries take < 50ms, when run in the Query Analyser Tool (SQL Server Management Studio). We have confirmed that the SQL queries are not slow.

The profiler (Yourkit Java Profiler) screenshots show the hibernate.criteriaImpl.list() consuming 1.8 s and 6.5 s for the two tests respectively.

Why is hibernate consuming more time, when the input (PO Number) and output (Search results) are exactly the same?
Due to this our performance SLA = 5 s is getting violated.
Please advise.

Tech Stack:
Hibernate 3.6.10
Liferay 6.2
Microsoft SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8)
Driver: Microsoft SQL Server JDBC driver

Profile_ShipmentEqualsPONumberPUB073117KF!

2

Second screenshot
Profile_ShipmentStartsWithPONumberPUB073117KF

3

It might be that the Query Analyser Tool only shows the execution plan without actually running the query, which might explain why the value is 50ms.

You didn’t say how many items you are fetching. If the query returns thousands of records, that would explain why the query takes so much time. Use pagination instead.

The exact match might use an index while the LIKE query might not because you either used a function or you used a wildcard operator at the very beginning.

Just add the actual execution plans executed by SQL Server as explained in this MSDN article. Then, we can better analyze what actuaally happens.