This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Query Slow in Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query Slow in Java" Watch "Query Slow in Java" New topic
Author

Query Slow in Java

Vinod Kumar
Ranch Hand

Joined: Jan 18, 2002
Posts: 75
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
Ranch Hand

Joined: Dec 18, 2006
Posts: 95
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


He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever - Chinese proverb
Vinod Kumar
Ranch Hand

Joined: Jan 18, 2002
Posts: 75
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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?


    JavaRanch FAQ HowToAskQuestionsOnJavaRanch
    Vinod Kumar
    Ranch Hand

    Joined: Jan 18, 2002
    Posts: 75
    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!.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Query Slow in Java