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

问题描述

我有一个 java 程序，它对 sql server 数据库运行一堆查询.第一个查询视图返回大约 750k 条记录.我可以通过 sql server management studio 运行查询，我在大约 30 秒内得到结果.然而，我昨晚启动了程序运行.当我今天早上检查它时，这个查询仍然没有将结果返回给 java 程序，大约 15 小时后.

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 server 工具集，因此如果您能提供更多有关如何执行您可能建议的操作的详细信息，我们将不胜感激.

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());

编辑 1:

好吧，这已经有一段时间了，这被转移了，但这个问题又回来了.我考虑从 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?

编辑 2:

好吧，我结束更改这两个连接字符串参数，使用默认选择方法(直接)并将 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 驱动程序配置为使用直接连接而不是基于光标的连接.如果您不确定，可以发布您的 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 profiler 并尝试在 jdbc 驱动程序执行语句时捕获 sql 查询，然后在管理工作室中运行该语句，看看是否有区别.

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).

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