scalability of single SqlConnection instance

[John A Grandy]

I have coded a data-access class in which is encapsulated all code which
makes calls ADO.NET via System.Data.SqlClient ( SqlDataAdapter.Fill() ,
SqlCommand.ExecuteReader() , etc. )

All public methods of these classes are thread safe.

My data-access class has a single private SqlConnection.

In my WinForms app, I always utilize a singleton to obtain a reference to
the single global instance of my data-access class , and therefore all app
code is talking to the databases via the same SqlConnection instance.

My app performs a fair amount of background processing which is
multi-threaded. So, all threads talk to the databases via a single
SqlConnection.

So far, I have encountered no performance problems , however I have only
tested with a handful of background processing tasks running simultaneously.
Potentially, there could be 100 threads running simultaneously.


Here are my questions :

Is a single SqlConnection sufficient for my scalability needs ?

Does ADO.NET implement any "under the hood magic" that would provide good
scalability for my single SqlConnection architecture ? e.g. a load
balanced connection pool within ADO.NET

Is there a 1:1 correspondence between my .NET SqlConnection instance and
ADO.NET's lower-level connection to my Sql Server database ?

Any other relevant information most appreciated.

Thank-you.

 

[Miha Markic]

Is a single SqlConnection sufficient for my scalability needs ?

No. There should be at least one SqlConnection per thread - it isn't thread
safe at all!

Does ADO.NET implement any "under the hood magic" that would provide good
scalability for my single SqlConnection architecture ? e.g. a load
balanced connection pool within ADO.NET

Yes, there is a connection pool at client side for SQL Server. You can
specify the min/max number of instances as well through connection string.

Is there a 1:1 correspondence between my .NET SqlConnection instance and
ADO.NET's lower-level connection to my Sql Server database ?

Since there is a connection pool - no. managed instance can reuse physicall
connection.

Any other relevant information most appreciated.

Do create a new SqlConnection instance and open it just before you need and
dispose it asap. Connection pool will work in the background for you.

 

[John A Grandy]

Hi Miha, and thanks for the response.

I think that technically my architecture is thread-safe ( since only a
single thread can make use of my single SqlConnection instance at once ) ,
but it's hardly a desirable way to do things.

However, I understand your points and I am going to change my architecture.

The reason I associated a single SqlConnection instance with my
db-access-class , and the reason I do not close the SqlConnection
immediately after each db-operation , is that it's my understanding that
it's relatively quite expensive to open / close SqlConnections. In fact ,
open/close SqlConnection tends to be the most expensive db operation ( on
average , compared to simple single-table read / write / update / delete ).
Since this is a WinForms app , I figured there was no reason to close the
SqlConnection after each use.

It sounds as if ADO.NET does some magic behind the scenes to keep one or
more low-level connections open , waiting to be used. However, for ADO.NET
to best manage the connection pool , I need to be opening my SqlConnection
just before use , and closing it just afterwards.

I did not wish all my threads to wait in line for a single SqlConnection.
Rather, at the C# level , I couldn't figure out how to implement an
architecture that would associate a single SqlConnection instance with each
thread.

Ok, so behind the scenes ADO.NET maintains a thread pool. Are the default
settings ok ? Or should I change them ? I need to architect for potential
of around 100 threads at once.

 

[William Vaughn \(MVP\)]

Ah, no. There is quite a bit of misinformation floating around about
connecting. It's why I spend so much time writing about it. The problem is,
many folks make the same generalizations about Windows Forms apps that they
make about ASP.NET and other SOA applications.
I agree that the SqlConnection class is not thread-safe. Not only that, but
it's half-duplex. That is, when you start an operation (execute a SELECT
query or execute a SP or an UPDATE), the operation must complete before
another can be started. Yes, you can consider a new "feature" (MARS) that
can permit multiple operations on a single connection, but it has so many
side-effects, I don't recommend it. I find it's far easier to simply open
another connection and keep going.
Yes, it's not free to create/open/close a Connection. While the Connection
pooling mechanism built into the SqlClient provider is efficient, it still
has to re-authenticate the user credentials and reset the connection
state--each time it's opened.
Yes, I agree, it's a good idea to open (one or more) connections in a
Windows Forms application and leave them open. Use a blocking serializer to
prevent multiple use (very much like the Connection Pooler). Generally, I
use one connection for SELECTs and (if necessary) another for UPDATES or
other DML queries.
Connections are not expensive AFA SQL Server--especially since Windows Forms
applications rarely try to open a few connections and the number of
applications on a single server is rarely more than a few hundred--the
server can support thousands without issue.

See Chapter 9 for more information...
hth

Hi Miha, and thanks for the response.

I think that technically my architecture is thread-safe ( since only a
single thread can make use of my single SqlConnection instance at once ) ,
but it's hardly a desirable way to do things.

However, I understand your points and I am going to change my
architecture.

The reason I associated a single SqlConnection instance with my
db-access-class , and the reason I do not close the SqlConnection
immediately after each db-operation , is that it's my understanding that
it's relatively quite expensive to open / close SqlConnections. In fact ,
open/close SqlConnection tends to be the most expensive db operation ( on
average , compared to simple single-table read / write / update /
delete ). Since this is a WinForms app , I figured there was no reason to
close the SqlConnection after each use.

It sounds as if ADO.NET does some magic behind the scenes to keep one or
more low-level connections open , waiting to be used. However, for
ADO.NET to best manage the connection pool , I need to be opening my
SqlConnection just before use , and closing it just afterwards.

I did not wish all my threads to wait in line for a single SqlConnection.
Rather, at the C# level , I couldn't figure out how to implement an
architecture that would associate a single SqlConnection instance with
each thread.

Ok, so behind the scenes ADO.NET maintains a thread pool. Are the default
settings ok ? Or should I change them ? I need to architect for
potential of around 100 threads at once.

No. There should be at least one SqlConnection per thread - it isn't
thread safe at all!


Yes, there is a connection pool at client side for SQL Server. You can
specify the min/max number of instances as well through connection
string.


Since there is a connection pool - no. managed instance can reuse
physicall connection.


Do create a new SqlConnection instance and open it just before you need
and dispose it asap. Connection pool will work in the background for you.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[John A Grandy]

Hi Bill, and thanks for the response.

Use a blocking serializer to prevent multiple use (very much like the
Connection Pooler).

Do you mean lock on the specific SqlConnection instance being used for the
operation ? e.g. if a WinForms app has two open SqlConnection instances ,
one for SELECT and one for UPDATE/DML , then lock on the appropriate
SqlConnection for the duration of each slice of data-access code ?

What about my specific situation where I have a WinForms app with
potentially 100 background processing threads ? Let's say I move from a
single "perma-open" SqlConnection to a pair of SqlConnections ( one for
SELECT , one for everything else ) ... have I impoved my scalability much ?

Suppose I dedicate a specific SqlConnection to each SqlClient method I
utilize ( SqlCommand.ExecuteNonQuery() , SqlDataAdapater.Fill() ,
SqlCommand.ExecuteReader() , etc. ) ... this would allow me to factor
serialization at a lower-level ( e.g. inside wrapper methods for those
SqlClient methods ) . What do you think of this strategy ?

Ch9 of your book I assume. Which one is this ?

Ah, no. There is quite a bit of misinformation floating around about
connecting. It's why I spend so much time writing about it. The problem
is, many folks make the same generalizations about Windows Forms apps that
they make about ASP.NET and other SOA applications.
I agree that the SqlConnection class is not thread-safe. Not only that,
but it's half-duplex. That is, when you start an operation (execute a
SELECT query or execute a SP or an UPDATE), the operation must complete
before another can be started. Yes, you can consider a new "feature"
(MARS) that can permit multiple operations on a single connection, but it
has so many side-effects, I don't recommend it. I find it's far easier to
simply open another connection and keep going.
Yes, it's not free to create/open/close a Connection. While the Connection
pooling mechanism built into the SqlClient provider is efficient, it still
has to re-authenticate the user credentials and reset the connection
state--each time it's opened.
Yes, I agree, it's a good idea to open (one or more) connections in a
Windows Forms application and leave them open. Use a blocking serializer
to prevent multiple use (very much like the Connection Pooler). Generally,
I use one connection for SELECTs and (if necessary) another for UPDATES or
other DML queries.
Connections are not expensive AFA SQL Server--especially since Windows
Forms applications rarely try to open a few connections and the number of
applications on a single server is rarely more than a few hundred--the
server can support thousands without issue.

See Chapter 9 for more information...
hth

 

[William Vaughn \(MVP\)]

See >>>

Hi Bill, and thanks for the response.


Do you mean lock on the specific SqlConnection instance being used for the
operation ? e.g. if a WinForms app has two open SqlConnection instances ,
one for SELECT and one for UPDATE/DML , then lock on the appropriate
SqlConnection for the duration of each slice of data-access code ?

What about my specific situation where I have a WinForms app with
potentially 100 background processing threads ? Let's say I move from a
single "perma-open" SqlConnection to a pair of SqlConnections ( one for
SELECT , one for everything else ) ... have I impoved my scalability much
?

Suppose I dedicate a specific SqlConnection to each SqlClient method I
utilize ( SqlCommand.ExecuteNonQuery() , SqlDataAdapater.Fill() ,
SqlCommand.ExecuteReader() , etc. ) ... this would allow me to factor
serialization at a lower-level ( e.g. inside wrapper methods for those
SqlClient methods ) . What do you think of this strategy ?

There are a number of (complex) issues here relating to throughput (how fast
can these queries be serviced by SQL Server, do the operations compete with
each other for the same pages thus creating lock contention or hotspots in
the database (and more)) so any suggestions I make here must be taken in
context of what I know about your system from afar.

Ch9 of your book I assume. Which one is this ?

It's the one in my sig--Hitchhiker's Guide 7th Edition...
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Miha Markic]

Hi John,

Hi Miha, and thanks for the response.

I think that technically my architecture is thread-safe ( since only a
single thread can make use of my single SqlConnection instance at once ) ,
but it's hardly a desirable way to do things.

However, I understand your points and I am going to change my
architecture.

The reason I associated a single SqlConnection instance with my
db-access-class , and the reason I do not close the SqlConnection
immediately after each db-operation , is that it's my understanding that
it's relatively quite expensive to open / close SqlConnections. In fact ,
open/close SqlConnection tends to be the most expensive db operation ( on
average , compared to simple single-table read / write / update /
delete ). Since this is a WinForms app , I figured there was no reason to
close the SqlConnection after each use.

SqlConnecitons aren't expensive. They have few cpu cycles of overhead but
that's it. The underlying physicall connections are expensive but those are
managed by connection pool for you.

It sounds as if ADO.NET does some magic behind the scenes to keep one or
more low-level connections open , waiting to be used. However, for
ADO.NET to best manage the connection pool , I need to be opening my
SqlConnection just before use , and closing it just afterwards.

True, but that's not the problem at all. In fact it is a good practice since
it guarantees you thread safety (and even nested connections scenario) at
almost non-existing cost.

I did not wish all my threads to wait in line for a single SqlConnection.
Rather, at the C# level , I couldn't figure out how to implement an
architecture that would associate a single SqlConnection instance with
each thread.

You might use ThreadStaticAttribute or just create a variable inside your
thread method.

Ok, so behind the scenes ADO.NET maintains a thread pool. Are the default
settings ok ? Or should I change them ? I need to architect for
potential of around 100 threads at once.

Check out these .net help topics:
SQL Server Connection Pooling (ADO.NET)
SqlConnection..::.ConnectionString Property
And funny, default Max Pool Size is 100 But perhaps you might tweak Min
Pool Size (default is 0). You have to figure out what settings are most
suitable for you...

 

[John A Grandy]

I could be wrong , but it seems as if yourself and Bill Vaughn disagree on
how expensive it is to dynamically create/open/close a SqlConnection on each
db-access. ( Note that by expensive I include not just the .NET overhead ,
but also the ADO.NET overhead. )

Bill seems to be of the opinion that in a WinForms app one need to design
around static open SqlConnection(s) , while you seem to be of the opinion
that dynamic create/open/close is always a best practice : whether ASP.NET
or WinForms.

Or am I misinterpreting your statements ?

 

[William Vaughn \(MVP\)]

This is not the first time folks have disagreed with me. I (and a number of
developers in the field) have found the "few lines of code" can take a bit
more time depending on the circumstances (like how long it takes to
re-authenticate the credentials) and how long it takes to get the connection
cleaned out--each time it's reopened.

I could be wrong , but it seems as if yourself and Bill Vaughn disagree on
how expensive it is to dynamically create/open/close a SqlConnection on
each db-access. ( Note that by expensive I include not just the .NET
overhead , but also the ADO.NET overhead. )

Bill seems to be of the opinion that in a WinForms app one need to design
around static open SqlConnection(s) , while you seem to be of the opinion
that dynamic create/open/close is always a best practice : whether ASP.NET
or WinForms.

Or am I misinterpreting your statements ?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

John,

In my idea you start a discussion about a dead horse.

The common usage is in AdoNet (including Linq to SQL where this is done
automaticly) to close a database connection as soon as you know that it is
not used anymore in a procedure that contains only SQL statements.

This newsgroup was in 2002/2003 full of those discussions.

Cor

 

[John A Grandy]

Hi Cor, and thanks for the response.

Unfortunately, I was not around for those 2002/2003 discussions.

My experience is primarily with ASP.NET , and in that context I always
follow a pattern of create/open/use/close a local SqlConnection within my
data-access methods ( and I attempt to factor down to highest degree of
granularity ) .

However, now I am working on a WinForms app with a very high level of
background processing ( 100+ threads ). It actually strikes me as similar
to a web-app because at the data-access layer level the origin & lifetime of
a calling thread is immaterial ; what is material is that there are
potentially very many threads potentially all requiring data-access at
approximately the same time.

However, nevertheless I am interested in the most scalable architecture for
WinForms, and so I am seeking the advice of those who have built such.

Can you point me to some links where the heavyweights weigh in on this issue
?

 

[Miha Markic]

Hi John,

I could be wrong , but it seems as if yourself and Bill Vaughn disagree on
how expensive it is to dynamically create/open/close a SqlConnection on
each db-access. ( Note that by expensive I include not just the .NET
overhead , but also the ADO.NET overhead. )

Bill seems to be of the opinion that in a WinForms app one need to design
around static open SqlConnection(s) , while you seem to be of the opinion
that dynamic create/open/close is always a best practice : whether ASP.NET
or WinForms.

Or am I misinterpreting your statements ?

No, you are correct, I disagree with Bill on this particular issue. He wants
a to creata an instance of SqlConnection at the start of the application and
keep it, while I'd create it just before I need it and dispose it asap.
Why would I embark on seemingly more costly way? Here are three reasons out
of my head:
- the performance penality of creating a SqlConnection (.net managed object
that is) instance is practically non existing. Perhaps it is even better if
you take in the account GC - garbage collector. GC doesn't like long living
instances.
- doing it my way, the scalability isn't a problem, just execute as many
threads as you wish as long as you don't share same SqlConnection. Even if
you start with a single threaded app! You may change it to a multithreaded
one some day.
- you can't mistakenly execute two queries at the same time with the same
connection

At least this is how I see it.

 

[Cor Ligthert[MVP]]

Miha,

Or I understand you wrong or I have always understood Bill V wrong.

I had not the idea that beside the recordset Bill has written things in the
way you told he wrote.

Cor

 

[Cor Ligthert[MVP]]

http://groups.google.com/group/micr...net&q=close+connection&qt_g=Search+this+group