Error when using MatchAllDocsQuery

jacquipre · **Joined:** Thu Jan 06, 2011 6:19 am **Posts:** 25

Hi again,

when I try to search all my indexed entities (which are in total 100.000) I got an "Too many parameters" exception as you can see here:

Code:

org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
   at org.hibernate.loader.Loader.doList(Loader.java:2536)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
   at org.hibernate.loader.Loader.list(Loader.java:2271)
   at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
   at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
   at org.hibernate.search.query.hibernate.impl.CriteriaObjectsInitializer.initializeObjects(CriteriaObjectsInitializer.java:107)
   at org.hibernate.search.query.hibernate.impl.QueryLoader.executeLoad(QueryLoader.java:84)
   at org.hibernate.search.query.hibernate.impl.AbstractLoader.load(AbstractLoader.java:72)
   at org.hibernate.search.query.hibernate.impl.FullTextQueryImpl.list(FullTextQueryImpl.java:208)
   at org.hibernate.search.jpa.impl.FullTextQueryImpl.getResultList(FullTextQueryImpl.java:147)
       ...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
        ...

The SQL statement:

Code:

 select ... from My_Elements_Table  where (ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?...................................................)  or ID in(?,?,......) .... )  // a lot of question marks

My Query is build like this:

Code:

FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(getEntityManager());   
SearchFactory searchFactory = fullTextEntityManager.getSearchFactory();
QueryBuilder queryBuilder = searchFactory.buildQueryBuilder().forEntity(My_Elements.class).get();
FullTextQuery fullTextQuery = fullTextEntityManager.createFullTextQuery(queryBuilder.all().createQuery(), My_Elements.class);
List<My_Elements> myElements = fullTextQuery.getResultList();

Is there a problem when searching with hibernate search on such a large amount of data? What can I do to retrieve all results (except paging)?

Regards, jacquipre.

jacquipre · **Joined:** Thu Jan 06, 2011 6:19 am **Posts:** 25

This problem even occurs when using a boolean query that means when I search for a value on a special field like this:

Code:

Query is: "+status:valid" 
Expected result size: 25.000 elements

sanne.grinovero · **Posted:** Tue May 24, 2011 6:21 pm

Hi,
yes that's not considered good manners from you to want to load such an high amount of data, you might kill your database, or run out of memory :)

I'd suggest to use a ScrollableResultset, or read the chapter in the reference documentation about Customizing object initialization strategies
The advantage of using a scrollable result is that you might be able to process whatever you need to do the entities sequentially and discard them often, so you load them in batches which is good for the database and don't keep it all in memory which is good for your application.

http://docs.jboss.org/hibernate/stable/search/reference/en-US/html_single/

jacquipre · **Joined:** Thu Jan 06, 2011 6:19 am **Posts:** 25

Thanks for your answer.

ScrollableResults seems to be the way to go. But how can I scroll the result with the JPA FullTextQuery / FullTextEntityManager? I did'nt find any example for that. At the moment my query looks like this:

Code:

FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(getEntityManager());   
//... build the lucene query
org.hibernate.search.jpa.FullTextQuery fullTextQuery = fullTextEntityManager.createFullTextQuery(luceneQuery, My_Elements.class);
// cannot call a scroll method :(

sanne.grinovero · **Posted:** Wed May 25, 2011 12:26 pm

Code:

Session unwrappedSession = getEntityManager().unwrap(Session.class);
//... build the lucene query
FullTextSession fullTextSession = org.hibernate.search.Search.getFullTextSession( unwrappedSession );
org.hibernate.search.FullTextQuery fullTextQuery = fullTextEntityManager.createFullTextQuery( luceneQuery, My_Elements.class );
fullTextQuery.setFetchSize( 50 ).scroll(); // can still scroll :)

jacquipre · **Joined:** Thu Jan 06, 2011 6:19 am **Posts:** 25

I guess you mean:

Code:

org.hibernate.search.FullTextQuery fullTextQuery = fullTextSession.createFullTextQuery( luceneQuery, My_Elements.class );

instead of

Code:

org.hibernate.search.FullTextQuery fullTextQuery = fullTextEntityManager.createFullTextQuery( luceneQuery, My_Elements.class );

Thanks, it works! :-)

sanne.grinovero · **Posted:** Fri May 27, 2011 5:41 am

right :)