Re: scalability of single SqlConnection instance

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

See >>>

"John A Grandy" <johnagrandy@g-mail-dot-com> wrote in message news:#lP8cuEdJHA.4684@xxxxxxxxxxxxxxxxxxxxxxx
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 ?
Yes.

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 ?
I think so... but it really depends on the other activity. And spin up another connection when needed. The issue here is that you're duplicating some (most?) of the Connection Pooling mechanism. The advantage to a static connection is that you can mange the server state including #temp and indexed #temp tables to improve query performance, create and manage server-side cursors which permit you to query data "live" as it changes on the server without having to download the whole rowset each time (Appendix B? of my book).

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 ?
Again, that might be worth looking at.

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 Vaughn (MVP)" <billva@xxxxxxxxxxxxxxx> wrote in message news:2050420B-34F6-4F1F-B166-802FAACC9B15@xxxxxxxxxxxxxxxx
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

"John A Grandy" <johnagrandy@g-mail-dot-com> wrote in message news:#ERcSd2cJHA.4492@xxxxxxxxxxxxxxxxxxxxxxx
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.


"Miha Markic" <miha at rthand com> wrote in message news:e7$1l5xcJHA.3792@xxxxxxxxxxxxxxxxxxxxxxx
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.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/


--
__________________________________________________________________________
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
____________________________________________________________________________________________





--
__________________________________________________________________________
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
____________________________________________________________________________________________



.

Relevant Pages

  • Pooled SQLConnection - Server Disconnection Recovery
    ... to access SQL Server. ... first SQLConnection fails with "General network error. ... According to what I've read up, once the first connection fails, the ... I catch the error, and try to create a new, second, SQLConnection. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: scalability of single SqlConnection instance
    ... Do you mean lock on the specific SqlConnection instance being used for the operation? ... Yes, you can consider a new "feature" that can permit multiple operations on a single connection, but it has so many side-effects, I don't recommend it. ... 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. ...
    (microsoft.public.dotnet.framework.adonet)
  • Convert SqlDataSource to SqlConnection
    ... Dim connection As New SqlConnection() ... Verify that the instance name is correct and that SQL Server is ... (provider: Named Pipes Provider, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Data access layer + sql connections
    ... > would be interested in hearing opinions from more experienced .NET ... > I am building a data access layer that encapsulates my business entities. ... > SqlConnection and issues the relevant SQL commands. ... and this transaction object should contain the connection. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Very strange SQL connection problem
    ... Try creating a *new instance* of SqlConnection and then open and after the ... > connection string again and got the second exception after ... > Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior ...
    (microsoft.public.dotnet.framework.adonet)