Accelerate your Hibernate engine (next)

4.6 HQL Tuning

4.6.1 Index Tuning

HQL looks very similar to SQL. The corresponding SQL WHERE clause can usually be guessed from the HQL where clause. The fields in the WHERE clause determine the index to which the database will be selected.

A common mistake most hibernate developers make is that whenever a new where clause is needed, a new index is created. Because indexes bring additional data update overhead, you should strive to create a small number of indexes to cover as many queries as possible.

Section 4.1 lets you use a collection to handle all possible data search conditions. If this is not practical, then you can use the backend profiling tool to create a collection of all the SQL involved in the application. You end up with a small set of indexes based on the classification of those search criteria. At the same time, you can also try to add an extra predicate to the WHERE clause to match the other where clause.

Example 7

There are two UI searchers and a back-end daemon finder to search for a table named Iso_deals. The first UI searcher has predicates on the Unexpectedflag, Dealstatus, Tradedate, and Isold properties.

The second UI searcher is based on a filter that the user types, including content that has other properties besides Tradedate and Isold. All of these filter properties are optional at first.

The back-end searcher is based on the Isold, Participantcode, and Transactiontype properties.

After further business analysis, it was found that the second UI searcher was actually selecting data based on some implicit unexpectedflag and dealstatus values. We also make tradedate a necessary property of the filter (each search filter should have the necessary properties in order to use the database index).

With this in mind, we constructed a composite index using Unexpectedflag, Dealstatus, Tradedate, and isold in turn. All two UI searchers can share it. (The order is important, if your predicate specifies these attributes in a different order or lists other attributes before them, the database will not select the composite index.) ）

The back-end searcher and the UI searcher are so different that we have to construct another composite index for it, using Isold, Participantcode, and transactiontype in turn.

4.6.2 binding Parameters vs. String concatenation

You can use binding parameters to construct a hql WHERE clause, or you can use string concatenation, a decision that has a certain effect on performance. The reason for using binding parameters is for the database to parse SQL at once and to reuse the resulting execution plan for subsequent duplicate requests, which saves CPU time and memory. However, to achieve optimal data access efficiencies, different binding values may require different SQL execution plans.

For example, a small range of data might return only 5% of the total data, while a large range of data might return 90% of the total data. The former is better with indexes, while the latter is best to use full table scans.

It is recommended that OLTP use binding parameters, and the Data Warehouse uses string concatenation because OLTP usually inserts and updates data repeatedly in one transaction, with only a small amount of data; The data warehouse usually has only a small number of SQL queries, and a certain execution plan is more important than saving CPU time and memory.

What if you knew that your OLTP search should use the same execution plan for different bound values?

Oracle 9i and later versions can explore parameter values the first time a binding parameter is invoked and an execution plan is generated. Subsequent calls do not probe again, but reuse the previous execution plan.