Hibernate比sql查询慢1000倍 [英] Hibernate is 1000 times slower than sql query
问题描述
@Table（name =A）
EntityA {
长ID;
列表< EntityB>儿童;
@Table（name =B）
EntityB {
Long ID;
EntityA父母;
EntityC孙子;
@Table（name =C）
EntityC {
Long ID;
$ b $ p
$ b SQL查询是这样的（我忽略了不相关的细节）：
选择top 300从A where ...和ID in（从B选择父级，其中...和grandchild在（从C中选择ID where ...））order by ...
直接数据库或通过Hibernate（3.5 ）SQL的运行速度比使用Criteria或HQL快1000倍。
生成的SQL与HQL和Criteria的相同，以及我发布的SQL在那里。
：更正 - sql不完全相同。我没有尝试管理工作室方面的Hibernate风格参数设置，因为直到后来我才意识到这一点 - 请参阅我的答案。
如果我将子查询分成单独的查询，然后它又快了。
我试过了
- 孩子，父母等的映射......并且仅仅使用长ID引用 - 同样的事情，所以它不是一个抓取，懒惰，热切相关的东西。
- 使用连接而不是子查询，并且获取与获取和加载的所有组合相同的缓慢行为。
- 在ID上设置投影而不是检索实体，所以没有对象转换 - 仍然很慢
我研究了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.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）;
//将把下面的所有内容移到另一个方法中，这个方法也可以在新的滚动内容中使用
//。
//
//需要改变最大行内容的处理方式（即在一个接口后面），所以
//我可以通过手段来控制断开的知识何时停止
final EntityKey optionalObjectKey = getOptionalObjectKey（queryParameters，session）;
final LockMode [] lockModesArray = getLockModes（queryParameters.getLockOptions（））;
final boolean createSubselects = isSubselectLoadingEnabled（）;
final列表subselectResultKeys = createSubselects？ new ArrayList（）：null;
final列表results = new ArrayList（）;
尝试{
handleEmptyCollections（queryParameters.getCollectionKeys（），rs，session）;
EntityKey [] keys = new EntityKey [entitySpan]; //我们可以为每一行重用它
if（log.isTraceEnabled（））log.trace（processing result set）;
int count; （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，
键，
returnProxies
）;
results.add（result）;
if（createSubselects）{
subselectResultKeys.add（keys）;
keys = new EntityKey [entitySpan]; //不能在这种情况下重用
}
if（log.isTraceEnabled（））{
log.trace（done处理结果集（+ count +行））;
}
}
finally {
session.getBatcher（）。closeQueryStatement（st，rs）;
}
initializeEntitiesAndCollections（hydratedObjects，rs，session，queryParameters.isReadOnly（session））;
if（createSubselects）createSubselects（subselectResultKeys，queryParameters，session）;
返回结果; // getResultList（结果）;
在这段代码中
final ResultSet rs = getResultSet（st，queryParameters.hasAutoDiscoverScalarTypes（），queryParameters.isCallable（），selection，session）;
它使用完整的SQL命中数据库，但没有收集到任何结果。
然后继续循环
for（count = 0; count < maxRows&& rs.next（）; count ++）{
预期的300个结果，它最终击中数据库，以获得实际结果。
这似乎很疯狂，因为它应该已经有1个查询后的所有结果。 Hibernate日志不显示在任何时候都会发布任何额外的SQL。
任何人都有洞察力吗？我唯一的选择是通过Hibernate去原生SQL查询。
解决方案我终于设法达到了这个底部。这个问题是由Hibernate设置参数与涉及子查询的实际SQL查询分开的。所以原生的SQL或不，性能会很慢，如果这样做。例如，这会很慢：
字符串sql =已命名为参数=的某些sql
SQLQuery sqlQuery =执行Session.createSQLQuery（SQL）;
sqlQuery.setParameter（value，someValue）;
List< Object []> list =（List< Object []>）sqlQuery.list（）;
而且这个速度很快
<$ p $字符串sql =一些本地sql其中parameter ='actualValue'
SQLQuery sqlQuery = session.createSQLQuery（sql）;
List< Object []> list =（List< Object []>）sqlQuery.list（）;
看起来由于某种原因让Hibernate处理参数，它最终陷入了resultSet抓取。这可能是因为数据库上的基础查询需要更长的参数化时间。我最终编写了与上面直接设置参数的Hibernate Criteria and Restrictions代码相同的代码。
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):
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.
The SQL generated is identical from HQL and Criteria and the SQL I posted there.
[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.
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.
- 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
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.
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.
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.
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.
Anyone have any insight? The only option I have is to go to native SQL query through Hibernate.
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:
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比sql查询慢1000倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
