准备语句SLOW的JPA（Hibernate）本地查询 [英] JPA (Hibernate) Native Query for Prepared Statement SLOW

查看：142
本文介绍了准备语句SLOW的JPA（Hibernate）本地查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

使用Hibernate 3.3.2GA后面的JPA（以及JBoss 5中包含的其余Hibernate包）会导致奇怪的性能问题。



我使用Native Query，并将SQL组装到预准备语句中。

  EntityManager em = getEntityManager（MY_DS）; 
 final Query query = em.createNativeQuery（fullSql，entity.getClass（））;

SQL有很多连接，但实际上非常基本，只有一个参数。如： 

  SELECT field1，field2，field3 FROM实体左连接entity2 on ... left连接entity3 on 
 WHERE stringId喜欢？

并且查询在MSSQL Studio下运行。



如果我添加 

  query.setParameter（0，ABC123％）;

查询将暂停9秒 

  2012-01-20 14:36:21  -  TRACE： -  AbstractBatcher.getPreparedStatement：（484）|准备声明
 2012-01-20 14:36:21  -  TRACE： -  StringType.nullSafeSet：（133）|绑定'ABC123％'参数：1 
 2012-01-20 14:36:30  - 调试： -  AbstractBatcher.logOpenResults：（382）|即将打开ResultSet（打开ResultSets：0，全局为0）

但是，如果我只是替换？ （使其不是Prepared Statement，而只是一个直接的SQL查询。）。 ，'ABC123％'）;

查询将在少一秒内完成。 / p>

为了防止注入攻击，我真的更希望为我们准备一个Prepared Statement（参数输入是从用户数据中提取的）。

追踪代码中的缓慢点，我深入到jtds-1.2.2包中，这条线似乎是SharedSocket line 841getIn（）。readFully（hdrBuf）;没有什么明显的虽然... 

  private byte [] readPacket（byte buffer []）
 throws IOException {
 // 
 //读取头文件的其余部分
 try {
 getIn（）。readFully（hdrBuf）; 
} catch（EOFException e）{
 throw new IOException （DB server closed connection。）; 
}

... 

  at net.sourceforge.jtds.jdbc.SharedSocket.readPacket（SharedSocket.java:841）
 at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket（SharedSocket.java:722）
 at net.sourceforge.jtds.jdbc.ResponseStream.getPacket（ResponseStream.java:466）
 at net .sourceforge.jtds.jdbc.ResponseStream.read（ResponseStream.java:103）
 at net.sourceforge.jtds.jdbc.ResponseStream.peek（ResponseStream.java:88）
 at net.sourceforge.jtds .jdbc.TdsCore.wait（TdsCore.java:3928）
 at net.sourceforge.jtds.jdbc.TdsCore.executeSQL（TdsCore.java:1045）
 at net.sourceforge.jtds.jdbc.TdsCore .microsoftPrepare（TdsCore.java:1178）
 at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL（ConnectionJDBC2.java:657）
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery（JtdsPreparedStatement .java：776）
 at org.hibernate.jdbc.AbstractBatcher.getResultSet（AbstractBatcher.java:208）
 at org.hibernate.loader.Loader.getResultSet（Loa der.java:1808）
在org.hibernate.loader.Loader.doQuery（Loader.java:697）
在org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections（Loader.java:259）
在org.hibernate.loader.Loader.doList（Loader.java:2228）
在org.hibernate.loader.Loader.listIgnoreQueryCache（Loader.java:2125）
在org.hibernate.loader .Loader.list（Loader.java:2120）
在org.hibernate.loader.custom.CustomLoader.list（CustomLoader.java:312）
 at org.hibernate.impl.SessionImpl.listCustomQuery（SessionImpl .java：1722）
 at org.hibernate.impl.AbstractSessionImpl.list（AbstractSessionImpl.java:165）
 at org.hibernate.impl.SQLQueryImpl.list（SQLQueryImpl.java:175）
 at org.hibernate.ejb.QueryImpl.getResultList（QueryImpl.java:67）


解决方案



问题在于如何解决这个问题。 JTDS驱动程序发送参数s向MSSQL投诉。显然，Java会尝试默认发送Unicode参数，MSSQL会将其转换为Ascii。为什么需要9秒钟，我不知道。



罗特引用了这里的内容，但没有什么能够帮助我，直到我能够确定它是一个问题与驱动程序MSSQL连接。



这个链接是有帮助的：



[http：//server.pramati

这是使用Microsoft驱动程序的字符串。

  jdbc：sqlserver：// localhost\SQLEXPRESS; 
 DatabaseName = TESTDB; 
 sendStringParametersAsUnicode = false

您只需将sendStringParametersAsUnicode = false传递给您的驱动程序URL设置，你很好。


Having strange performance issue using Hibernate 3.3.2GA behind JPA (and the rest of the Hibernate packages included in JBoss 5.)

I'm using Native Query, and assembling SQL into a prepared statement.

EntityManager em = getEntityManager(MY_DS);
final Query query = em.createNativeQuery(fullSql, entity.getClass());

The SQL has a lot of joins, but is actually very basic, with a single parameter. Like:

SELECT field1, field2, field3 FROM entity left join entity2 on... left join entity3 on
WHERE stringId like ?

and the query runs in under a second on MSSQL Studio.

If I add

query.setParameter(0, "ABC123%");

The query will pause for 9 seconds

2012-01-20 14:36:21 - TRACE: - AbstractBatcher.getPreparedStatement:(484) | preparing statement
2012-01-20 14:36:21 - TRACE: - StringType.nullSafeSet:(133) | binding 'ABC123%' to parameter: 1
2012-01-20 14:36:30 - DEBUG: - AbstractBatcher.logOpenResults:(382) | about to open ResultSet (open ResultSets: 0, globally: 0)

However, if I just replace the "?" with the value (making it not a Prepared Statement, but just a straight SQL query.

fullSql = fullSql.replace("?", "'ABC123%'");

the query will complete in less that a second.

I would really prefer to us a Prepared Statement (the input for the parameters is being extracted from user data) to prevent injection attacks.

Tracing down the slow point in the code, I arrived deep within the jtds-1.2.2 package. The offending line seems to be SharedSocket line 841 "getIn().readFully(hdrBuf);" Nothing really obvious there though...

private byte[] readPacket(byte buffer[])
        throws IOException {
    //
    // Read rest of header
    try {
        getIn().readFully(hdrBuf);
    } catch (EOFException e) {
        throw new IOException("DB server closed connection.");
    }

Arrived to through this stack...

  at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:841)
  at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:722)
  at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:466)
  at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:103)
  at net.sourceforge.jtds.jdbc.ResponseStream.peek(ResponseStream.java:88)
  at net.sourceforge.jtds.jdbc.TdsCore.wait(TdsCore.java:3928)
  at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1045)
  at net.sourceforge.jtds.jdbc.TdsCore.microsoftPrepare(TdsCore.java:1178)
  at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.java:657)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
  at org.hibernate.loader.Loader.doQuery(Loader.java:697)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
  at org.hibernate.loader.Loader.doList(Loader.java:2228)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
  at org.hibernate.loader.Loader.list(Loader.java:2120)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
  at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)

解决方案

I'll leave this question and answer out here in case anyone has the same issue in the future.

The issue is in the way the JTDS drivers send the parameter strings to MSSQL. Apparently Java will attempt to send the parameters Unicode by default, and MSSQL will translate it to Ascii. Why that takes 9 seconds, I do not know.

Lot's of references to this out there, but nothing that helped my till I was able to isolate that it was an issue with the driver to MSSQL connection.

This link was helpful:

[http://server.pramati.com/blog/2010/06/02/perfissues-jdbcdrivers-mssqlserver/]

This is the string using the Microsoft driver.

jdbc:sqlserver://localhost\SQLEXPRESS;
  DatabaseName=TESTDB;
  sendStringParametersAsUnicode=false

You just need to get the sendStringParametersAsUnicode=false passed to your driver URL setup and you are good.

这篇关于准备语句SLOW的JPA（Hibernate）本地查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆