5

My application have performance issues, so i started to investigate this from the root: "The connection with the database".

The best practices says: "Open a connection, use it and close is as soon as possible", but i dont know the overhead that this causes, so the question is:

1 -"Open, Use, Close connections as soon as possible is the best aproach using ODP.NET?"

2 - Is there a way and how to use connection pooling with ODP.NET? I thinking about to create a List to store some connections strings and create a logic to choose the "best" connection every time i need. Is this the best way to do it?

Aaron
  • 55,518
  • 11
  • 116
  • 132
Ewerton
  • 4,046
  • 4
  • 30
  • 56
  • 2
    Are you talking about a client/server application? Or a three-tier application? Are you stating that you have profiled the application and determined that opening and closing database connections is a bottleneck? Or are you just guessing that the performance problems may be database related? – Justin Cave Apr 12 '12 at 15:25
  • 1
    Whose best practice is this? Opening a connection to the database is an expensive and time-consuming operation. Therefore the best advice is to keep connections open and in use for as long as possible. – APC Apr 12 '12 at 16:59
  • Justin. Yes, It is an Asp.Net application, so it is client/server, with 3 or 4 tiers, the "Open Connection Operation" is not the bottleneck but i guess it contribute to the performance decrease (if dont used correctly). – Ewerton Apr 12 '12 at 17:22
  • APC. According to the Microsoft Ado.Net team it is a best practice. See the anwser below – Ewerton Apr 12 '12 at 17:23

3 Answers3

6

Here is a slide deck containing Oracle's recommended best practices:

http://www.oracle.com/technetwork/topics/dotnet/ow2011-bp-performance-deploy-dotnet-518050.pdf

You automatically get a connection pool when you create an OracleConnection. For most middle tier applications you will want to take advantage of that. You will also want to tune your pool for a realistic workload by turning on Performance Counters in the registry.

Please see the ODP.NET online help for details on connection pooling. Pool settings are added to the connection string.

Another issue people run into a lot with OracleConnections is that the garbage collector does not realize how truly resource intensive they are and does not clean them up promptly. This is compounded by the fact that ODP.NET is not fully managed and so some resources are hidden from the garbage collector. Hence the best practice is to Close() AND Dispose() all Oracle ODP.NET objects (including OracleConnection) to force them to be cleaned up.

This particular issue will be mitigated in Oracle's fully managed provider (a beta will be out shortly)

(EDIT: ODP.NET, Managed Driver is now available.)

Christian Shay

Oracle

Christian Shay
  • 2,570
  • 14
  • 24
1

The ODP.NET is a data provider for ADO.NET. The best practice for ADO.Net is Open, Get Data (to memory), close, use in memory data. For example using a OracleDataReader to load data in a DataTable in memory and close connection.

[]'s

Cezar Schwartz
  • 11
  • 1
0

For a single transaction this is best but for multiple transaction where you commit at the end this might not be the best solution. You need to keep the connection open until the transaction either committed or rolled back. How do you manage that and also how do you check the connection still exist in that case?(ie network failure) There is ConnectionState.Broken property which does not work at this point.

user1810535
  • 109
  • 6