Help coderanch get a
new server
by contributing to the fundraiser
  • 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL query takes too long to execute?

 
Ranch Hand
Posts: 476
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to select data from a table that has 4,630 records. Now, when I select this data from UNIX shell it takes less than a second, from JDBC it take good 3-4 seconds. Is such difference in execution time normal?

 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Alex,

I don't think that's normal, but perhaps not surprising as there's quite a lot going on. Try the following code to determine where the time is going:

You can also use the Unix time command to accurately time your query in Unix. I'm assuming that this is just test code, otherwise you should rewrite your query to use "SELECT COUNT(*)" Also, are you aware that you close the connection twice for each exception (catch & finally)?

Hope this helps.

Jules
 
Alex Kravets
Ranch Hand
Posts: 476
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried it, and it seems that results vary. One time it will take 4 sec, another 3 sec and sometimes 1 sec. And it all happens at execute().
Also, I close my connection twise if there is an exeption, I can't have finally() without catch() right?
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Wrong. You can have either and you should have both. But only close the connection in the finally. You should be logging the exceptions in some way in the catch clauses.

Is there a network between your database and your Java process? That could be the cause of the variance. The speed of transfer will depend on the network bandwidth and load, and also how busy your DB server is. I'm not really sure what you can do about that. It might be worth putting it to the boys in the performance forum, but essentially I don't think it's Java performance.

Jules
 
author and iconoclast
Posts: 24207
46
Mac OS X Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Alex Kravets:
I can't have finally() without catch() right?



You can, actually. try/finally without catch is a fairly common idiom for doing something that needs cleaning-up after.
 
author & internet detective
Posts: 41945
911
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I agree with Jules. It is due to the network transfer. It is best to request specific columns from a query and not return any data that isn't needed.

If you post the query, we can give more specific suggestions.
 
Let me tell you a story about a man named Jed. He made this tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic