Server SQL query is slow from java

Question

I have a java program that launches a bunch of queries against a sql server database. The first of them, which requests a presentation, returns about 750 thousand records. I can run the query through sql server management studio and I get the results after about 30 seconds. however, I started a program to work last night. when I checked this this morning, this query still did not return the results back to the java program, after 15 hours.

I have access to the database to do whatever I want, but I'm really not sure how to start debugging. What needs to be done to find out what causes this situation? I am not dBA, and I am not familiar with the sql server toolkit, so the more detailed information you can give me on how to do what you could offer will be appreciated.

heres code

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

EDIT1:

Well, it was time, and it was distracting, but this problem came back. I studied the update from the jdbc v 1.2 driver to 2.0, but we were stuck on jdk 1.4, and v 2.0 requires jdk 1.5, so as not to run. Now I am viewing the properties of the connection string. I see 2 that may be useful.

 SelectMethod=cursor|direct responseBuffering=adaptive|full 

Currently, with a latency problem, I am running the cursor as the selectMethod method and the default for responseBuffering, which is full. Are these properties changing that might help? if so, what are the ideal settings? I think based on what I can find on the Internet that using the direct selection and buffering method of adaptive response can solve my problem. any thoughts?

EDIT2:

WEll I ended up changing both of these connection string parameters using the default (direct) selection method and specifying responseBuffering as adaptive. This ultimately works best for me and alleviates the lag problems I've seen. Thanks for all the help.

Answer 1

Make sure your JDBC driver is configured to use a direct connection, not a cusror based connection. You can publish the URL of your JDBC connection if you are not sure.

Make sure you use only the read-only result set (this is the default if you do not set it).

And make sure you are using updated JDBC drivers.

If all this does not work, you should look at the sql profiler and try to capture the sql query when the jdbc driver executes the instruction and runs this statement in the management studio and see if there is a difference.

In addition, since you are extracting so much data, you must be sure that you do not have any memory / garbage notes in the JVM (although in this case this does not explain the time difference).

Answer 2

I had a similar problem with a very simple query (SELECT. FROM. WHERE =.) Taking up to 10 seconds to return a single row when using jdbc connection in Java, while in sqlshell it will only accept 0.01. The problem was the same regardless of whether I used the official MS SQL driver or the JTDS driver.

The solution was to set this property in the jdbc url : sendStringParametersAsUnicode = false

Full example if you use the official MS SQL driver: jdbc: sqlserver: // yourserver; instanceName = yourInstance; databaseName = yourDBName; sendStringParametersAsUnicode = false;

Instructions for using different jdbc drivers and more detailed information about the problem here: http://emransharif.blogspot.fr/2011/07/performance-issues-with-jdbc-drivers.html

SQL Server distinguishes its data types that support Unicode from those that support ASCII. For example, the character data types that support Unicode are nchar, nvarchar, longnvarchar, where ASCII char, varchar, and longvarchar, respectively, are their counting parts. By default, all Microsoft JDBC drivers send Unicode strings to SQL Server, regardless of whether the data type of the corresponding column defined in SQL Server supports Unicode or not. In the case where the column data types support Unicode, everything goes smoothly. But in cases where the column data types do not support Unicode, serious performance problems arise, especially during data collection. SQL Server tries to convert data types other than unicode in the table to unicode data types before performing the comparison. Moreover, if an index exists in a non-Unicode column, it will be ignored. This ultimately leads to a scan of the entire table during data retrieval, thereby drastically slowing down search queries.

In my case, I had 30M + entries in the table in which I was looking. The query execution time lasted more than 10 seconds, up to about 0.01 s after applying this property.

Hope this helps someone!

Answer 3

This does not seem to apply to your specific situation, but I wanted to provide another possible explanation to someone who is looking for this problem.

I had a similar problem when the query executed directly in SQL Server took 1 minute and the same query took 5 minutes through the prepared java statemnent. I tracked it to the point that it was done as a prepared expression.

When you execute a query directly in SQL Server, you provide it with a non-parameterized query in which it knows all the search criteria during optimization. In my case, the search criteria included a date range, and the SQL server was able to look at it, deciding that "this date range is huge, do not use the date index", and then he chose something much better.

When I execute the same query through a prepared java statement, while SQL Server is optimizing the query, you have not provided it with any parameter values, so it should make an assumption which index to use, In case of my date range, if it is optimized for a small range, and I give him a large range, it will work slower than he could. Similarly, if it is optimized for a large range, and I give it a small one, it will again work slower than I could.

To demonstrate that this is indeed a problem, as an experiment, I tried to give him tips on what needs to be optimized to use the SQL Server OPTIMIZE FOR option. When I told him to use a tiny date range, my Java query (which actually had a wide date range) actually took twice as much as before (10 minutes, unlike 5 minutes before, and unlike 1 minute in SQL Server). When I said that my exact dates were for optimization, the runtime was identical between the prepared Java expression.

So my solution was to hardcode the exact dates in the request. This worked for me because it was a one-time expression. PreparedStatement was not intended to be reused, but simply to parameterize values ​​to avoid SQL injection. Since these dates came from the java.sql.Date object, I did not have to worry about my date values ​​containing the injection code.

However, for a statement that needs to be reused, hard date encoding will not work. Perhaps the best option for this would be to create several prepared statements optimized for different date ranges (one for one 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 request, execute one prepared statement whose time range best matches the range in the current request.

Of course, this only works when your date ranges are evenly distributed. If 80% of your records were last year and 20% were allocated over the previous 10 years, then performing "multiple queries based on range size" might not be the best. You will need to optimize your queries based on certain ranges or something else. You will need to understand that an error has occurred through the trial version.

Answer 4

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

Answer 5

I know this is an old question, but since this is one of the first results when looking for this problem, I decided that I should post what worked for me. I had a query that took less than 10 seconds when I used the SQL Server JDBC driver, but more than 4 minutes when using jTDS. I tried all the suggestions mentioned here, and none of this mattered. The only thing that worked was to add this to the url :; prepareSQL = 1 "

See here for more

Answer 6

Returning to the fact that many data will require a lot of time. You should probably figure out a way to not require so much data in your application at any given time. Data page or use lazy loading , for example. It is difficult to say in more detail about what you are trying to accomplish.

Answer 7

The fact that it starts quickly from the management studio can be caused by an incorrectly cached query plan and outdated indexes (say, because of a lot of import or deletion). It quickly returns all 750 thousand records in SSMS?

Try rebuilding your indexes (or if it takes too long, update the statistics); and possibly clearing the procedure cache (be careful if this is a production system ...): DBCC FREEPROCCACHE

Answer 8

To start debugging, it would be useful to determine if the problem area is in the database or in the application. Have you tried changing the query so that it returns a much lower result? If this does not return, I would suggest targeting how you access the database with Java.

Answer 9

Try adjusting the sample size of Statement and try selectMethod cursor

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

We had problems with large result sets using mysql and required it to pass the result set as described in the following link.

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

Answer 10

Quote from MS Adaptive buffer recommendations:

Avoid using the selectMethod = cursor connection string property so that the application can handle a very large result set. The adaptive buffering feature allows applications to process very large result-only read-only result sets without using a server cursor. Note that when you select selectMethod = cursor, only all read-only result sets created only by this connection are affected. In other words, if your application regularly processes short result sets with several lines, creating, reading and closing the server cursor for each result set will use more resources both on the client side and on the server side than if selectMethod is not installed in cursor.

and

There are times when using selectMethod = cursor instead of responseBuffering = adaptive would be more useful, for example:

• If your application handles direct access only, the read-only result is set slowly, for example, reading each line after entering a user using selectMethod = cursor instead of responseBuffering = adaptive can help reduce the use of SQL Server resources.

• If your application processes two or more read-only datasets of read-only data in the same connection, use selectMethod = cursor instead of responseBuffering = adaptive can help reduce the amount of memory required by the driver while processing these result sets.

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

More details: http://technet.microsoft.com/en-us/library/bb879937.aspx

Answer 11

Sometimes this may be due to how the parameters are bound to the request object. I found that the following code is very slow when executed from a java program.

 Query query = em().createNativeQuery(queryString) .setParameter("param", SomeEnum.DELETED.name()) 

As soon as I delete the “remote” parameter and directly add the line “DELETED” to the query, it becomes very fast. Perhaps due to the fact that the SQL server expects that all parameters will definitely solve the optimized plan.

Answer 12

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

• You can get better performance with the read-only ResultSet method.
• I remember that the old MSFT JDBC drivers were slow. Make sure you use the latest n-largest. I think there is a common SQL Server one and one specifically for SQL 2005.