Throttling connections, custom connection pooling




what are the proper design patterns for throttling sql server command
execution/the number of connections (concurrent)?

I have a very sound understanding of the ADO.NET/SqlConnection internals,
and I do understand that ADO.NET SqlConnection fully encapsulates a
connection pooling through its internal pooling mechanism, based on the
settings in the connection string.

I am building a fairly asynchronuous application, not a UI based - it does
not matter that badly when a command executes, I just need to make sure that
they all execute. Additionally, I think I may need some sort of priority
queue - different classes of sql commands should be "scheduled" with
different priorities (not just first come first serve). I don't see any
particular good way to do that just with the build in connection pooling
(other than using multiple pools using different connection strings, but
that's not what I want, because the behaviour would not be want I want).

The application is basically a queueing application. For example, I want to
make sure that a "send" operation is always possible and prevent several
concurrent receive operations from clogging up all connections. So basically
different priorities for different types of commands. Probably I also have
some long running queries and don't want several of these running at the same

Should I just go with OOTB pooling here - or am I overengineering the stack
I am building when manually implementing a custom pooling mechanism for SQL
server connections?

What I was thinking of is a manual pooling mechanism using something like
InputQueue<DatabaseConnection> (one can be found in the WCF stack,
System.ServiceModel.Channels.InputQueue, NOT public), and then do something

a) BeginGetConnection (BeginDequeue on the InputQueue)
b) EndGetConnection (this is an async callback)
c) BeginExecute SQL
d) End Execute SQL
e) return connection to my "pool"

The "GetConnection" part would be responsible for the throttling.

I am seeing that this is difficult to implement (read: difficult to get
bulletproof correct _and_ performant), locking and contention in the custom
connection pool management only one issue.

Any recommendations welcome.



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question