将十进制和字符串传递给SqlParameter的正确方法

Question

我正在为某些项目为SQL Server创建可重用的数据访问层。我有一个问题，找到一个合适的方式来传递.NET decimal作为查询参数。最明显的办法：

cmd.Parameters.AddWithValue("@P0", parameterValue); 

作品几乎不错。该值正确存储在DB中，但SqlClient从实际值推断出确切类型，因此当应用程序传递12345.678时，它将@P0声明为NUMERIC(8,3)。当完全相同的查询与其他值一起使用时，将声明该值的精度和小数位数。由于我还不知道的原因，从SQL Server来看，这些查询并不相同，并且它们中的每一个在sys.dm_exec_cached_plans中都有单独的条目。 string也有同样的问题 - 每个长度都有一个不同的查询计划。

我们的一些应用程序每分钟写入不少记录，每个记录的5-10 decimal值范围变化很​​大（某些传感器数据，某些货币值）。几乎每个INSERT都得到它自己的查询计划，并且在一天之后，缓存了超过100k个相同查询计划的版本，并且占用了几GB的RAM。

我的想法是将所有可能的类型合并成几个任意选择的类型。对于decimal，当精度低于20时，我将它设置为20.当精度大于20时，我将它设置为最小值，比实际精度大4（例如24,28,32,36）。缩放（最小2，然后2步）和字符串（最小128，然后2的幂）类似。这种方式似乎可以控制问题 - 我已经看到每个查询大概有100个变体，而不是100k，而SQL Server使用内存作为缓冲池，而不是用于一些无用的计划。

这种方法可能出错吗？使用SqlClient和使用decimals加载的查询时，是否有更好的方法可以控制查询计划缓存？

名单的事情我不能做：

• 使用存储过程 - 因为这DAL的目的是创造了很多的DBMS（SQL服务器，MS Access中，火鸟，甲骨文此刻）抽象层。
• 强迫我的队友以某种方式通过每个变量的实际类型 - 因为这将是残酷的。
• 垃圾这个DAL，使用普通的旧DbProviderFactory和创建参数，就像是1990年 - 因为这DAL也处理查询创建的SQL方言，DB结构创建等
• 垃圾这个DAL和使用像NHibernate的“正确”的DAO - 因为这里没有发明。
• 把这个问题带到dba.stackexchange.com - 因为它是SqlClient的问题和我使用它的方式，而不是SQL Server本身。

Answer 1

您正确地确定了此处的关键问题，因为让SQL驱动程序为您确定参数的大小，比例和精度。您通过限制精度/尺度/参数大小来减少查询计划的想法也是正确的。对于字符串，可以将大小设置为预期的最大值：当查询将具有较大大小限制的参数字符串与较小大小的varchar s进行比较时，它应该正常工作。为小数选取几个精度也听起来不错。看看你是否可以把它降到只有一对精度/规模：这可能是可能的（它对我们的项目工作非常有效）。如果你设法做到这一点，那么你就可以为每个SQL查询选择一个查询计划。