Hibernate比sql查询慢1000倍

I have this setup

我有这个设置

@Table(name ="A")
EntityA {
    Long ID;
    List<EntityB> children;
}

@Table(name ="B")
EntityB {
    Long ID;
    EntityA parent;
    EntityC grandchild;
}

@Table(name ="C")
EntityC {
    Long ID;
}

The SQL query is this (I omitted irrelevant details):

SQL查询就是这个(我省略了不相关的细节):

select top 300 from A where ... and ID in (select parent from B where ... and grandchild in (select ID from C where ...)) order by ...

The sql query in direct database or through Hibernate (3.5) SQL runs 1000 faster than using Criteria or HQL to express this.

直接数据库中的sql查询或通过Hibernate(3.5)SQL运行比使用Criteria或HQL表达快1000。

The SQL generated is identical from HQL and Criteria and the SQL I posted there.

生成的SQL与HQL和Criteria以及我在那里发布的SQL完全相同。

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

[编辑]:更正 - sql不完全相同。我没有在管理工作室方面尝试Hibernate样式参数设置,因为直到后来我才意识到这一点 - 请参阅我的回答。

If I separate out the subqueries into separate queries, then it is fast again.

如果我将子查询分成单独的查询,那么它再次快速。

I tried

• removing all mappings of child, parent, ect.. and just use Long Id references - same thing, so its not a fetching, lazy,eager related.

删除子,父,等的所有映射..并且只使用Long Id引用 - 同样的事情,所以它不是一个提取,懒惰,渴望相关。

• using joins instead of subqueries, and got the same slow behaviour with all combinations of fetching and loading.

使用连接而不是子查询,并获得与获取和加载的所有组合相同的缓慢行为。

• setting a projection on ID instead of retrieving entities, so there is no object conversion - still slow

在ID上设置投影而不是检索实体,因此没有对象转换 - 仍然很慢

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.

我查看了Hibernate代码,它正在做一些惊人的事情。它有一个循环遍历所有300个结果,最终命中数据库。

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 < maxRows && 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);

}

In this code

在这段代码中

final ResultSet rs = getResultSet( st, queryParameters.hasAutoDiscoverScalarTypes(), queryParameters.isCallable(), selection, session );

it hits the database with the full SQL, but there are no results collected anywhere.

它使用完整的SQL命中数据库,但在任何地方都没有收集结果。

Then it proceeds to go through this loop

然后它继续经历这个循环

for ( count = 0; count < maxRows && rs.next(); count++ ) {

Where for every one of the expected 300 results, it ends up hitting the database to get the actual result.

对于预期的300个结果中的每一个,它最终会到达数据库以获得实际结果。

This seems insane, since it should already have all the results after 1 query. Hibernate logs do not show any additional SQL being issued during all that time.

这看起来很疯狂,因为在1次查询后它应该已经有了所有结果。 Hibernate日志不会显示在此期间发出的任何其他SQL。

Anyone have any insight? The only option I have is to go to native SQL query through Hibernate.

任何人有任何见解?我唯一的选择是通过Hibernate转到本机SQL查询。

1 个解决方案

#1

I finally managed to get to the bottom of this. The problem was being caused by Hibernate setting the parameters separately from the actual SQL query that involved subqueries. So native SQL or not, the performance will be slow if this is done. For example this will be slow:

我终于成功了解了这一点。问题是由Hibernate将参数与涉及子查询的实际SQL查询分开设置引起的。如此原生SQL,如果这样做,性能将会很慢。例如,这将是缓慢的:

String sql = some sql that has named parameter = :value
SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.setParameter ("value", someValue);
List<Object[]> list = (List<Object[]>)sqlQuery.list();

And this will be fast

这将是快速的

String sql = some native sql where parameter = 'actualValue'
SQLQuery sqlQuery = session.createSQLQuery(sql);
List<Object[]> list = (List<Object[]>)sqlQuery.list();

It seems that for some reason with letting Hibernate take care of the parameters it ends up getting stuck in the resultSet fetching. This is probably because the underlying query on the database is taking much longer being parameterized. I ended up writing the equivalent of Hibernate Criteria and Restrictions code that sets the parameters directly as above.

似乎由于某些原因让Hibernate处理参数,它最终会被卡在resultSet中。这可能是因为数据库的基础查询花费了更长的时间进行参数化。我最终编写了相当于Hibernate Criteria和Restrictions代码的代码,它直接如上所述设置参数。