Re: Global data concurrent access ?

Jon wrote:
Michael Petrotta <mpetrotta@xxxxxxxxx> wrote:
Jon wrote:
Next in line: how expensive is making a database connection these days?

That may have been a rhetorical question, but I happen to have just
written a little test to figure out how pooled versus unpooled
connections performed. The code below is executing the same simple
stored procedure 1000 times, from a 3.4GHz Xeon box to a 3.6GHz Xeon
box about four network hops away.

No pooling 1000 iterations. Elapsed time: 00:00:12.9217096
Pooling 1000 iterations. Elapsed time: 00:00:01.0624864

That's about 12ms per round-trip for a non-pooled connection, and 1ms
per for a pooled connection (I was trying to prove that pooling didn't
produce an order of magnitude performance improvement, and I was
obviously wrong).

Out of interest, what happens to the figures if:

1) You're on the same box?

No pooling 1000 iterations. Elapsed time: 00:00:06.0936720
Pooling 1000 iterations. Elapsed time: 00:00:00.2499968

2) You're only one network hop away?

No pooling 1000 iterations. Elapsed time: 00:00:12.7683600
Pooling 1000 iterations. Elapsed time: 00:00:00.2904176

Interesting. The results are repeatable, but it's not a perfect test
(in particular, the client for #2 is a much slower laptop).

If I take the results at face value, it seems to say that network speed
affects pooled connections (makes sense; there's not much connection
setup and teardown with pooled connections. It's just the time taken
to get the query and results over the wires).

I think the speed of my laptop is affecting the non-pooled test; it's
taking time to have ADO.NET set up and tear down the connection.
Unfortunately, our network architecture is such that it's hard to have
two powerful desktops closely connected.

(The speed of pooled connections in general surprised me, when I first
ran this test. I'd assumed network latency would give me round-trip
times around 10-20ms. Pings to the server are also returning
sub-millisecond times. Remember modems and their 200ms pings?)

Michael

.

Relevant Pages

  • Re: Global data concurrent access ?
    ... No pooling 1000 iterations. ... Elapsed time: 00:00:06.0936720 ... setup and teardown with pooled connections. ...
    (microsoft.public.dotnet.framework)
  • Re: Global data concurrent access ?
    ... No pooling 1000 iterations. ... Elapsed time: 00:00:12.9217096 ... That's about 12ms per round-trip for a non-pooled connection, ...
    (microsoft.public.dotnet.framework)
  • Re: Question about Connection Pooling in ASP
    ... you almost have to deliberately turn off connection pooling in order ... As long as you are talking about storing a string in Application, ... pooling is used for all connections using that string. ... using connected recordsets, instead of utilizing GetString, GetRows ...
    (microsoft.public.inetserver.asp.db)
  • Re: How to close connection pool?
    ... Simply open connections with pooling disabled--or as suggested when ... Microsoft MVP ... pool preventing it until the user exits the application. ... so I need to set pooling to false for that action. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: How should I connect via DBD::Oracle to efficiently obtain 2000+ simultaneous connections?
    ... Here are a few options off the top of my head: ... Pooling via SQLRelay ... connections, just reduces the number of connections required. ... to take some load off of your database server. ...
    (perl.dbi.users)