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:
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?
目前,由于延迟问题,我使用cursor作为selectMethod运行,并且responseBuffering的默认值已满。改变这些属性可能会有所帮助吗?如果是这样,那么理想的设置是什么?我想,根据我在网上找到的内容,使用直接选择方法和自适应响应缓冲可能会解决我的问题。有什么想法吗?
EDIT2:
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.
我是否结束了这两个连接字符串参数的更改,使用默认的select方法(直接)并将responseBuffering指定为自适应。这最终最适合我,并缓解我看到的延迟问题。感谢所有的帮助。
12 个解决方案
#1
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驱动程序配置为使用直接连接而不是基于cusror的连接。如果您不确定,可以发布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并尝试捕获sql查询,因为jdbc驱动程序执行该语句,并在管理工作室中运行该语句,看看是否存在差异。
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上没有任何内存/垃圾收集速度减慢(尽管在这种情况下并不能解释时间差异)。
#2
I had similar problem, with a very simple request (SELECT . FROM . WHERE = .) taking up to 10 seconds to return a single row when using a jdbc connection in Java, while taking only 0.01s in sqlshell. The problem was the same whether i was using the official MS SQL driver or the JTDS driver.
我有类似的问题,一个非常简单的请求(SELECT.FROM.WHERE =。)在Java中使用jdbc连接时需要10秒才能返回单行,而在sqlshell中只需0.01s。无论我使用官方MS SQL驱动程序还是JTDS驱动程序,问题都是一样的。
The solution was to setup this property in the jdbc url : sendStringParametersAsUnicode=false
解决方案是在jdbc url中设置此属性:sendStringParametersAsUnicode = false
Full example if you are using MS SQL official driver : jdbc:sqlserver://yourserver;instanceName=yourInstance;databaseName=yourDBName;sendStringParametersAsUnicode=false;
完整示例如果您使用的是MS SQL官方驱动程序:jdbc:sqlserver:// yourserver; instanceName = yourInstance; databaseName = yourDBName; sendStringParametersAsUnicode = false;
Instructions if using different jdbc drivers and more detailled infos about the problem here : http://emransharif.blogspot.fr/2011/07/performance-issues-with-jdbc-drivers.html
如果在这里使用不同的jdbc驱动程序和更多关于此问题的详细信息的说明:http://emransharif.blogspot.fr/2011/07/performance-issues-with-jdbc-drivers.html
SQL Server differentiates its data types that support Unicode from the ones that just support ASCII. For example, the character data types that support Unicode are nchar, nvarchar, longnvarchar where as their ASCII counter parts are char, varchar and longvarchar respectively. By default, all Microsoft’s JDBC drivers send the strings in Unicode format to the SQL Server, irrespective of whether the datatype of the corresponding column defined in the SQL Server supports Unicode or not. In the case where the data types of the columns support Unicode, everything is smooth. But, in cases where the data types of the columns do not support Unicode, serious performance issues arise especially during data fetches. SQL Server tries to convert non-unicode datatypes in the table to unicode datatypes before doing the comparison. Moreover, if an index exists on the non-unicode column, it will be ignored. This would ultimately lead to a whole table scan during data fetch, thereby slowing down the search queries drastically.
SQL Server将支持Unicode的数据类型与仅支持ASCII的数据类型区分开来。例如,支持Unicode的字符数据类型是nchar,nvarchar,longnvarchar,因为它们的ASCII计数器部分分别是char,varchar和longvarchar。默认情况下,所有Microsoft的JDBC驱动程序都将Unicode格式的字符串发送到SQL Server,而不管SQL Server中定义的相应列的数据类型是否支持Unicode。在列的数据类型支持Unicode的情况下,一切都很顺利。但是,如果列的数据类型不支持Unicode,则会出现严重的性能问题,尤其是在数据提取期间。 SQL Server尝试在执行比较之前将表中的非unicode数据类型转换为unicode数据类型。此外,如果非unicode列上存在索引,则将忽略该索引。这最终会在数据提取期间导致整个表扫描,从而大大减慢搜索查询的速度。
In my case, i had 30M+ records in the table i was searching from. The duration to complete the request went from more than 10 seconds, to approximatively 0.01s after applying the property.
在我的情况下,我在搜索的表中有30M +记录。申请完成申请的时间从10秒以上到申请财产后大约0.01秒。
Hope this will help someone !
希望这会对某人有所帮助!
#3
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准备好的statemnent花了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服务器能够查看它,决定“日期范围是巨大的,让我们不使用日期索引”,然后它选择了更好的东西。
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年中分散的,那么做“基于范围大小的多个查询”的事情可能不是最好的。您必须根据特定范围或某些内容优化查询。你需要通过试验找出错误。
#4
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 for string等。尝试使用硬编码到查询体中的所有参数运行查询而不进行任何操作?看到这是一个问题。
#5
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 Here for more
请看这里了解更多
#6
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 loading for example. Without more details on what you're trying to accomplish, it's hard to say.
拉回那么多数据需要很多时间。您应该想办法在任何给定时间不要求应用程序中有太多数据。例如,页面数据或使用延迟加载。如果没有关于你想要完成什么的更多细节,很难说。
#7
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
#8
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访问数据库的方式。
#9
Try adjusting the fetch size of the Statement and try selectMethod of cursor
尝试调整Statement的获取大小并尝试selectMethod of cursor
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
#10
Quote from the MS Adaptive buffer guidelines:
引用MS Adaptive缓冲区指南:
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:
在某些情况下,使用selectMethod = cursor而不是responseBuffering = adaptive更有用,例如:
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.
如果您的应用程序缓慢处理只进,只读结果集,例如在某些用户输入后读取每一行,则使用selectMethod = cursor而不是responseBuffering = adaptive可能有助于减少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.
如果您的应用程序在同一连接上同时处理两个或多个只进,只读结果集,则使用selectMethod = cursor而不是responseBuffering = adaptive可能有助于减少驱动程序在处理这些结果集时所需的内存。
In both cases, you need to consider the overhead of creating, reading, and closing the server cursors.
在这两种情况下,您都需要考虑创建,读取和关闭服务器游标的开销。
See more: http://technet.microsoft.com/en-us/library/bb879937.aspx
查看更多:http://technet.microsoft.com/en-us/library/bb879937.aspx
#11
Sometimes it could be due to the way parameters are binding to the query object. I found the following code is very slow when executing from java program.
有时可能是由于参数绑定到查询对象的方式。我发现从java程序执行时,以下代码非常慢。
Query query = em().createNativeQuery(queryString)
.setParameter("param", SomeEnum.DELETED.name())
Once I remove the "deleted" parameter and directly append that "DELETED" string to the query, it became super fast. It may be due to that SQL server is expecting to have all the parameters bound to decide the optimized plan.
删除“已删除”参数并将“DELETED”字符串直接附加到查询后,它变得非常快。这可能是由于SQL服务器期望绑定所有参数来决定优化计划。
#12
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版本慢得多,那么我会检查几件事:
- You shoudl get the best performance with a forward-only, read-only ResultSet.
- 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.
您只需使用只进,只读ResultSet即可获得最佳性能。
我记得MSFT的旧JDBC驱动程序很慢。确保您使用的是最新的n-great。我认为有一个通用的SQL Server专用于SQL 2005。
#1
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驱动程序配置为使用直接连接而不是基于cusror的连接。如果您不确定,可以发布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并尝试捕获sql查询,因为jdbc驱动程序执行该语句,并在管理工作室中运行该语句,看看是否存在差异。
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上没有任何内存/垃圾收集速度减慢(尽管在这种情况下并不能解释时间差异)。
#2
I had similar problem, with a very simple request (SELECT . FROM . WHERE = .) taking up to 10 seconds to return a single row when using a jdbc connection in Java, while taking only 0.01s in sqlshell. The problem was the same whether i was using the official MS SQL driver or the JTDS driver.
我有类似的问题,一个非常简单的请求(SELECT.FROM.WHERE =。)在Java中使用jdbc连接时需要10秒才能返回单行,而在sqlshell中只需0.01s。无论我使用官方MS SQL驱动程序还是JTDS驱动程序,问题都是一样的。
The solution was to setup this property in the jdbc url : sendStringParametersAsUnicode=false
解决方案是在jdbc url中设置此属性:sendStringParametersAsUnicode = false
Full example if you are using MS SQL official driver : jdbc:sqlserver://yourserver;instanceName=yourInstance;databaseName=yourDBName;sendStringParametersAsUnicode=false;
完整示例如果您使用的是MS SQL官方驱动程序:jdbc:sqlserver:// yourserver; instanceName = yourInstance; databaseName = yourDBName; sendStringParametersAsUnicode = false;
Instructions if using different jdbc drivers and more detailled infos about the problem here : http://emransharif.blogspot.fr/2011/07/performance-issues-with-jdbc-drivers.html
如果在这里使用不同的jdbc驱动程序和更多关于此问题的详细信息的说明:http://emransharif.blogspot.fr/2011/07/performance-issues-with-jdbc-drivers.html
SQL Server differentiates its data types that support Unicode from the ones that just support ASCII. For example, the character data types that support Unicode are nchar, nvarchar, longnvarchar where as their ASCII counter parts are char, varchar and longvarchar respectively. By default, all Microsoft’s JDBC drivers send the strings in Unicode format to the SQL Server, irrespective of whether the datatype of the corresponding column defined in the SQL Server supports Unicode or not. In the case where the data types of the columns support Unicode, everything is smooth. But, in cases where the data types of the columns do not support Unicode, serious performance issues arise especially during data fetches. SQL Server tries to convert non-unicode datatypes in the table to unicode datatypes before doing the comparison. Moreover, if an index exists on the non-unicode column, it will be ignored. This would ultimately lead to a whole table scan during data fetch, thereby slowing down the search queries drastically.
SQL Server将支持Unicode的数据类型与仅支持ASCII的数据类型区分开来。例如,支持Unicode的字符数据类型是nchar,nvarchar,longnvarchar,因为它们的ASCII计数器部分分别是char,varchar和longvarchar。默认情况下,所有Microsoft的JDBC驱动程序都将Unicode格式的字符串发送到SQL Server,而不管SQL Server中定义的相应列的数据类型是否支持Unicode。在列的数据类型支持Unicode的情况下,一切都很顺利。但是,如果列的数据类型不支持Unicode,则会出现严重的性能问题,尤其是在数据提取期间。 SQL Server尝试在执行比较之前将表中的非unicode数据类型转换为unicode数据类型。此外,如果非unicode列上存在索引,则将忽略该索引。这最终会在数据提取期间导致整个表扫描,从而大大减慢搜索查询的速度。
In my case, i had 30M+ records in the table i was searching from. The duration to complete the request went from more than 10 seconds, to approximatively 0.01s after applying the property.
在我的情况下,我在搜索的表中有30M +记录。申请完成申请的时间从10秒以上到申请财产后大约0.01秒。
Hope this will help someone !
希望这会对某人有所帮助!
#3
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准备好的statemnent花了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服务器能够查看它,决定“日期范围是巨大的,让我们不使用日期索引”,然后它选择了更好的东西。
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年中分散的,那么做“基于范围大小的多个查询”的事情可能不是最好的。您必须根据特定范围或某些内容优化查询。你需要通过试验找出错误。
#4
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 for string等。尝试使用硬编码到查询体中的所有参数运行查询而不进行任何操作?看到这是一个问题。
#5
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 Here for more
请看这里了解更多
#6
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 loading for example. Without more details on what you're trying to accomplish, it's hard to say.
拉回那么多数据需要很多时间。您应该想办法在任何给定时间不要求应用程序中有太多数据。例如,页面数据或使用延迟加载。如果没有关于你想要完成什么的更多细节,很难说。
#7
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
#8
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访问数据库的方式。
#9
Try adjusting the fetch size of the Statement and try selectMethod of cursor
尝试调整Statement的获取大小并尝试selectMethod of cursor
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
#10
Quote from the MS Adaptive buffer guidelines:
引用MS Adaptive缓冲区指南:
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:
在某些情况下,使用selectMethod = cursor而不是responseBuffering = adaptive更有用,例如:
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.
如果您的应用程序缓慢处理只进,只读结果集,例如在某些用户输入后读取每一行,则使用selectMethod = cursor而不是responseBuffering = adaptive可能有助于减少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.
如果您的应用程序在同一连接上同时处理两个或多个只进,只读结果集,则使用selectMethod = cursor而不是responseBuffering = adaptive可能有助于减少驱动程序在处理这些结果集时所需的内存。
In both cases, you need to consider the overhead of creating, reading, and closing the server cursors.
在这两种情况下,您都需要考虑创建,读取和关闭服务器游标的开销。
See more: http://technet.microsoft.com/en-us/library/bb879937.aspx
查看更多:http://technet.microsoft.com/en-us/library/bb879937.aspx
#11
Sometimes it could be due to the way parameters are binding to the query object. I found the following code is very slow when executing from java program.
有时可能是由于参数绑定到查询对象的方式。我发现从java程序执行时,以下代码非常慢。
Query query = em().createNativeQuery(queryString)
.setParameter("param", SomeEnum.DELETED.name())
Once I remove the "deleted" parameter and directly append that "DELETED" string to the query, it became super fast. It may be due to that SQL server is expecting to have all the parameters bound to decide the optimized plan.
删除“已删除”参数并将“DELETED”字符串直接附加到查询后,它变得非常快。这可能是由于SQL服务器期望绑定所有参数来决定优化计划。
#12
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版本慢得多,那么我会检查几件事:
- You shoudl get the best performance with a forward-only, read-only ResultSet.
- 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.
您只需使用只进,只读ResultSet即可获得最佳性能。
我记得MSFT的旧JDBC驱动程序很慢。确保您使用的是最新的n-great。我认为有一个通用的SQL Server专用于SQL 2005。