Re: What happened to SqlConnection.BeginOpen?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

While there is no built-in support for an async open, you can fake it with a
BackgroundWorker thread. However, I doubt if that will solve your problem.
Once the pool is full, it means that there are no more connections to
use--of course you can bump the size of the pool. If you system can handle
the load, you can make the pool virtually any size.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

"Chris Mullins" <cmullins@xxxxxxxxx> wrote in message
news:OpFPQvA5GHA.3840@xxxxxxxxxxxxxxxxxxxxxxx
Hi Bill,

Nope, if it ever existed, it was dropped. There is no async open in
ADO.NET (at least not yet).

Open should return very quickly once the connection is initially opened
and closed.

The issue we're having is that we're a high-throughput server handling
thousands of requests per second that come in over Windows Sockets. All of
our operations are running on IOCP callback threads.

The "block once we hit the limit" means we can never queue up transactions
beyond the size of the ADO.NET connection pool - which just isn't an
option. We handle LOTS of transactions per second right now...

We currently do ADO.NET synchronously, as no databases except SQL Server
support Async I/O with ADO.Net. This means we play quite a few games in
terms of queuing up work requests and managing the number of parallel
operations. We want this managment code to disappear, as it causes
un-necessary context switches and means we have more threads running than
we really should.

We hit a brick wall though due to the lack of an async way to open a
database connection - this means that once we need to hit the DB we're
stuck blocking for an indefinite amount of time once the connection pool
is exhausted.

As a high performance Socket Server using IOCP, this means we very quickly
get lots of threads (about 1000) blocked waiting for a connection to open.
This sucks, and we can't figure out a way around it. In terms of moving to
async, we need to be all-or-nothing. It doesn't do any good to be async in
95% of the places if that last 5% causes our threads to stall...

If the pool is full, you'll block (and eventually timeout) while waiting
for a connection.

Yup. This is why I really want the "BeginOpenConnection" method or a
workaround. I need a way to put my thread back into the IOCP pool while we
wait for a DB Connection to become available. When the connection becomes
available, a callback (preferable on an IOCP thread) would then happen and
we could continue processing the reqeust.

You haven't created your own pooling mechanism have you?

In order to work around this, I'm thinking about it.

We've already got a rich connection management infrastructure for managing
non ADO.Net server connections (mostly connections to Active Directory,
LDAP, and NTLM servers), and I was thinking of extending that for use with
ADO.Net.

I could create an IOCP Port, and bind the connections to that so that as
they were checked into and out of the pool, I could perform the callbacks
I was describing earlier.


--
Chris Mullins, MCSD.NET, MCPD:Enterprise
http://www.coversant.net/blogs/cmullins



"William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxxx> wrote in message
news:%23dqLmeA5GHA.1256@xxxxxxxxxxxxxxxxxxxxxxx

I don't endorse MARS. It causes more problems that it fixes--at least in
my experience.

Never depend on early builds, beta or CTP docs or feature sets. They can
(and always are) pared down prior to RTM.

I have a long section in my new book on Async ops that might help...
it'll be out in early November--that's only 5 weeks away.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

"Chris Mullins" <cmullins@xxxxxxxxx> wrote in message
news:%236HQ3h24GHA.1460@xxxxxxxxxxxxxxxxxxxxxxx
We're building some very high performance database applications, and are
trying to use all of the new Async ADO.Net 2.0 features.

The problem we're having is that we can't find a
SqlConnection.BeginOpen/EndOpen method. We can execute everything using
the BeginExecuteCommand, but without the BeginOpen on the connection we
end up with lots of deadlocked threads, all waiting for
SqlConnection.Open.

We looked briefly into MARS for doing this, as that might be an option,
but we really prefer the more standard Async stuff as it maps much
easier into working with other database provides such as Oracle. To do
this with MARS, we would still need this BeginEnd methodology.

What's frustrating is that early Whidbey builds, and many old ADO.Net
2.0 articles, refer to these methods so they existed at one point. These
methods were obviously removed during the .NET 2.0 RTM process, which
seems really strange.

Any ideas how to work around this and actually do highly scalable I/O
using the Async infrastructure in ADO.NET 2.0?

--
Chris Mullins MCSD.Net, MCPD Enterprise
http://www.coversant.net/blogs/cmullins







.

Relevant Pages

  • Re: What happened to SqlConnection.BeginOpen?
    ... beyond the size of the ADO.NET connection pool - which just isn't an option. ... as no databases except SQL Server ... support Async I/O with ADO.Net. ... As a high performance Socket Server using IOCP, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Async TCPClients
    ... Do you mean NOT to use the Async callback to reestablish the async? ... if there will be a limited number of client connections, ... If you were to use a synchronous loop you could spawn your own Thread ... RPC to complete before it can establish a connection", ...
    (microsoft.public.dotnet.general)
  • Re: What happened to SqlConnection.BeginOpen?
    ... waiting for a connection. ... I don't endorse MARS. ... I have a long section in my new book on Async ops that might help... ... we would still need this BeginEnd methodology. ...
    (microsoft.public.dotnet.framework.adonet)
  • bluetooth XP SP2 Cannot Set BT COM port
    ... can not change Active Sync Connection. ... The connection manager of ASync only ... shows standard COM ports which all are configured com ports ... but not the incomming ports from the PDA. ...
    (microsoft.public.pocketpc.activesync)
  • Re: Random database connectivity failure
    ... We can’t find any problems with the pooling, that it uses to many resources. ... pointing to its not a pool problem. ... The server is only used for this web-application, ... white paper on handling the connection pool on my website. ...
    (microsoft.public.dotnet.framework.adonet)