File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query Slow in Java

 
Vinod Kumar
Ranch Hand
Posts: 75
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 95
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 75
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Ranch Hand
    Posts: 75
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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!.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic