We're updating the issue view to help you get more done. 

FullTextQuery with max results of 250 takes considerable time to load entities

Description

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);
ftQuery.setFirstResult((pagNumber-1)*pageSize).setMaxResult(pageSize);
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 = ?)
OR ...
))

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.

Environment

Hibernate 4.2.2, SQL Server 2008

Status

Assignee

Unassigned

Reporter

Chris Cranford

Labels

None

Suitable for new contributors

None

Pull Request

None

Feedback Requested

None

Components

Affects versions

4.3.0.Final

Priority

Major