Best Architecture for Win App with Multiple (hundreds) Database Connections RRS feed

  • Question

  • Looking for some insight, experience, and suggestions.

    Using Visual C++ 6.0, SQL Server 2005/2008 for a traditional Windows App - this cannot change, for now, so please don't tell me, "use .NET" - yeah, I get it. :)

    Consider that we have a multithreaded application using ADO to connecto to a SQL Server database.  There could be hundreds of worker threads that need to connect to the database and perform queries.

    1. Is it better to have each thread create its own connection or pass in a pointer to an existing connection?
    2. If it is better for each thread to create its own connection, are there any potential issues with possibly making hundreds of simultaneous connections?


    Monday, June 14, 2010 5:20 PM

All replies

  • Do not create idle connections. Idle connections take away server side resources and may slow the server down. You probably won't have hundreds of CPUs to actually use the hundreds of connections at the same time . Open a connection when you actual need to do database work, and close it as soon as you finished reading/writing data. This will return the connection to the connection pool. 

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Monday, June 14, 2010 6:38 PM
  • 1. what you can do is you can set a configurable number of allowed connections in your code.

    2. Each time when a connection would be needed you will create a new connection spawning a new thread from your thread pool(size of the thread pool would be the max no of connections allowed) and dedicate that thread for the connection with the database.

    3. In between if you find that a new connection is needed and you have one idle thread(which has completed its job), assign that idle thread along with its connection to perform the db operation.

    4. If no idle thread is found, spawn a new thread from thread pool ensuring that thread count doesn't exceed with MAX no. of threads allowed. If no. of threads reaches the threshold value, wait for some thread to be idle.

    5. You can use semaphore constructs to ensure configurable number of connections from thread pool.

    Note- Number of allowed connections is always a judiciary call and you have to decide a number which can match with your infra and network load.

    Let me know you have any queries regarding if this flow.

    Monday, June 14, 2010 6:59 PM
  • Use ADO COnnection pool diligently, close them explicitly so that the unused connection does not creep up in memory.

    You can set Max and Min numbers of connection pool in config files.

    Also use thread pool while creating threads.

    Tuesday, June 15, 2010 6:05 AM
  • Depends on what your threads are doing.

    If they're database intensive then hundreds of threads probably isn't a great idea as they'll be all getting in each others way.

    The way connection pooling works, so long as the connection strings are exactly the same then you can just close a connection and it will pool and be re-used by the next thread opening a connection.

    Maybe you also want to consider the repository pattern.

    Tuesday, June 15, 2010 8:56 AM
  • Tim,

          Yes, Connection pooling is meant for that. As Andy suggested usage of connection pooling, I think with repository pattern would be an ideal option.




    Tuesday, June 15, 2010 12:46 PM