Do multiple open/close connections to SQl via Java effect performance?

Question

I am working on a basic code to access a query in a database for an alert system. I understand that the database at work (Oracle based) automatically creates a pool of connections and I wanted to know if I connect, execute the query and close the connection every 5-15seconds would the performance drop dramatically and was it the correct way to do it or would I have to leave the connection open until the infinite loop is closed?

I have someone at work telling me that closing the connection would result in the database having to lookup a query each time from scratch but if I leave it open the query will be in a cache somewhere on the database.

     ResultSet rs1 = MyStatement.executeQuery(QUERY_1);
     while (rs1.next()){
     // do something
     }
     rs1.close();

     rs1 = MyStatement.executeQuery(QUERY_2);
     while (rs1.next()){
     // do something
     }
     rs1.close();

Oracle doesn't automatically create a pool of connections. And the above clode doesn't close any connection. It only closes result sets. — JB Nizet, Mar 03 '13 at 10:30
I agree with @JBNizet : your sample code doesn't demonstrate the question you're asking. So, voting to close. — APC, Mar 03 '13 at 11:01

score 2 · Accepted Answer · edited May 23 '17 at 11:57

Oracle can't pool connections, this has to be done on the client side
You aren't closing any connections in the code example you posted
Opening a connection is a rather slow process, so use either a fixed set of connections (typically the set has size one for things like fat client applications) or a connection pool, that pools open connections for reuse. See this question for what might be viable options for connection pools: Connection pooling options with JDBC: DBCP vs C3P0 If you are running on an application server it will probably already provide a connection pooling solution. check the documentation.
closing stuff like the resultset in your code or a connection (not in your code) should be done in a finally block. Doing the closing (and the neccessary exception handling correct is actually rather difficult. Consider using something like the JdbcTemplate of Spring (http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html)
If you aren't using stuff like VPN (virtual private database) Oracle will cache execution plans of statements no matter if they come from the same connection or not. Also data accessed lately is kept in memory to make queries accessing similar data fast. So the performance decrease is really coming from the latency of establishing the connection. There is some overhead on the DB side for creating connections which in theory could affect the performance of the complete database, but it is likely to be irrelevant.

Actually Oracle *can* do connection pooling on the server side. It's called "shared server": http://docs.oracle.com/cd/E11882_01/server.112/e25789/dist_pro.htm#i19036 (but it's usually more efficient to do the pooling on the client side) — , Mar 03 '13 at 11:56
I know, but this kind of pooling is something completely different. — Jens Schauder, Mar 03 '13 at 14:45

score 1 · Answer 2 · answered Mar 03 '13 at 11:00

Every time a client connects to the database that connection has to be authenticated. This is obviously an overhead. Furthermore the database listener can only process a limited number of connections at the same time; if the number of simultaneous connection attempts exceeds that threshold they get put into a queue. That is also an overhead.

So the general answer is, yes, opening and closing connections is an expensive operation.

score 0 · Answer 3 · edited Mar 03 '13 at 10:47

0

It is always beneficial to be using DB Connection pools especially if you are using a Java EE app server. Also using the connection pool which is out of the box in the Java EE app server is optimal as it will be optimized and performance tested by the App server development team.

edited Mar 03 '13 at 10:47

Arjan Tijms

37,782
12
108
140

answered Mar 03 '13 at 10:32

HarshaKA

165
1
1
8

Do multiple open/close connections to SQl via Java effect performance?

3 Answers3