ExecuteQuery taking a long time

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup
Try using a stored procedure if you can (dont know if you can do that in JDBC )|||

Hello Arup,

I would agree that the best way to solve long running query executions is to use a stored procedure which does all the processing of the data on the server and only return to the client the necessary amount of processed data via output parameters. You can use the CallableStatement API provided by the JDBC driver for doing this.

That being said, the Microsoft SQL Server 2005 v1.2 JDBC driver includes a critical 'feature' called "Adaptive Response Buffering" which allow developers to retrieve large amounts of data. The lengthy latency you are encountering is not because of the query execution, but because of the time it takes the driver to spool the data on the client machine before control is given back to the application.

You can ready more about "Adaptive Reponse Buffering" at http://blogs.msdn.com/jdbcteam/archive/2007/05/02/what-is-adaptive-response-buffering-and-why-should-i-use-it.aspx. This functionality if available in the V1.2 CTP1 driver which has the version 1.2.2323.101. You can download it at http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en.

We appreciate your feedback using the newly added functionality of this driver.

Kind Regards,

Jaaved