0

I use a cs convenience class to handle my sql queries. It is basic, you instantiate by specifying the db name, and it only manages one command/datareader duo at once.

public static void      Connect     (string DBName)
{
    string connectionString =
        "Server=serverNaaaaaaaaame;Database=" 
        + DBName 
        + ";Integrated Security=SSPI;";
    try
    {
        if (cnn != null)
            cnn.Close ();
        cnn = new SqlConnection (connectionString);
        cnn.Open ();
    }
    catch (Exception e)
    {
        string aaaaaa = e.Message;
    }

}
public void     Command     (string command)
{
    try
    {
        if (drd != null)
            drd.Dispose ();
        if (cmd != null)
            cmd.Dispose ();
        cmd = new SqlCommand (command, cnn);
        //cmd.CommandTimeout = 300;
        drd = cmd.ExecuteReader ();
    }
    catch (Exception e)
    {
        string aaaaaa = e.Message;
    }
}

As is, in the multi-threaded parts of my code, I often get timeouts. It seems to be due to my amount of concurrent connection being too high. (If I enter debug upon timeout and try to execute a query in mssqlsms it hangs until I stop the debug.)

I've been told about the Max Pool Size option to set in the connection string, however I can not see what it affects.

Does it change the quantity of SqlConnections I can plug to the same database?

Does it change the amount of concurrent SqlCommand and subsequent sqlDataReaders I can use at once? On the same SqlConnection? Something else?

Do I have to specify it in all my connection strings?

Does it have any effect if the database is already in use in another soft?

  • The problem is that you open a connection in one method and never close it until another connection attempt is made. This means that locks are kept for a long time and one connection may be blocking another. Don't do that. Use *one* connection for *only as long as you need it*. Connection pooling means that you *won't* pay any performance penalty for this. Check [What is the Managed C++ equivalent to the C# using statement](https://stackoverflow.com/questions/338950/what-is-the-managed-c-equivalent-to-the-c-sharp-using-statement). It shows how to define, use and dispose a connection – Panagiotis Kanavos Nov 13 '18 at 14:10
  • @PanagiotisKanavos Are you saying I should close the connection once I've built the datareader? I have a close function in this class too. It would kind of make sense since the connections are only used once (at least in this context). –  Nov 13 '18 at 14:28

1 Answers1

0

In multi-threading environment the recommended scenario is

  • open SqlConnection (this creates or acquires existing connection in the pool)
  • process the data with readers/commands
  • close SqlConnection ASAP to release the pool connection for use by other threads)

According MSDN, the MaxPoolSize limits the number of concurrent connections in the pool (for each unique connection string).

Does it change the quantity of SqlConnections I can plug to the same database?

Yes, when the count of open/used SqlConnection > MaxPoolSize the application is waiting until a pool connection will be freed.

Does it change the amount of concurrent SqlCommand and subsequent sqlDataReaders I can use at once? On the same SqlConnection? Something else?

Yes, as it said above, the amount of concurrent connections is limited by a MaxPoolSize. However, SqlDataReader depends on SqlConnection, you can use (sequentially) several readers at the same connection (see also MARS option for multiple recordsets).

Do I have to specify it in all my connection strings?

Normally, you need only one connection string used for pooling. Otherwise you need to manage multiple pools.

Does it have any effect if the database is already in use in another soft?

Yes, your queries will affect DBMS performance and even may lock some processing doing by other application at the same database. However, this problem is not specific of multi-threading.

serge
  • 992
  • 5
  • 8
  • 1): OK so the timeouts did come from there. 2): I still can use only one command/datareader by sqlconnection, is that it? 3): Well, I use the same connection string, but what I'm asking is that if it changes anything whether or not the option is set after the first time. 4): Does the setting of the option have an impact on the way it works in another soft, if the base was already opened in it? –  Nov 13 '18 at 14:53
  • 1/ the running long/blocked queries may produce timeouts, too 2/ yes 3/ some options may be set by SqlConnection properties regardless the string 4/ the impact doesn't depend on the fact of using MaxPoolSize, it depend on your querying. Normally, it's the work of DBA to find this kind of problems. – serge Nov 13 '18 at 15:13
  • 1): All queries were nearly instant in mssqlsms, and since it now works, that was the issue. 3): Understood, I would have left it anyway, just to be sure. 4): I'd assume mssqlsms and the sqlconnection object use the same pool (cue the hangs). As such, the pool size would be determined by the last query to specify it. I don't believe lowering the pool size would cull any surnumerary connection but from now on I'll make sure to avoir running mssqlsms while my soft is running. Thank you for your help. –  Nov 13 '18 at 16:28