[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-766) Big Performance problem with setParameterList()

[Jacob Poder Kristensen (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-766?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_30466 ] 

Jacob Poder Kristensen commented on HHH-766:
--------------------------------------------

Even though it might be a bit controversial to use a huge number of constants in the WHERE IN clause, the complexity of the used utility, StringHelper. firstIndexOfChar(String sqlString, String string, int startindex), is unnecessarily high.
With the current implementation, 'sqlString' is scanned for all of the characters in 'string', even if a match is found early:

  public static int firstIndexOfChar(String sqlString, String string, int startindex) {
    int matchAt = -1;
    for ( int i = 0; i < string.length(); i++ ) {
      int curMatch = sqlString.indexOf( string.charAt( i ), startindex );
      if ( curMatch >= 0 ) {
        if ( matchAt == -1 ) { // first time we find match!
          matchAt = curMatch;
        }
        else {
          matchAt = Math.min( matchAt, curMatch );
        }
      }
    }
    return matchAt;
  }

Matching one character at a time from 'sqlString' against all those in 'string', has a much better chance for early exit:
  /**
   * Starting from <code>startindex</code>, find the first index in <code>sqlString</code>
   * where any one of the characters in <code>string</code> matches.
   * @param sqlString the String to look in.
   * @param string the characters to find one of.
   * @param startindex the zero based string index to start looking from.
   * @return the index of the first matching character or -1 if not found.
   */
     public static int firstIndexOfChar(final String sqlString, final String string, final int startindex) {
         final int length = string.length();
         final char[] chars = new char[length];
         string.getChars(0, length, chars, 0);
         for ( int i = startindex; i < sqlString.length(); ++i ) {
             char c = sqlString.charAt(i);
             for ( int j = 0; j < length; ++j ) {
                 if(c == chars[j]) {
                     return i;
                 }
             }
         }
         return -1;
     }

So, even if you are happy with the performance of setParameterList, the above is an improvement to firstIndexOfChar in general.


> Big Performance problem with setParameterList()
> -----------------------------------------------
>
>                 Key: HHH-766
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-766
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.0 final
>         Environment: Windows
>            Reporter: Teva Lautier
>            Assignee: Max Rydahl Andersen
>
> Here is a simple code that test setParameterList() with big arrays : 
>     int nb=16; 
>     for (int j = 0; j < 50; j++) { 
>       idCats = new Integer[nb]; 
>       for (int i = 0; i < idCats.length; i++) { 
>         idCats[i] = new Integer(i + 1); 
>       } 
>       long deb = System.currentTimeMillis(); 
>       SQLQuery query = getSession().createSQLQuery( 
>   "select {R.*} from Cats R where R.idCat in (:cats) "); 
>       query.addEntity("R", Cat.class); 
>  
>       query.setParameterList("cats",idCats); 
>       query.list() 
>       System.out.println("nb doc:"+nb 
>             +"  time="+(System.currentTimeMillis()-deb)); 
>       nb*=2; 
>     } 
> The table Cats contains only 2 lines!
> This gives this  :
> ... 
> nb docs:64               time=16 
> nb docs:128              time=62 
> nb docs:256              time=32 
> nb docs:512              time=281 
> nb docs:1024             time=609 
> nb docs:2048             time=2219 
> nb docs:4096             time=8938 
> nb docs:8192             time=34140 
> And I stop to wait for nb docs=16384 after 6 minutes ! The time is not lineare!
> I saw that it is method SQLQueryParser.substituteParams that takes so much time, not the query. 
> I prevent this performance problem by splitting arrays with length nb , and makes nbDoc/nb queries
> I tried with nb=200 and nb=500 and nb=50. The best performance I have is when nb=50
> I tried to use simple hand made statement for this query and it is 3 to 10 faster than with hibernate, and time is lineare
> Good luck.

-- 
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