• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database best practice

 
Gareth Baker
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have been trying to research best programing practices in relation to database queries and I have read a few websties which give the advice that you should always close Statements, PreparedStatements, ResultSet, etc before closing the connection and in doing so not to wait until the garbage collector decides to clean up these objects.

What facts are there to suggest that I will have better resource usage if I close statements and ResultSets before closing the connection? Has anyone seen a website i.e IBM, Sun, other that highly recommend this practice?
Is there any advantage to do the following?

Example:

 
Tempora Telora
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have written against sybase and oracle. Closing your the connection after executing the statement I believe is a large part of controlling performance.

Here is a scenario. You have a small website where you have 20 users logged in at one time. If you didnt close your connection then that would be 20 concurrent connections to your db that are open.

vs

Only creating a connection when running a query/update/etc... against the db for the 20 users.

Great performance closing the connection after running your query.. Just be agile with your coding. If you know you are going to have to make a couple of query calls (for a single connection user) then try and do that in the same connection.
 
Tempora Telora
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have written against sybase and oracle. Closing your the connection after executing the statement I believe is a large part of controlling performance.

Here is a scenario. You have a small website where you have 20 users logged in at one time. If you didnt close your connection then that would be 20 concurrent connections to your db that are open.

vs

Only creating a connection when running a query/update/etc... against the db for the 20 users.

Great performance closing the connection after running your query.. Just be agile with your coding. If you know you are going to have to make a couple of query calls (for a single connection user) then try and do that in the same connection.
 
Gareth Baker
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Im not talking about closing the connection but rather do I get better resource usage by closing SQL Statements and or ResultSet before closing the connection?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34656
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Gareth Baker:
Im not talking about closing the connection but rather do I get better resource usage by closing SQL Statements and or ResultSet before closing the connection?

It depends on the database driver implementation. The only way to find out for sure is to try it under load on your system and do some profiling. That said, it is good practice to always close the resources. What if you change drivers or upgrade the database in the future.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34656
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to our JDBC/database forum.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic