为什么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上运行相应的查询时，它返回约。 220k行几乎瞬间完成。当我从客户端发出相同的查询时，它会停止。同样的测试在客户端上运行良好，早期版本的数据库只有大约。 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附加到传递给<的URL来解决这个问题。 code> DriverManager.getConnection（）。建立连接后，我在 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应该是正常的默认现在

• When the client stalls it still shows only relatively light CPU load, unlike what I would expect from heavy garbage collection
• "responseBuffering=adaptive" should be the normal default by now

更新我检查过并发现从 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).

UPDATE 这是我当前的查询：

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.

当你开始跟踪时，make确定已选择 ExistingConnections 。比较来自JDBC连接和SSMS连接的SPID。我想到 ARITHABORT 会导致SSMS和JDBC驱动程序之间的性能出现差异。 Microsoft在此简要提及： http://msdn.microsoft.com/en-我们/库/ ms190306.aspx 。 Stack Exchange信息： https：// dba .stackexchange.com / questions / 9840 / why-would-set-arithabort-on-dramatic-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上，通过在语句 / 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.

此外，您在中做了什么（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屋！