Query Slow in Java

[Vinod Kumar]

Hi,

Here is my situation...query which is executed from Java runs on MS SQL Server query analyzer within no time. But from Java it takes more than 20 minutes . I am using Prepared Statement. I am using MS SQL Server JDBC Type 4 driver.

Thanks for you any points...

Regards,
Vinod

[Shikhar Madhok]

If the same query on the same database works fine. Then I would probably look for any network latency. If that also is fine then I would check the size of the result set, if too big then I would try to first test the same query with a smaller result set (filter excess results using the where clause).

HTH
Shikhar

[Vinod Kumar]

Hi Shikhar ,

The query has a where clause, the confusing part is why only through JDBC the query is slow?. More precisely no other query in my application I see this issue, means different between query response directly from a database vs through Java. Is Statement better than Prepared Statement?

Thanks,
Vinod

[Paul Sturrock]

Like Shikhar Madhok has already said, its probably nothing to do with the query itself. If it runs quickly in another SQL client, you can assume its OK. You can further verify this by running an explain plan on the query.

Is Statement better than Prepared Statement?

Potentially yes. But this change is unlikely to improve a query that takes 20 minutes to run. 20 minutes is a massive amount of time for a query to take, unless the application is some sort of bulk import/export tool.

Likely issues could be:

very large result sets. Your Java app might be running out of memory

network latency. This will be more pronounced if the result set is big, since there is more data to pull over the network. Large data types will also slow this down (e.g. image data types)

blocking caused by some other operation in your Java application. It could just be that other JDBC operations you perform adversely affects the query

Complex logic being performed while iterating though the result set.

Can you post your code?

[Vinod Kumar]

thanks Paul and Shikar for your pointers..upon reviewing closely the sql query and adding one more condition (where clause) it resolved the issue.. the resultset has only close to 10 records but joining tables hold hundred thousand records...changing preparedstatement to statement itself gave a very good response time of 9 minutes and upon correcting the joins gives results in 3 seconds!.