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

[Strong Liu (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5905?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Strong Liu updated HHH-5905:
----------------------------

    Affects Version/s: 4.0.0.CR7

> 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, 4.0.0.CR7
>            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.
For more information on JIRA, see: http://www.atlassian.com/software/jira