• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Query Slow in Java

 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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!.
     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    reply
      Bookmark Topic Watch Topic
    • New Topic