D
dlosch
Hi,
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
time.
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
like
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.
Thx,
Daniel
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
time.
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
like
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.
Thx,
Daniel