Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL query takes too long to execute?

 
Alex Kravets
Ranch Hand
Posts: 476
  • Mark post as helpful
  • send pies
  • 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?

 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • 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
  • 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?
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • 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
 
Ernest Friedman-Hill
author and iconoclast
Marshal
Pie
Posts: 24211
35
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • 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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34401
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic