Our search form allows users to specify the size of their results to be either 25, 50, 100, or 250. We have noticed for this particular implementation that the response for 25/50 is within acceptable application response times; however, when selecting 100 or 250 they times are exponentially slower.
FullTextQuery ftQuery = ftSession.createFullTextQuery(query, Item.class);
int totalHits = ftQuery.getResultSize();
List<?> results = ftQuery.list();
When adding debugging, to the above code, it's obvious that the issue is during the call to list() on the query and nothing else.
The query generated looks similar to the following:
SELECT (bunch of fields)
FROM items this_
WHERE (( (this_.ITEM_ID = ? AND this_.PLANT_ID = ?)
OR (this_.ITEM_ID = ? AND this_.PLANT_ID = ?)
As the number of bind parameters increase, the speed of returning the results degrades exponentially. One DBA claims it is due to all the OR operators that get generated for the database fetch in the WHERE clause, but I've ran the generated SQL select statement from within Toad but without bind parameters and it runs in under 100ms.
I need to determine what is the slow down with list() when the page size is anything greater than 50.
Hibernate 4.2.2, SQL Server 2008