[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5905) HQL "where in" queries consume near 100% CPU - possibly for hours

[Marcel Stör (JIRA)]

HQL "where in" queries consume near 100% CPU - possibly for hours
-----------------------------------------------------------------

                 Key: HHH-5905
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5905
             Project: Hibernate Core
          Issue Type: Bug
    Affects Versions: 3.5.6
            Reporter: Marcel Stör
            Priority: Critical


[Claim]
The loop in ParameterParser.parse(String, ParameterParser$Recognizer) uses 100% CPU core per thread that executes it since it iterates over a string char by char and calling string operations in the loop.

[Java client]
Query query = getEntityManager().createQuery("select articleNumber from Article where articleNumber in (:articleNumbers)");
query.setParameter("articleNumbers", articleNumbers);
List<ArticleNumber> filteredArticleNumbers = (List<ArticleNumber>) query.getResultList();

[Stacktrace of involved classes/methods]
ParameterParser.parse(String, ParameterParser$Recognizer) line: 88   
ParamLocationRecognizer.parseLocations(String) line: 75   
HQLQueryPlan.buildParameterMetadata(ParameterTranslations, String) line: 290   
HQLQueryPlan.<init>(String, String, boolean, Map, SessionFactoryImplementor) line: 121   
HQLQueryPlan.<init>(String, boolean, Map, SessionFactoryImplementor) line: 80   
QueryPlanCache.getHQLQueryPlan(String, boolean, Map) line: 98   
SessionImpl(AbstractSessionImpl).getHQLQueryPlan(String, boolean) line: 156   
SessionImpl.list(String, QueryParameters) line: 1250   
QueryImpl.list() line: 102   
QueryImpl<X>.getResultList() line: 241

[Observations]
QueryImpl#list() calls SessionImpl#list(String, QueryParameters) passing the *expanded* query:
return getSession().list(expandParameterLists(namedParams), getQueryParameters(namedParams));

Hence, what is passed to SessionImpl#list(String, QueryParameters) in my case is something like:
select articleNumber from Article where articleNumber in (:articleNumbers0_, :articleNumbers1_, :articleNumbers2_, :articleNumbers3_, :articleNumbers4_, :articleNumbers5_, :articleNumbers6_,  

Now imagine how long that query string is when the "where in" query has a few thousand article numbers. org.hibernate.engine.query.ParameterParser.parse(String, Recognizer) then iterates over each character in the query string and calls StringHelper.firstIndexOfChar() for each named parameter. Even on fast servers this operation may easily take half an hour or more (mind you it's a blocking call!) if the expanded query string has a few million characters.
I don't know Hibernate well enough to propose a fix but "where in" with Hibernate is an absolute no-go as it is right now.
We had switch all our "where in" queries to native :-(

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira