在 MSSQL (TSQL) 中，我可以指定位于连接字符串中但不影响池的上下文变量吗？
为了进一步解释这一需求，请考虑以下场景：
在严重依赖 TRIGGERS 的遗留系统中，我们需要提供某种类型的令牌（我们称之为“SessionID”）以插入到某些安全性中日志表。此令牌是在应用程序服务器中用 C# 创建的，并将传递到所有 SQL 命令中。
不幸的是，这个关于触发器的要求是不可更改的
因此，因为我有权修改连接字符串，所以我可以（并且已经成功证明我可以）使用“应用程序名称”令牌来提供这段信息。
string connectionString = string.Format("SERVER=sql.example.com; "
+ "DATABASE=someDB; User ID=xyz; Password=123; Application Name={0}", sID);
既然这有效，那么有什么问题呢？
问题很简单...上面的方法有效，但是由于我们有数千个用户...我们的连接池被破坏了（因为连接池是基于连接字符串创建的...基本上，我需要连接池基于除应用程序名称属性之外的所有内容）。
那么，您知道我可以如何：
- 在连接字符串中设置一个不包含在池中的属性。
- 以其他方式为此连接设置上下文属性，这对性能不会造成太大影响。
作为旁注...我可以立即打开连接并创建一个具有单个值的临时表：
SELECT 12345 AS SessionID INTO #context
但这似乎太过分了！
To explain the need further, consider this scenario:
In a legacy system that relies heavily on TRIGGERS, we need to provide a token of some sort (let's call it "SessionID") to be inserted into some security log tables. This token is created in C# in the application server, and will be passed into all SQL commands.
SADLY, THIS REQUIREMENT ON TRIGGERS IS UNCHANGEABLE
So, because I have access to modify the connection string, I can (and have successfully proven that I can) use the "Application Name" token to provide this piece of information.
string connectionString = string.Format("SERVER=sql.example.com; "
+ "DATABASE=someDB; User ID=xyz; Password=123; Application Name={0}", sID);
Since this works, what's the problem?
The problem is very simple... the above works, but since we have thousands of users... our connection-pool is destroyed (since connection pools are made based on the connection string... basically, I need the connection pool to be based on everything except the Application Name property).
So, do you know of how I can either:
- Set a property in the connection string that will NOT be included in pooling.
- Set a contextual property for this connection some other way that is not incredibly heavy on performance.
As a side-note... I could open the connection right away and create a temp table with a single value:
SELECT 12345 AS SessionID INTO #context
But that seems incredibly overkill!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您需要执行此操作，而不是依赖连接字符串，而是依赖临时表
SET CONTEXT_INFO/CONTEXT_INFO()可用于将某些任意数据与当前会话/连接关联起来（并且在触发器中可用）。如果服务器可以接受从用户/时间等的某种组合创建变量，您可以从 登录触发器，在这种情况下，您根本不需要更改连接机制。
You would need to execute this rather than rely on the connection string, but rather than a temp table
SET CONTEXT_INFO/CONTEXT_INFO()can be used to associate some arbitrary data against the current session/connection (and is available in triggers).If its acceptable for the server to create the variable from some combination of user/time etc you could assign
CONTEXT_INFOautomatically from within a logon trigger, in which case you would not need to change your connection mechanic at all.我的理解是，单独的连接池是使用连接字符串作为键创建的，因此对连接字符串的任何上下文修改都将不可避免地影响池化
我之前注意到 SqlConnection 类上的 WorkstationId 属性 - 这可能是引入一些上下文化的技术？
My understanding is that separate connections pools are created using the connection string as a key, so any contextual modification to the connection string will inevitably affect pooling
I have noticed the WorkstationId property on the SqlConnection class before - that might be a technique to introduce some contextualization?
好的，经过大量测试后，我想出了一个解决方案。
在做了一些基准测试之后，我发现连接池比不使用池快 50 倍...但是...不使用池仍然每个连接只花费 0.005 秒。
我愿意忍受纳秒级的性能损失，以获得我们需要的安全日志记录。
哦，顺便说一句 - 如果当前所有连接都在使用，连接池就会有很大的性能限制，因为排队的第 101 个人将不得不等待......所以，在某些情况下...连接池比不使用池慢 1,000 倍！
OK, so after doing much testing, I've come up with a solution.
After doing some benchmarking, I was able to see that connection pooling is 50 times faster than not using pooling... but... not using pooling still only took 0.005 seconds per connection.
I'm willing to live with the nano-second performance loss to get the security logging that we need.
Oh, as a side point - connection pooling has MAJOR performance limitations if all of the connections are being used at the moment, since the 101th person in line will have to wait... so, in SOME instances... connection pooling is 1,000 times slower than not using pooling!
不幸的是，连接字符串中的所有内容都将被用于池化（我认为他们将使用 GetHashCode() 来检查字符串是否已经在池中）。
Unfortunately everything in the connection string will be taken for the pooling (i think they will use GetHashCode() to check if the string is already in the pool).