sql server查询从java运行缓慢

sql server query running slow from java

提问by shsteimer

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.

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

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.

我可以访问数据库来做我想做的任何事情，但我真的不知道如何开始调试它。应该怎么做才能弄清楚是什么导致了这种情况？我不是 dba，对 sql server 工具集也不是很熟悉，因此如果您能提供更多有关如何执行您可能建议的操作的详细信息，我们将不胜感激。

heres the code

继承人的代码

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

EDIT1:

编辑1：

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.

好吧，已经有一段时间了，这被转移了，但是这个问题又回来了。我考虑从 jdbc 驱动程序 v 1.2 升级到 2.0，但我们被困在 jdk 1.4 上，而 v 2.0 需要 jdk 1.5，所以这是一个非初学者。现在我正在查看我的连接字符串属性。我看到 2 个可能有用。

SelectMethod=cursor|direct
responseBuffering=adaptive|full

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?

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

EDIT2:

编辑2：

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.

嗯，我结束了更改这两个连接字符串参数，使用默认选择方法（直接）并将 responseBuffering 指定为自适应。这最终对我来说效果最好，并减轻了我所看到的延迟问题。感谢所有的帮助。

采纳答案by Yishai

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.

确保您的 JDBC 驱动程序配置为使用直接连接而不是基于光标的连接。如果您不确定，您可以发布您的 JDBC 连接 URL。

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

确保您使用的是只进、只读的结果集（如果您没有设置它，则这是默认值）。

And make sure you are using updated JDBC drivers.

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

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.

如果所有这些都不起作用，那么您应该查看 sql profiler 并尝试在 jdbc 驱动程序执行语句时捕获 sql 查询，然后在管理工作室中运行该语句，看看是否有区别。

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

此外，由于您要提取如此多的数据，您应该尝试确保 JVM 上没有任何内存/垃圾收集速度减慢（尽管在这种情况下并不能真正解释时间差异）。

回答by JP Alioto

Pulling back that much data is going to require lots of time. You should probably figure out a way to not require that much data in your application at any given time. Page the data or use lazy loadingfor example. Without more details on what you're trying to accomplish, it's hard to say.

拉回这么多数据将需要大量时间。您可能应该想出一种方法，在任何给定时间都不需要在您的应用程序中使用那么多数据。例如，分页数据或使用延迟加载。如果没有关于您要完成的任务的更多详细信息，则很难说。

回答by Mitch Wheat

The fact that it is quick when run from management studio could be due to an incorrectly cached query plan and out of date indexes (say, due to a large import or deletions). Is it returning all 750K records quickly in SSMS?

从管理工作室运行时速度很快的事实可能是由于不正确缓存的查询计划和过时的索引（例如，由于大量导入或删除）。它是否在 SSMS 中快速返回所有 750K 记录？

Try rebuilding your indexes (or if that would take too long, update your statistics); and maybe flushing the procedure cache (use caution if this is a production system...): DBCC FREEPROCCACHE

尝试重建您的索引（或者如果这需要太长时间，请更新您的统计数据）；并且可能会刷新过程缓存（如果这是一个生产系统，请小心...）：DBCC FREEPROCCACHE

回答by akf

To start debugging this, it would be good to determine whether the problem area is in the database or in the app. Have you tried changing the query such that it returns a much smaller result? If that doesnt return, I would suggest targeting the way you are accessing the DB from Java.

要开始调试，最好确定问题区域是在数据库中还是在应用程序中。您是否尝试过更改查询以使其返回更小的结果？如果没有返回，我建议针对您从 Java 访问数据库的方式。

回答by Brian Reiter

Does it take a similar amount of time with SQLWB? If the Java version is much slower, then I would check a couple of things:

使用 SQLWB 是否需要类似的时间？如果 Java 版本慢得多，那么我会检查几件事：

1. You shoudl get the best performance with a forward-only, read-only ResultSet.
2. I recall that the older JDBC drivers from MSFT were slow. Make sure you are using the latest-n-greatest. I think there is a generic SQL Server one and one specifically for SQL 2005.

1. 您应该使用只进、只读的 ResultSet 获得最佳性能。
2. 我记得 MSFT 的旧 JDBC 驱动程序很慢。确保您使用的是最新的n-greatest。我认为有一个通用的 SQL Server 一个，一个专门用于 SQL 2005。

回答by objects

Try adjusting the fetch size of the Statement and try selectMethod of cursor

尝试调整Statement的fetch size并尝试cursor的selectMethod

http://technet.microsoft.com/en-us/library/aa342344(SQL.90).aspx

http://technet.microsoft.com/en-us/library/aa342344(SQL.90).aspx

We had issues with large result sets using mysql and needed to make it stream the result set as explained in the following link.

我们在使用 mysql 时遇到了大型结果集的问题，需要使其流式传输结果集，如以下链接中所述。

http://helpdesk.objects.com.au/java/avoiding-outofmemoryerror-with-mysql-jdbc-driver

http://helpdesk.objects.com.au/java/avoiding-outofmemoryerror-with-mysql-jdbc-driver

回答by Leon Fleysher

If the query is parametrized it can be a missing parameter or a parameter that is set with the wrong function, e.g. setLong for string, etc. Try to run your query with all parameters hardcoded into the query body without any ?to see of this is a problem.

如果查询是参数化的，则可能是缺少参数或使用错误函数设置的参数，例如 setLong 用于字符串等。尝试使用硬编码到查询正文中的所有参数运行您的查询，而无需?查看这是一个问题。

回答by eugenevd

Quote from the MS Adaptive buffer guidelines:

引自 MS 自适应缓冲液指南：

Avoid using the connection string property selectMethod=cursor to allow the application to process a very large result set. The adaptive buffering feature allows applications to process very large forward-only, read-only result sets without using a server cursor. Note that when you set selectMethod=cursor, all forward-only, read-only result sets produced by that connection are impacted. In other words, if your application routinely processes short result sets with a few rows, creating, reading, and closing a server cursor for each result set will use more resources on both client-side and server-side than is the case where the selectMethod is not set to cursor.

避免使用连接字符串属性 selectMethod=cursor 来允许应用程序处理非常大的结果集。自适应缓冲功能允许应用程序在不使用服务器游标的情况下处理非常大的只进、只读结果集。请注意，当您设置 selectMethod=cursor 时，该连接产生的所有只进、只读结果集都会受到影响。换句话说，如果您的应用程序经常处理包含几行的短结果集，那么为每个结果集创建、读取和关闭服务器游标将在客户端和服务器端使用比 selectMethod 的情况更多的资源未设置为光标。

And

和

There are some cases where using selectMethod=cursor instead of responseBuffering=adaptive would be more beneficial, such as:

• If your application processes a forward-only, read-only result set slowly, such as reading each row after some user input, using selectMethod=cursor instead of responseBuffering=adaptive might help reduce resource usage by SQL Server.

• If your application processes two or more forward-only, read-only result sets at the same time on the same connection, using selectMethod=cursor instead of responseBuffering=adaptive might help reduce the memory required by the driver while processing these result sets.

In both cases, you need to consider the overhead of creating, reading, and closing the server cursors.

在某些情况下，使用 selectMethod=cursor 而不是 responseBuffering=adaptive 会更有益，例如：

• 如果您的应用程序处理只进、只读的结果集很慢，例如在某些用户输入后读取每一行，使用 selectMethod=cursor 而不是 responseBuffering=adaptive 可能有助于减少 SQL Server 的资源使用。

• 如果您的应用程序在同一连接上同时处理两个或更多只进只读结果集，使用 selectMethod=cursor 而不是 responseBuffering=adaptive 可能有助于减少驱动程序在处理这些结果集时所需的内存。

在这两种情况下，您都需要考虑创建、读取和关闭服务器游标的开销。

See more: http://technet.microsoft.com/en-us/library/bb879937.aspx

查看更多：http: //technet.microsoft.com/en-us/library/bb879937.aspx

回答by ldkronos

It appears this may not have applied to your particular situation, but I wanted to provide another possible explanation for someone searching for this problem.

看来这可能不适用于您的特定情况，但我想为搜索此问题的人提供另一种可能的解释。

I just had a similar problem where a query executed directly in SQL Server took 1 minute while the same query took 5 minutes through a java prepared statemnent. I tracked it down to the fact that it is was done as a prepared statement.

我刚刚遇到了一个类似的问题，直接在 SQL Server 中执行的查询需要 1 分钟，而通过 Java 准备好的语句执行相同的查询需要 5 分钟。我追踪到它是作为准备好的声明完成的。

When you execute a query directly in SQL Server, you are providing it a non-parameterized query, in which it knows all of the search criteria at optimization time. In my case, my search criteria included a date range, and SQL server was able to look at it, decide "that date range is huge, let's not use the date index" and then it chose something much better.

当您直接在 SQL Server 中执行查询时，您正在为其提供一个非参数化查询，其中它在优化时知道所有搜索条件。就我而言，我的搜索条件包括一个日期范围，SQL Server 能够查看它，决定“该日期范围很大，我们不要使用日期索引”，然后它选择了更好的东西。

When I execute the same query through a java prepared statement, at the time that SQL Server is optimizing the query, you haven't yet provided it any of the parameter values, so it has to make a guess which index to use. In the case of my date range, if it optimizes for a small range and I give it a large range, it will perform slower than it could. Likewise if it optimizes for a large range and I give it a small one, it's again going to perform slower than it could.

当我通过 java 准备好的语句执行相同的查询时，在 SQL Server 正在优化查询时，您还没有向它提供任何参数值，因此它必须猜测要使用哪个索引。就我的日期范围而言，如果它针对小范围进行优化，而我给它一个大范围，则它的执行速度会比预期的要慢。同样，如果它针对大范围进行优化，而我给它一个小范围，那么它的执行速度将再次比原来慢。

To demonstrate this was indeed the problem, as an experiment I tried giving it hints as to what to optimize for using SQL Server's "OPTIMIZE FOR" option. When I told it to use a tiny date range, my java query (which actually had a wide date range) actually took twice as long as before (10 minutes, as opposed to 5 minutes before, and as opposed to 1 minute in SQL Server). When I told it my exact dates to optimize for, the execution time was identical between the java prepared statement.

为了证明这确实是问题所在，作为一个实验，我尝试提示它使用 SQL Server 的“OPTIMIZE FOR”选项优化什么。当我告诉它使用一个很小的日期范围时，我的 java 查询（实际上有一个很宽的日期范围）实际上是以前的两倍（10 分钟，而不是 5 分钟之前，而不是 SQL Server 中的 1 分钟） ）。当我告诉它我要优化的确切日期时，java 准备好的语句之间的执行时间是相同的。

So my solution was to hard code the exact dates into the query. This worked for me because this was just a one-off statement. The PreparedStatement was not intended to be reused, but merely to parameterize the values to avoid SQL injection. Since these dates were coming from a java.sql.Date object, I didn't have to worry about my date values containing injection code.

所以我的解决方案是将确切日期硬编码到查询中。这对我有用，因为这只是一次性声明。PreparedStatement 不打算重用，而只是为了参数化值以避免 SQL 注入。由于这些日期来自 java.sql.Date 对象，因此我不必担心我的日期值包含注入代码。

However, for a statement that DOES need to be reused, hard coding the dates wouldn't work. Perhaps a better option for that would be to create multiple prepared statements optimized for different date ranges (one for a day, one for a week, one for a month, one for a year, and one for a decade...or maybe you only need 2 or 3 options...I don't know) and then for each query, execute the one prepared statement whose time range best matches the range in the actual query.

但是，对于 DOES 需要重用的语句，对日期进行硬编码是行不通的。也许更好的选择是创建针对不同日期范围优化的多个准备好的语句（一个用于一天，一个用于一周，一个用于一个月，一个用于一年，一个用于十年......或者你只需要 2 或 3 个选项......我不知道）然后对于每个查询，执行一个准备好的语句，其时间范围与实际查询中的范围最匹配。

Of course, this only works well if your date ranges are evenly distributed. If 80% of your records were in the last year, and 20% percent spread out over the previous 10 years, then doing the "multiple queries based on range size" thing might not be best. You'd have to optimize you queries based on specific ranges or something. You'd need to figure that out through trial an error.

当然，这只适用于您的日期范围均匀分布的情况。如果您 80% 的记录在去年，而 20% 的记录分布在前 10 年，那么执行“基于范围大小的多个查询”的事情可能不是最好的。您必须根据特定范围或其他内容优化查询。你需要通过试错来解决这个问题。

回答by Sol

I know this is an old question but since it's one of the first results when searching for this issue I figured I should post what worked for me. I had a query that took less than 10 seconds when I used SQL Server JDBC driver but more than 4 minutes when using jTDS. I tried all suggestions mentioned here and none of it made any difference. The only thing that worked is adding this to the URL ";prepareSQL=1"

我知道这是一个老问题，但由于它是搜索此问题时的第一个结果之一，我想我应该发布对我有用的内容。我有一个查询，使用 SQL Server JDBC 驱动程序时花费不到 10 秒，但使用 jTDS 时花费了 4 分钟以上。我尝试了这里提到的所有建议，但没有任何区别。唯一有效的是将它添加到 URL ";prepareSQL=1"

See Herefor more

看到这里了解更多