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