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.
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).
He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever - Chinese proverb
posted 11 years ago
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?
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.
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!.