title: Hibernate is 1000 times slower than sql query
body: <p>I have this setup</p>
  
  <pre><code>@Table(name ="A")
  EntityA {
      Long ID;
      List&lt;EntityB&gt; children;
  }
  
  @Table(name ="B")
  EntityB {
      Long ID;
      EntityA parent;
      EntityC grandchild;
  }
  
  @Table(name ="C")
  EntityC {
      Long ID;
  }
  </code></pre>
  
  <p>The SQL query is this (I omitted irrelevant details):</p>
  
  <pre><code>select top 300 from A where ... and ID in (select parent from B where ... and grandchild in (select ID from C where ...)) order by ...
  </code></pre>
  
  <p>The sql query in direct database or through Hibernate (3.5) SQL runs 1000 faster than using Criteria or HQL to express this.</p>
  
  <p>The SQL generated is <strong>identical</strong> from HQL and Criteria and the SQL I posted there.</p>
  
  <p>[EDIT]: Correction - the sql was not identical. I didn't try the Hibernate style parameter setting on the management studio side because I did not realize this until later - see my answer.</p>
  
  <p>If I separate out the subqueries into separate queries, then it is fast again.</p>
  
  <p>I tried </p>
  
  <ul>
  <li>removing all mappings of child, parent, ect.. and just use Long Id references - same thing, so its not a fetching, lazy,eager related. </li>
  <li>using joins instead of subqueries, and got the same slow behaviour with all combinations of fetching and loading.</li>
  <li>setting a projection on ID instead of retrieving entities, so there is no object conversion - still slow</li>
  </ul>
  
  <p>I looked at Hibernate code and it is doing something astounding. It has a loop through all 300 results that end up hitting the database.</p>
  
  <pre><code>private List doQuery(
          final SessionImplementor session,
          final QueryParameters queryParameters,
          final boolean returnProxies) throws SQLException, HibernateException {
  
      final RowSelection selection = queryParameters.getRowSelection();
      final int maxRows = hasMaxRows( selection ) ?
              selection.getMaxRows().intValue() :
              Integer.MAX_VALUE;
  
      final int entitySpan = getEntityPersisters().length;
  
      final ArrayList hydratedObjects = entitySpan == 0 ? null : new ArrayList( entitySpan * 10 );
      final PreparedStatement st = prepareQueryStatement( queryParameters, false, session );
      final ResultSet rs = getResultSet( st, queryParameters.hasAutoDiscoverScalarTypes(), queryParameters.isCallable(), selection, session );
  
  // would be great to move all this below here into another method that could also be used
  // from the new scrolling stuff.
  //
  // Would need to change the way the max-row stuff is handled (i.e. behind an interface) so
  // that I could do the control breaking at the means to know when to stop
  
      final EntityKey optionalObjectKey = getOptionalObjectKey( queryParameters, session );
      final LockMode[] lockModesArray = getLockModes( queryParameters.getLockOptions() );
      final boolean createSubselects = isSubselectLoadingEnabled();
      final List subselectResultKeys = createSubselects ? new ArrayList() : null;
      final List results = new ArrayList();
  
      try {
  
          handleEmptyCollections( queryParameters.getCollectionKeys(), rs, session );
  
          EntityKey[] keys = new EntityKey[entitySpan]; //we can reuse it for each row
  
          if ( log.isTraceEnabled() ) log.trace( "processing result set" );
  
          int count;
          for ( count = 0; count &lt; maxRows &amp;&amp; rs.next(); count++ ) {
  
              if ( log.isTraceEnabled() ) log.debug("result set row: " + count);
  
              Object result = getRowFromResultSet( 
                      rs,
                      session,
                      queryParameters,
                      lockModesArray,
                      optionalObjectKey,
                      hydratedObjects,
                      keys,
                      returnProxies 
              );
              results.add( result );
  
              if ( createSubselects ) {
                  subselectResultKeys.add(keys);
                  keys = new EntityKey[entitySpan]; //can't reuse in this case
              }
  
          }
  
          if ( log.isTraceEnabled() ) {
              log.trace( "done processing result set (" + count + " rows)" );
          }
  
      }
      finally {
          session.getBatcher().closeQueryStatement( st, rs );
      }
  
      initializeEntitiesAndCollections( hydratedObjects, rs, session, queryParameters.isReadOnly( session ) );
  
      if ( createSubselects ) createSubselects( subselectResultKeys, queryParameters, session );
  
      return results; //getResultList(results);
  
  }
  </code></pre>
  
  <p>In this code</p>
  
  <pre><code>final ResultSet rs = getResultSet( st, queryParameters.hasAutoDiscoverScalarTypes(), queryParameters.isCallable(), selection, session );
  </code></pre>
  
  <p>it hits the database with the full SQL, but there are no results collected anywhere.</p>
  
  <p>Then it proceeds to go through this loop</p>
  
  <pre><code>for ( count = 0; count &lt; maxRows &amp;&amp; rs.next(); count++ ) {
  </code></pre>
  
  <p>Where for every one of the expected 300 results, it ends up hitting the database to get the actual result.</p>
  
  <p>This seems insane, since it should already have all the results after 1 query. Hibernate logs <strong>do not show</strong> any additional SQL being issued during all that time.</p>
  
  <p>Anyone have any insight? The only option I have is to go to native SQL query through Hibernate.</p>