Open connection overhead

[damon.cognito]

I have been examining timings in dbmonitor due to slowness in the app. Each time a query is run the following items appear:

Code: Select all

Open Connection (47 - 63ms)
Exeute: SET Search_Path TO public (47 - 62ms)
Close Connection (0 - 15ms)

Not much of an overhead but if you run 10 queries against an instance of the Entity (for example inside a using) it repeats these for each query. So, in this example, if the queries take 125ms to prepare and execute, we get:

query time: 1250ms
connection overhead: 940 - 1400ms
total: 2190 - 2650ms

Is this to do with Linq, Postgresql or the Entity? Is there any way to persist the connection so the open, execute and close are only carried out once?

Using Postgresql 8.4, dotconnect V5.80, C# visual studio 10.

[Shalex]

Is there any way to persist the connection so the open, execute and close are only carried out once?

Entity Framework opens/closes a connection for each interoperation with a database. But if you open the connection manually, it stays in the opened state until it is closed explicitly.

Example of opening connection for ObjectContext:

Code: Select all

  using (var conext = new MyContext()) {
    conext.Connection.Open();

DbContext:

Code: Select all

  using (var conext = new MyContext()) {
     ((IObjectContextAdapter)сonext).ObjectContext.Connection.Open();