SNOW-1714154: Using unique query tags in connection string

[ivanjt-srai]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of .NET driver are you using?
   4.1.0

2. What operating system and processor architecture are you using?
   Windows 11

3. What version of .NET framework are you using?
   E.g. .net framework 4.5.2 or .net standard 2.0
   .NET 8

4. What did you do?

I upgraded Snowflake nuget to 4.1.0 and have been seeing this error and would like a way to resolve this issue we are facing.

I understand post 4.0+ upgrade , connection pooling is now connection string driven, ie, for eveyr unique connection string used , a connection pool is created , and default values are used for minPoolSize ,maxPoolSize and WaitingForIdleSessionTimeout.

But in my application , we require the use of query tags , and query tags are almost uniquely created for each request and each query that we send to snowflake for query processing. So that means a new connection pool is created for almost every query that executes. This will lead to pool exhaustion later on , which we have seen often happening, that results in an exception from snowflake with the error "ConnectionPoolTimeout" , and as of now I have rolled back to 3.1.0 version , and currently figuring out a way to have this query tag implemented in the latest version.

We used to earlier run ALTER SESSION command to set the query tag , we run this command before every main query that gets executed for a request. But this has posed quite a few problems, including high queueing times , duplicate queries being seen executed in the query history, so we rolled back that way of implementation, and saw that 4.1.0 introduced supporting query tags in connection string. But since its unique, its posing problems with connection pooling, please help !

Thanks.

5. What did you expect to see?

Expected connection pooling to ignore query tags, and manage pooling accordingly.

6. Can you set logging to DEBUG and collect the logs?

[sfc-gh-dszmolka]

hi and thank you for raising this concern. i'm wondering if something similar to how multi-statement queries are enabled, could provide a mitigation and relief for you, using the DbCommand.CreateParameter() method?

I mean something like this for multi-statement query, seen in the above examle:

using (DbCommand cmd = conn.CreateCommand())
{
    // Set statement count
    var stmtCountParam = cmd.CreateParameter();
    stmtCountParam.ParameterName = "MULTI_STATEMENT_COUNT";
    stmtCountParam.DbType = DbType.Int16;
    stmtCountParam.Value = 3;
    cmd.Parameters.Add(stmtCountParam);
    cmd.CommandText = "CREATE OR REPLACE TABLE test(n int); INSERT INTO test values(1), (2); SELECT * FROM test ORDER BY n;
    DbDataReader reader = cmd.ExecuteReader();
    do
    {
        if (reader.HasRow)
        {
            while (reader.Read())
            {
                // read data
            }
        }
    }
    while (reader.NextResult());
}

just something like this instead:

    var queryTagParam = cmd.CreateParameter();
    queryTagParam.ParameterName = "QUERY_TAG";
    queryTagParam.DbType = DbType. String;
    queryTagParam.Value = "MyStringToTagQueries";
    cmd.Parameters.Add(queryTagParam);
    cmd.CommandText = "SELECT * FROM test ORDER BY n;
    DbDataReader reader = cmd.ExecuteReader();
..

of course we still need to investigate the issue you reported, i'm just trying to provide a mitigation if possible, to unblock you.
Would it be possible to try this to see if it works as a workaround?

[ivanjt-srai]

thats interesting, let me try that out and get back, thanks!

[sfc-gh-dszmolka]

Also I forgot about SnowflakeDbCommand.QueryTag, see the Connecting docs:

To set QUERY_TAG on the statement level you can use SnowflakeDbCommand.QueryTag.

thats something else to try.

[ivanjt-srai]

do you have an example when using Dapper to execute queries?

[sfc-gh-dszmolka]

Had a quick internal sync with a team, and one of my colleagues had a great remark. The QUERY_TAG must be a differentiator, because otherwise if different QUERY_TAGged connections get pooled into the same Pool, then you might get a connection out of the pool with a different query tag, which inadvertently will tag the specific query with a tag you did not expect to tag to it.

So a different query_tag should make the connection to go into a different pool, thus current behaviour is expected and documented. I also made a small docs PR to document it even better (#1035)
This part (QUERY_TAG being part of the connectionstring + entire connectionstring is used to determine Pool) is planned to remain unchanged for now.

for Dapper, unfortunately I cannot provide examples - it is a third party library to us, and Snowflake does not manage or support it. Perhaps in the Dapper repo, some hints could be available.

[sfc-gh-dszmolka]

closing this issue for now but if you require further help, please comment and can reopen.