Java 简单的休眠查询返回非常缓慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6609645/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simple hibernate query returning very slowly
提问by Trym
I have the following hibernate query:
我有以下休眠查询：
Query query = session.createQuery("from MyHibernateClass");
List<MyHibernateClass> result = query.list();// executes in 7000ms
When logging the sql being executed in MySQL I see
记录在 MySQL 中执行的 sql 时，我看到
select
myhibernat0_.myFirstColumn as myfirstcolumn92_,
myhibernat0_.mySecondColumn as mysecondcolumn92_,
myhibernat0_.mythirdcolumn as mythirdcolumn92_,
myhibernat0_.myFourthColumn as myfourthcolumn92_
from MyHibernateClass myhibernat0_
where (1=1);
When measurering the java code in the jvm on a small dataset of 3500 rows in MyHibernateClass database table this takes about 7000ms.
在 MyHibernateClass 数据库表中 3500 行的小数据集上测量 jvm 中的 java 代码时，这需要大约 7000 毫秒。
If I on the otherhand uses direct jdbc as follows:
如果我另一方面使用直接 jdbc 如下：
Statement statement = session.connection().createStatement();
ResultSet rs = statement.executeQuery("select * from MyHibernateClass");// 7ms
List<MyHibernateClass> result = convert(rs);// executes in 20ms
I see the same sql going into the database but now the time spend in the java code in the jvm is 7ms.
我看到同样的 sql 进入数据库，但现在在 jvm 中的 java 代码中花费的时间是 7ms。
The MyHibernateClass is a simple java bean class with getters and setters, I use no special resulttransformers as can be seen in the example. I only need a read-only instance of the class, and it doesn't need to be attached to the hibernate session.
MyHibernateClass 是一个带有 getter 和 setter 的简单 java bean 类，我没有使用特殊的 resulttransformers，如示例中所示。我只需要该类的一个只读实例，并且不需要将其附加到休眠会话。
I would rather like to use the hibernate version but cannot accept the execution times.
我宁愿使用休眠版本，但不能接受执行时间。
Added information: After adding hibernate logging I see
添加信息：添加休眠日志记录后，我看到
[2011-07-07 14:26:26,643]DEBUG [main] [logid: ] -
org.hibernate.jdbc.AbstractBatcher.logOpenResults(AbstractBatcher.java:426) -
about to open ResultSet (open ResultSets: 0, globally: 0)
followed by 3500 of the following log statements
后跟 3500 条以下日志语句
[2011-07-07 14:26:26,649]DEBUG [main] [logid: ] -
org.hibernate.loader.Loader.getRow(Loader.java:1197) -
result row: EntityKey[com.mycom.MyHibernateClass#1]
followed by 3500 log statements like
后跟 3500 条日志语句，例如
[2011-07-07 14:27:06,789]DEBUG [main] [logid: ] -
org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:130) -
resolving associations for [com.mycom.MyHibernateClass#1]
[2011-07-07 14:27:06,792]DEBUG [main] [logid: ] -
org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:226) -
done materializing entity [com.mycom.MyHibernateClass#1]
What does this mean?
这是什么意思？
What is Hibernate doing in the first implementation, how can I find out?
Hibernate 在第一个实现中做了什么，我怎么知道？
回答by maple_shaft
If you utilize Log4j in your application you can set a variety of different logging options specific to Hibernate to get a better picture of what is going on behind the scenes in Hibernate.
如果您在应用程序中使用 Log4j，您可以设置各种特定于 Hibernate 的不同日志记录选项，以更好地了解 Hibernate 幕后发生的事情。
My guess is that this is the typical initial load time that occurs when first calling an HQL query in an application. Subsequent HQL queries should be noticeably and considerably faster after this first one.
我的猜测是，这是在应用程序中首次调用 HQL 查询时发生的典型初始加载时间。在第一个查询之后，后续的 HQL 查询应该会明显更快。
回答by maple_shaft
Based on the new information I felt I should provide another answer. The difference looks like that you have a one-to-many association specified for a List or Set property in your bean.
根据新信息，我觉得我应该提供另一个答案。区别在于您为 bean 中的 List 或 Set 属性指定了一对多关联。
You are probably specifying that lazy=falsewhich will turn off lazy loading. With lazy loading turned off it will fetch every associated record for every MyHibernateClassentity and this is why it is taking so long to execute.
您可能正在指定lazy=false将关闭延迟加载的内容。关闭延迟加载后，它将获取每个MyHibernateClass实体的每个关联记录，这就是执行时间如此长的原因。
Try setting lazy=trueand this will perform much faster and then only retrieve the associated entities when explicitly requesting them from the entity.
尝试设置lazy=true，这将执行得更快，然后仅在从实体显式请求它们时检索关联的实体。
回答by Trym
Adding a constructor with all attributes of the class did the trick, now the execution times are 70ms for the hibernate query. Previously the class only had a default constructor without arguments and a constructor with the entity id argument.
添加具有类的所有属性的构造函数可以解决问题，现在休眠查询的执行时间为 70 毫秒。以前该类只有一个没有参数的默认构造函数和一个带有实体 id 参数的构造函数。
回答by jamb
I know this thread is old, but to update I ran into the same problem but with SQL Server and it turns out to be that SQL being printed by Hibernate and SQL Sent using the driver is different. Using MSSQL Driver by default sends the queries as stored procedures as RPC calls it's because the driver tries to optimize the query plan for MSSQL Standards , so it sends the queries something like
我知道这个线程很旧，但是要更新我遇到了同样的问题，但是使用 SQL Server，结果是 Hibernate 打印的 SQL 和使用驱动程序发送的 SQL 是不同的。默认情况下，使用 MSSQL 驱动程序将查询作为 RPC 调用的存储过程发送，这是因为驱动程序试图优化 MSSQL 标准的查询计划，因此它发送的查询类似于
Hibernate Query:
休眠查询：
select c.col1,c.col2 from customer c where c.name like @param1 and c.country like @param2
Actual Driver Sent Query:
实际驱动程序发送查询：
@param1=somevalue, @param2=somevalue
declar sp ....
select c.col1,c.col2 from customer c where c.name like @param1 and c.country like @param2
go
Note: This Query I got through SQL Profiler Tool directly listening on DB
注意：这个查询是我通过 SQL Profiler Tool 直接监听 DB 得到的
It turns out to be that sp_exec optimizations on the MSSQL tend to produce good Query plans that's get cached, but this would result in 'parameter sniffing' to know more about this problem read here...
事实证明，MSSQL 上的 sp_exec 优化往往会产生被缓存的良好查询计划，但这会导致“参数嗅探”以了解有关此问题的更多信息，请阅读此处...
So to overcome this I had following options:
所以为了克服这个问题，我有以下选择：
Change my HQL to native Queries and add OPTION RECOMPILE FOR SOME PARAM
Use Direct query values instead of prepared statements so there will be no translation for param values and queries will not be modified as Stored Procedures by the Driver
Change the driver settings to not send the stored procedures (this is still bad because now the query plans in MSSQL server will be specific to this query, this is same as Option:2 but outside the code)
将我的 HQL 更改为本机查询并添加 OPTION RECOMPILE FOR SOME PARAM
使用直接查询值而不是准备好的语句，因此不会对参数值进行转换，并且查询不会被驱动程序修改为存储过程
将驱动程序设置更改为不发送存储过程（这仍然很糟糕，因为现在 MSSQL 服务器中的查询计划将特定于此查询，这与 Option:2 相同，但在代码之外）
I didn't want to use OPTION 1 & 2 since that eliminates the whole purpose of using ORM Frameworks and I end up using OPTION 3 for now
我不想使用 OPTION 1 & 2 因为这消除了使用 ORM 框架的全部目的，我现在最终使用了 OPTION 3
So I changed the JDBC URL to send option prepareStatement=false
所以我更改了 JDBC URL 以发送选项 prepareStatement=false
After setting this I had one more problem the query being sent like
设置后，我还有一个问题，查询被发送
Select * from customer c where c.name like **N**'somename' and c.country=**N**'somevalue'
Here there is a prefix before the values which states that to convert the encoding scheme , so I disable the JDBC url to sendUnicode = false
这里在值之前有一个前缀，表示要转换编码方案，所以我禁用了 JDBC url 以发送Unicode = false
This all I did in JTDS driver options.. As far as I am concerned now the application is up and running fast. I have also introduced second level caches to cache it for some time..
这就是我在 JTDS 驱动程序选项中所做的一切。就我而言，现在应用程序已启动并快速运行。我还引入了二级缓存来缓存它一段时间..
Hope this helps for someone, if you have any good suggestion please let me know.
希望这对某人有所帮助，如果您有任何好的建议，请告诉我。
回答by Jeffrey Hawkins
I know this is an old question but here is what fixed it for me...
我知道这是一个老问题，但这是为我解决的问题......
In your hibernate.cfg.xml make sure you have the correct !DOCTYPE... it should be as follows:
在您的 hibernate.cfg.xml 中，确保您有正确的 !DOCTYPE... 应该如下所示：
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
回答by gregd
I had an incident where my application was always using every row in the result set of a query. I found a 40-fold increase in speed by setting my fetch size using the setFetchSize method below. (The performance improvement includes the addition of the count query.)
我遇到了一个事件，我的应用程序总是使用查询结果集中的每一行。通过使用下面的 setFetchSize 方法设置我的提取大小，我发现速度提高了 40 倍。（性能改进包括添加计数查询。）
Long count = getStoreCount(customerId);
Query query = session.getNamedQuery("hqlGetStoresByCustomerId")
.setString("i_customerid",customerId)
.setFetchSize(count.intValue());
Be careful while doing this; my data set had about 100 rows, and it was scoped to a the life of a web request. If you have larger data sets, you will be eating Java Heap for the duration of the existence of that data, prior to returning it to the Java Heap.
这样做时要小心；我的数据集大约有 100 行，它的范围是 Web 请求的生命周期。如果您有更大的数据集，在将数据返回到 Java 堆之前，您将在该数据存在的持续时间内消耗 Java 堆。
回答by Hendra Anggrian
It took me 10 seconds to execute a simple select all query before I found out that DOCTYPE tag is written wrongly in hibernate.cfg.xmland *mapping object*.hbm.class
我花了10秒执行一个简单的选择所有查询之前，我发现DOCTYPE标签被误写成hibernate.cfg.xml和*mapping object*.hbm.class
Make sure that hibernate.cfg.xmlstart with
确保hibernate.cfg.xml以
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
And mapping xml.class with
并将 xml.class 与
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
Now it took me 1-2 seconds to execute any queries.
现在我花了 1-2 秒来执行任何查询。
回答by Raman
Anyone else who is facing a similar issue with SQL Server can use sendStringParametersAsUnicode=falsein the JDBC Query String as shown in this answer:
遇到类似 SQL Server 问题的任何其他人都可以sendStringParametersAsUnicode=false在 JDBC 查询字符串中使用，如本答案所示：
JPA (Hibernate) Native Query for Prepared Statement SLOW
If you're not using Unicode for your prepared statement parameters and want to utilize the index on the varcharfield which you're using as a parameter for the prepared statement, this can help.
如果您没有将 Unicode 用于准备好的语句参数，并且想要利用您用作准备好的语句的参数的varchar字段上的索引，这会有所帮助。
