为什么 Microsoft SQL Server 2012 查询在 JDBC 4.0 中需要几分钟，而在 Management Studio 中需要几秒钟? [英] Why does Microsoft SQL Server 2012 query take minutes over JDBC 4.0 but second(s) in Management Studio?

问题描述

在检索相对较大的ResultSet 从远程 Microsoft SQL Server 2012 到使用 Microsoft JDBC Driver 4.0 的 Java 客户端.

I am dealing with what is apparently a performance issue while retrieving a relatively large ResultSet from a remote Microsoft SQL Server 2012 to a Java client that uses Microsoft JDBC Driver 4.0.

当我在远程服务器的 Microsoft SQL Server Management Studio 上运行相应的查询时，它返回大约.几乎瞬间就有 22 万行.当我从客户端发出相同的查询时，它会停止.相同的测试在具有较早版本数据库的客户端上也运行良好，其中只有大约.400 行合格.

When I run the corresponding query on the remote server's Microsoft SQL Server Management Studio, it returns approx. 220k rows almost instantaneously. When I issue the same query from the client, it stalls. The same test has worked fine also on the client with an earlier version of the database where only approx. 400 rows qualified.

我试图通过将 ;responseBuffering=adaptive" 附加到传递给 DriverManager.getConnection() 的 URL 来解决这个问题.建立连接后，我看到了这个属性(以及其他几个)来自 connection.getMetaData().getURL()，但是[ connection.getClientInfo(responseBuffering) 返回 null，更重要的是客户仍在拖延.

I tried to tackle this by appending ;responseBuffering=adaptive" to the URL passed to DriverManager.getConnection(). After the connection is established, I see this property (among several others) in the result from connection.getMetaData().getURL(), but[ connection.getClientInfo(responseBuffering) returns null, and what is more the client is still stalling.

这里可能出了什么问题，我如何指示 Microsoft SQL Server(不仅仅是向它建议——以 Java 编程方式)它必须以较小的块返回行而不是一次全部返回，或者通过以下方式改进 JDBC 查询时间其他一些措施.

What could be going wrong here and how can I instruct the a Microsoft SQL Server (not just suggest to it -- programmatically in Java) that it must return rows in smaller chunks rather than all at once or improve JDBC query times by some other measures.

另外两个看似有些奇怪的观察结果，它们可能完全指向不同的根本原因:

Two further observations that seem somewhat strange and that perhaps point to a different root cause entirely:

• 当客户端停止时，它仍然只显示相对较轻的 CPU 负载，这与我对大量垃圾收集的预期不同
• "responseBuffering=adaptive" 应该是正常的 默认现在

UPDATE 我检查并发现从 PreparedStatement 切换到 Statement 并没有改善我的情况(它显然可以帮助其他情况).

UPDATE I've checked and found that switching from PreparedStatement to Statementdoes not improve things in my case (it apparently can help in other cases).

更新这是我当前的查询:

select 
    PARENT.IDENTIFIER    as PARENT_IDENTIFIER,
    PARENT.CLASS         as PARENT_CLASS,
    CHILD.TYPE           as CHILD_TYPE,
    CHILD.IDENTIFIER     as CHILD_IDENTIFIER,
    PROPERTY.IDENTIFIER  as PROPERTY_IDENTIFIER,
    PROPERTY.DESCRIPTION as PROPERTY_DESCRIPTION,
    PROPERTY.TYPE        as PROPERTY_TYPE,
    PROPERTY.PP          as PROPERTY_PP,
    PROPERTY.STATUS      as PROPERTY_STATUS,
    PROPERTY.TARGET      as PROPERTY_TARGET -- a date
from
    OBJECTS as CHILD
    left outer join RELATIONS              on RELATIONS.CHILD = CHILD.IDENTIFIER
    left outer join OBJECTS    as PARENT   on RELATIONS.PARENT = PARENT.IDENTIFIER
    inner join      PROPERTIES as PROPERTY on PROPERTY.OBJECT = CHILD.IDENTIFIER
where
    PROPERTY.TARGET is not null
order by
    case when PARENT.IDENTIFIER is null then 1 else 0 end,
    PARENT.IDENTIFIER,
    CHILD.IDENTIFIER,
    PROPERTY.TARGET,
    PROPERTY.IDENTIFIER

推荐答案

自适应缓冲是一个很好的答案.我还建议通过 SQL Server Profiler 检查连接的 SET 选项.

The adaptive buffering is a good answer. I would also recommend checking the connections' SET options via SQL Server Profiler.

当您开始跟踪时，请确保选择了 ExistingConnections.比较来自 JDBC 连接和 SSMS 连接的 SPID.ARITHABORT 是我看到的导致 SSMS 和 JDBC 驱动程序之间性能差异的原因.微软在这里简要提到它:http://msdn.microsoft.com/en-我们/图书馆/ms190306.aspx.此处堆栈交换信息:https://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query

When you start a trace, make sure ExistingConnections is selected. Compare a SPID from a JDBC connection and a SSMS connection. ARITHABORT comes to mind as one that I have seen cause a difference in performance between SSMS and JDBC driver. Microsoft briefly mentions it here: http://msdn.microsoft.com/en-us/library/ms190306.aspx. Stack Exchange information here: https://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query

在 Oracle 上，我看到了对 Statement/PreparedStatement 对象使用 setFetchSize 方法的巨大影响.显然，SQL Server 驱动程序不支持该方法.但是，驱动程序中有一个内部方法.请参阅 在 SQL Server 中使用设置默认行预取JDBC 驱动程序了解详情.

On Oracle, I have seen huge impacts by playing with the setFetchSize method on the Statement / PreparedStatement object. Apparently, the SQL Server driver does not support that method. However, there is an internal method in the driver for it. See Set a default row prefetch in SQL Server using JDBC driver for details.

另外，你在 while (rs.next()) 循环中做了什么?除了读取列之外什么都不做，例如 rs.getInt(1).走着瞧吧.如果它成功，则表明瓶颈在于您之前对结果集的处理.如果还是慢，那肯定是驱动或者数据库有问题.

Also, what are you doing in your while (rs.next()) loop? Try doing nothing other than reading a column, like rs.getInt(1). See what happens. If it flies, that suggests the bottleneck is in your former processing of the result set. If it is still slow, then the problem must be in the driver or database.

您可以使用 SQL Server Profiler 来比较通过 JDBC 传入和通过 SSMS 运行时的执行情况.比较 CPU、读取、写入和持续时间.如果它们不同，那么执行计划可能不同，这让我回到了我提到的第一件事:SET 选项.

You could use SQL Server Profiler to compare the executions as they come in via JDBC and as you run it via SSMS. Compare the CPU, reads, writes and duration. If they are different, then the execution plan is probably different, which points me back to the first thing I mentioned: the SET options.

这篇关于为什么 Microsoft SQL Server 2012 查询在 JDBC 4.0 中需要几分钟，而在 Management Studio 中需要几秒钟?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！