There is a specific reason that I have been asking about things like QueryTranslator and setMaxResults. I am working on a project that abstracts out much of the persistence layer functionality from developers, except for the creation of DAOs, BOs, and HQL statements. Developers send in an HQL query, and we are looking to do two types of queries behind the scenes. Some of our data tables will contain 5+ million records, and so we had to define our search strategies into three categories. One of the categories has been coined a goverened search strategy. This strategy is based on the suggestions of Dan Tow in
SQL Tuning (page 256):
Quote:
1. Determine the maximum-length list you want to return (1000 for us)
2. In the call, request one more row than the max (1001)
3. Query execution plane is robust without prehash, sort, or storing whole large rowsets
4. Request that the query result from the database be unsorted
5. Sort the result as needed in the application layer
6. Cancel the query and return an error that suggests a more selective search
So what we are trying to accomplish is two queries from one initial query. The user will send us an HQL statement and we want to test the governed portion before we submit the query. As far as the setMaxResults goes, Hibernate successfully does its job of creating a DB2 statement of "fetch first n rows". The only problem with this (which is DB2 not Hibernate) is this may be too much work just to check if the soon to be submitted query is going to request too many rows. So what we need to do is insure that the indexes are being queried against and that the query can acquire the necessary row count without hitting the data tables.
The following was done on a sister project in native jdbc, which worked with the suggestions made in
SQL Tuning and created a significant performance benefit between these two types of queries:
General Way
Code:
java.sql.Connection con = JDBCConnectionFactory.getConnection();
java.sql.Statement statement = con.createStatement();
String countSQL = "Select Count(*) From user u, user_account ua " +
"Where u.id = ua.id For Read Only";
java.sql.ResultSet rs = statement.executeQuery(countSQL);
int count = 0;
// Now select the actual count.
if (rs.next() == true) {
count = rs.getInt(1);
System.out.println("Matches found: " + count);
}
rs.close();
statement.close();
con.close();
Problems
- This query may need to hit data tables
- count(*) requires a complete result set before actually completing
- This also means that count(*) will require a temporary table to be created
Faster WayCode:
long rowLimit = 1000;
java.sql.Connection con = JDBCConnectionFactory.getConnection();
java.sql.Statement statement = con.createStatement();
String countSQL = "Select 1 From user u, user_account ua " +
"Where u.id = ua.id For Read Only";
java.sql.ResultSet rs = statement.executeQuery(countSQL);
int rowCount = 0;
while (rowCount <= rowLimit && rs.next() == true) {
rowCount++;
}
if (rowCount > rowLimit) {
System.out.println("Row limit exceeded!");
} else {
System.out.println("Matches found:" + rowCount);
}
rs.close();
statement.close();
con.close();
Benefits
- Select 1 (constant) does not require the creation of a temporary table
- Because of DB2's fast initial results, the code can begin to receive results and begin processing a row count before the actual query completes, therefore allowing the query to be intercepted once it has passed the allowable row limit.
So the faster option requires the creation of an SQL statment with constants, which I don't believe is a capability in Hibernate's HQL at this time. This is why we have the need to take the developer's original HQL query and extract the SQL from the HQL statement. And to even further add performance and processing speed, we want to send in an HQL statement and only transform particular statements into SQL. So in our case, we only need to transform the from and where clauses into SQL, and ignoring possible performance time to transform the select statement. Then we can utilize just the from and where clause to construct the constant query to check for row count on the result, then submitting the original HQL query if the results of the constant query are less than 1001.
I am really looking for Hibernate team feedback to provide any potential suggestions?
Thanks,
-jay
Login
FAQ
Search
