Can a single SQL Server connection be shared among tasks executed in parallel

netparallelismsql server

Can a single SQL Server connection be shared among tasks executing in parallel? Each task would dequeue an item from a ParallelQueue, instantiate a SqlClient command, and insert a row into the table. Or does each command-instantiating-task require its own dedicated connection to the database? Can the connection(s) be instantiated and opened at the top of the method and then closed when the parallel tasks have been completed, or should each task instantiate, open, and close its connection?

Best Answer

A SQLServer Connection can be shared by multiple tasks executing in parallel, e.g. threads in a C# program or requests in an app server. But most use scenarios would require you to synchronize access to the Connection. A task will have to wait for the connection if another task is using it. By the time you build a shared connection mechanism that does not break or become a performance constraint for your parallel tasks, you have likely built a connection pool.

Another way to look at it is, yes you can share a pool of connections among a set of threads and let the connection pool handle dispensing connections and connection state cleanup. Microsoft's .net data access had very capable connection pooling as of 3 to 4 years ago when I last used it. With connection pooling you don't have to spend a lot of extra time opening and reopening connections. You don't have to hold open more connections than you actually need to use in a large long running application. You reuse connections when they are available, and automatically open a new connection when needed, automatically close them when they are no longer needed, e.g. after an inactivity period.

Connection Pooling may be a good answer. Just holding a connection for each task may be a good answer.

Related Solutions

SQL Server and MongoDB – Can SQL Server and Mongo Be Used Together?

You've run into a problem that many have before you...a database optimized for reading is seldom good for write efficiency and vice versa. One approach that has evolved from this read-write impediment is CQRS (Command Query Responsibility Segregation). Despite Wikipedia linking the two together CQRS and CQS are technically different. CQS just demands that a method either make a change (command) or ask information (query) never both.

CQRS takes it a step further and specifies that you have a separate model for Queries and Commands. This single step enables things like separating your read and write database. Which is what you want to do.

I can't say that I'm an expert on Mongo or configuring it to work with SQL Server. But from my understanding, people use Mongo as a denormalized view of their transactional database. Updating Mongo from the transactional db might come down to running a SQL Agent. Or having a separate service to poll the database.

An even better alternative would be to have your Command service fire an event whenever an update is made. You would then have a service that listened for that event and updated the MongoDB with that information. That is the fundamental approach to Event Sourcing (search for Event Sourcing on the page).

Greg Young, one of the thought leaders in the DDD world is currently writing a book in the Fowler Signature Series on CQRS called Event-Centric (it used to be called CQRS). Fowler has written a post on his bliki describing the approach

C# – Clients with multiple proxy and multithreading callbacks

I created a SingleTon class that can be used to Manage this Proxy.

/// <summary>
/// Singleton Pattern
/// <para>ConnectionHelper provides connection to the Server.</para>
/// </summary>
public sealed class ConnectionHelper
{
    private static readonly ConnectionHelper instance = new ConnectionHelper();
    /// <summary>
    /// Provides a readonly Instance to "ConnectionHelper" class.
    /// </summary>
    public static ConnectionHelper Instance
    {
        get
        {
            return instance;
        }
    }
    /// <summary>
    /// Provides access to IServer members.
    /// </summary>
    public ServerProxy ServerProxyInstance { get; set; }

    /// <summary>
    /// Gets or sets the peer vue communication URL.
    /// </summary>
    /// <value>The peer vue communication URL.</value>
    public string CommunicationURL
    {
        get;
        set;
    }

    .....
    ...
    ..
}

Best Answer

Related Solutions

SQL Server and MongoDB – Can SQL Server and Mongo Be Used Together?

C# – Clients with multiple proxy and multithreading callbacks

Related Topic