将小数和字符串作为 SqlParameter 传递的正确方法
我正在为某些项目创建可重用的 SQL Server 数据访问层。我在寻找将 .NETs decimal 作为查询参数传递的正确方法时遇到问题。最明显的方法：
cmd.Parameters.AddWithValue("@P0", parameterValue);
效果几乎很好。该值正确存储在数据库中，但 SqlClient 从实际值推断出确切的类型，因此当应用程序传递 12345.678 时，它将 @P0 声明为 NUMERIC(8,3)。当完全相同的查询与其他值一起使用时，将声明该值的精度和小数位数。由于我还不知道的原因，SQL Server 中的这些查询并不相等，并且每个查询在 sys.dm_exec_cached_plans 中都有单独的条目。 string 存在完全相同的问题 - 对于每个长度都有不同的查询计划。
我们的一些应用程序每分钟写入相当多的记录，每条记录都有 5-10 个十进制值，范围变化很大（一些传感器数据，一些货币值）。几乎每个 INSERT 都会获得自己的查询计划，一天后就会缓存同一查询计划的超过 10 万个版本，占用的 RAM 远远超过几 GB。
我的想法是将所有可能的类型合并为一些任意选择的类型。对于decimal，当精度低于20时，我将其设置为20。当精度大于20时，我将其设置为比实际精度大的能被4整除的最小值（例如24、28、32、 36）。类似的情况也适用于比例（最小 2，然后以 2 为步长）和字符串（最小 128，然后为 2 的幂）。这种方式似乎可以控制问题 - 我已经看到每个查询有大约 100 个计划变体，而不是 100k，并且 SQL Server 使用内存作为缓冲池，而不是一些无用的计划。
这种方法有可能出错吗？使用 SqlClient 和加载小数的查询时，是否有更好的方法来控制查询计划缓存？
我不能做的事情列表：
- 使用存储过程 - 因为此 DAL 的目的是为许多 DBMS（目前是 SQL Server、MS Access、Firebird、Oracle）创建抽象层。
- 强迫我的队友以某种方式传递每个变量的实际类型 - 因为那会很残酷。
- 扔掉这个 DAL，使用普通的旧 DbProviderFactory 并像 1990 年一样创建参数 - 因为这个 DAL 还处理 SQL 方言的查询创建、数据库结构创建等。
- 扔掉这个 DAL 并使用像 NHibernate 这样的“正确”DAO - 因为它不是在这里发明的。
- 将此问题带到 dba.stackexchange.com - 因为这是 SqlClient 和我使用它的方式的问题，而不是 SQL Server 本身的问题。
I am creating reusable Data Access Layer for SQL Server for some projects. I have a problem with finding a proper way to pass .NETs decimal as a parameter to query. Most obvious way:
cmd.Parameters.AddWithValue("@P0", parameterValue);
works almost good. The value is stored properly in DB, but SqlClient infers exact type from actual value, so when application passes 12345.678 it declares @P0 as NUMERIC(8,3). When exactly same query is used with other value, that value's precision and scale is declared. For a reason yet not known to me, from SQL Server those queries are not equal and each of them gets separate entry in sys.dm_exec_cached_plans. There is exactly same problem with string - for each length there is a different query plan.
Some of our applications write quite a few records per minute, each with 5-10 decimal values with wildly varying ranges (some sensor data, some currency values). Almost each INSERT gets it's own query plan and after a days there are more than 100k versions of the same query plan cached, taking well over few GBs of RAM.
My idea to deal with is to coalesce all possible types to a few arbitrary chosen types. For decimal, when precision is lower than 20 I set it to 20. When precision is greater than 20, I set it as smallest value divisible by 4 greater than actual precision (e.g. 24, 28, 32, 36). Similar goes for scale (minimum 2, then steps by 2) and strings (minumum 128, then powers of 2). This way seems to keep the problem under control - I've seen around 100 variants of plans for each query instead of 100k and SQL Server uses memory for buffer pool, not for some useless plans.
Is there any way this approach could go wrong? Are there better ways to keep query plans cache under control when using SqlClient and queries loaded with decimals?
List of things I can't do:
- Use stored procedures - because purpose of this DAL is to create abstract layer for many DBMSes (SQL Server, MS Access, Firebird, Oracle at the moment).
- Force my fellow teammates to somehow pass actual type with each variable - because that would be cruel.
- Trash this DAL, use plain old DbProviderFactory and create parameters like it's 1990 - because this DAL also handles query creation for SQL dialects, DB structure creation, etc.
- Trash this DAL and use "proper" DAO like NHibernate - because it was Not Invented Here.
- Take this question to dba.stackexchange.com - because it's a problem with SqlClient and the way I use it, not with SQL Server per se.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您正确地确定了这里的关键问题是让 SQL 驱动程序为您决定参数的大小、比例和精度。您通过限制参数的精度/比例/大小来减少查询计划数量的想法听起来也是正确的。对于字符串，您可以将大小设置为您期望的最大值：当您的查询将具有较大大小限制的参数字符串与较小大小的
varchar进行比较时，它应该可以正常工作。只选择小数点的几个精度听起来也是正确的。看看是否可以将其降低到只有一对精度/比例：这是可能的（对于我们的工作项目来说效果非常好）。如果您设法做到这一点，您将能够针对每个 SQL 查询使用单个查询计划。You correctly identified the key issue here as letting SQL driver decide the sizes, scales, and precisions on your parameters for you. Your idea of reducing the number of query plans by limiting precisions / scales / sizes of parameters also sounds correct. For strings, you can set the size to the largest value that you expect: it should work correctly when your query compares parameter strings with large size limits to
varchars of smaller size. Picking just a few precisions for decimals also sounds right. See if you can get it down to just one pair of precision/scale: it may be possible (it worked out very well for our projects at work). If you manage to do it, you'd be able to go to a single query plan per SQL query.