CODEWITHSUNDEEP
javasqloracle-databasejdbc
Michael
Michael

Reputation: 87

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

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();

Upvotes: 1

Views: 4202

Answers (3)

Jens Schauder
Jens Schauder

Reputation: 81988

  1. Oracle can't pool connections, this has to be done on the client side

  2. You aren't closing any connections in the code example you posted

  3. 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.

  4. 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)

  5. 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.

Upvotes: 2

APC
APC

Reputation: 146349

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.

Upvotes: 1

HarshaKA
HarshaKA

Reputation: 165

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.

Upvotes: 0

Related Questions