I recently noticed that when our application does an SQL query to the Oracle database, it always takes at least 200 ms to execute. It doesn't matter how simple or complex the query is, the minimum time is about 200 ms. We're using the Oracle Managed Data Access driver for Oracle 11g.
I then created a simple console application to test the connection. I noticed that if I create the connection like in the example below, then every cmd.ExecuteReader
method takes the extra 200 ms (opening the connection)?
using (OracleConnection con = new OracleConnection(connStr))
{
con.Open();
OracleCommand cmd = con.CreateCommand();
...
}
The connection state is always Closed
when creating the connection like this (shouldn't it be open if the connections are pooled?).
If I open the connection at the start of the program and then pass the opened connection to the method, the cmd.ExecuteReader
takes about 0-5 ms to return. I've tried to add Pooling=true
to the connection string but it doesn't seem to do anything (it should be the default anyway).
Does this mean that the connection pooling is not working as it should? Or could there be any other reason why the cmd.ExecuteReader
takes the extra 200 ms to execute?
The problem is almost the same as in this issue, except that we're using Oracle Connection pooling is slower than keeping one connection open
Is your database remote and the delay caused by network? In this case connection pooling works but the problem is that there is always a TCP communication roundtrip (and not even TNS packet). Unfortunately this happens with every
Open
call.Managed data access implementation communicates in different way so the overhead takes place only at the very first
Open
call, then theOpen
method is free.After a lot of testing and research I finally figured out where the extra 200ms comes from: my virtual computer's network adapter. I'm using VMWare Player and the connection was configured to "NAT" mode. When I changed the connection to "Bridged" mode the latency was removed.