GeeCON Prague 2014*
The moose likes JDBC and the fly likes SQL query takes too long to execute? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query takes too long to execute?" Watch "SQL query takes too long to execute?" New topic
Author

SQL query takes too long to execute?

Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
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?



All right brain, you don't like me and I don't like you, but let's just do this one thing so I can get back to killing you with beer.<br /> <br />- Homer Simpson
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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

Joined: Jan 24, 2001
Posts: 476
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

Joined: Aug 02, 2004
Posts: 823
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

Joined: Jul 08, 2003
Posts: 24187
    
  34

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.


[Jess in Action][AskingGoodQuestions]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30586
    
154

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL query takes too long to execute?