从Java运行缓慢的SQL Server查询 [英] sql server query running slow from java

问题描述

我有一个Java程序，可以对sql服务器数据库运行一堆查询.其中第一个查询视图返回大约750k条记录.我可以通过sql server management studio运行查询，大约30秒后即可得到结果.但是，我从昨晚开始运行该程序.当我今天早上检查它时，大约15小时后，该查询仍未将结果返回给Java程序.

I have a java program that runs a bunch of queries against an sql server database. The first of these, which queries against a view returns about 750k records. I can run the query via sql server management studio, and I get results in about 30 seconds. however, I kicked off the program to run last night. when I checked on it this morning, this query still had not returned results back to the java program, some 15 hours later.

我可以访问数据库以执行几乎任何我想做的事情，但是我真的不确定如何开始调试它.人们应该怎么做才能找出导致这种情况的原因?我不是dba，也不对sql服务器工具集非常熟悉，因此，您可以向我提供更多有关如何执行可能建议的操作的详细信息.

I have access to the database to do just about anything I want, but I'm really not sure how to begin debugging this. What should one do to figure out what is causing a situation like this? I'm not a dba, and am not intimately familiar with the sql server tool set, so the more detail you can give me on how to do what you might suggest would be appreciated.

代码在这里

stmt = connection.createStatement();
clientFeedRS = stmt.executeQuery(StringBuffer.toString());

好一阵子了，这个问题有些偏离，但是这个问题又回来了.我研究了从jdbc驱动程序v 1.2升级到2.0的问题，但是我们坚持使用jdk 1.4，而v 2.0需要jdk 1.5，所以这不是一个开始.现在，我正在查看我的连接字符串属性.我看到2可能有用.

Well it's been a while, and this got sidetracked, but this issue is back. I looked into upgrading from jdbc driver v 1.2 to 2.0, but we are stuck on jdk 1.4, and v 2.0 require jdk 1.5 so that's a non starter. Now I'm looking at my connection string properties. I see 2 that might be useful.

SelectMethod=cursor|direct
responseBuffering=adaptive|full

当前，由于存在延迟问题，我使用游标作为selectMethod来运行，并且responseBuffering的默认值已满.更改这些属性可能会有所帮助吗?如果是这样，理想的设置是什么?根据我在网上可以找到的信息，我认为使用直接选择方法和自适应响应缓冲可能会解决我的问题.有什么想法吗?

Currently, with the latency issue, I am running with cursor as the selectMethod, and with the default for responseBuffering which is full. Is changing these properties likely to help? if so, what would be the ideal settings? I'm thinking, based on what I can find online, that using a direct select method and adaptive response buffering might solve my issue. any thoughts?

我将使用默认的select方法(直接)并指定responseBuffering为自适应来更改这两个连接字符串参数.这最终对我来说效果最好，并缓解了我所看到的延迟问题.感谢所有帮助.

WEll I ended changing both of these connection string params, using the default select method(direct) and specifying the responseBuffering as adaptive. This ends up working best for me and alleviates the latency issues I was seeing. thanks for all the help.

推荐答案

请确保将JDBC驱动程序配置为使用直接连接而不是基于cusror的连接.如果不确定，可以发布JDBC连接URL.

Be sure that your JDBC driver is configured to use a direct connection and not a cusror based connection. You can post your JDBC connection URL if you are not sure.

确保您使用的是只读，只读结果集(如果未设置，则为默认设置).

Make sure you are using a forward-only, read-only result set (this is the default if you are not setting it).

并确保您使用的是更新的JDBC驱动程序.

And make sure you are using updated JDBC drivers.

如果所有这些都不起作用，那么您应该查看sql事件探查器，并尝试在jdbc驱动程序执行该语句时捕获sql查询，并在Management Studio中运行该语句，看看是否有区别.

If all of this is not working, then you should look at the sql profiler and try to capture the sql query as the jdbc driver executes the statement, and run that statement in the management studio and see if there is a difference.

此外，由于要提取大量数据，因此应尝试确保JVM上没有任何内存/垃圾收集速度下降(尽管在这种情况下并不能真正解释时间差异).

Also, since you are pulling so much data, you should be try to be sure you aren't having any memory/garbage collection slowdowns on the JVM (although in this case that doesn't really explain the time discrepancy).

这篇关于从Java运行缓慢的SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！