今天早些时候，我正在研究一个Stored Procedure表现不佳的报告。我能够通过将包含variable十个或更少字符的字符串的静态变量从 更改为VARCHAR(MAX)来解决这个问题VARCHAR(10)。 我希望了解为什么会有所作为。
如有需要，可提供其他详细信息
原始查询比需要查看的要大得多，但我看到发生了以下事情，当将基础连接table到 a时，Key-Value Pair table我们返回的rows结果比实际需要的要多得多（在某些情况下，最终输出超过 140 亿行~60,000 行）并且我们有一些额外的操作（尤其是Lazy Table Spool）。该部分查询计划的图像如下：
我可以看到执行计划中一个奇怪的部分是如何@ProgramID variable处理的。此变量用作表[Column] = @ProgramID的一部分，并且是Column表的一部分。但是我们仍然在做一个而不是一个。Non-Clustered IndexVarChar(10)Inequality SearchEquality Search
parameter/variable将其从修改VarChar(Max)为 后VarChar(10)。execution time从几个小时下降到大约 30 秒，该计划的结构非常不同。从执行计划中查看类似的片段，我们没有返回几乎那么多的行，我们没有额外的操作并且我们正在执行equality searchon [Column] = @ProgramID（再次Column是表的一部分Non-Clustered Index并且是 a VarChar(10)）。
下面是一个简化的查询，它显示了一些这种行为（我们没有得到所有额外的步骤，但是 Seek Predicate 的区别仍然存在）：（粘贴计划）
DECLARE @CompanyID VarChar(10) = 'RxCRoads'
,@ClientID VarCHar(10) = 'Amgen'
,@ProgramID VarChar(MAX) = 'Foundation'
,@StartDate DATETIME = '2020-01-01'
,@EndDate DATETIME = '2020-12-07'
,@RxOnly INT = 9
SELECT PC.CompanyID,
PC.ClientID,
PC.ProgramID,
PC.PatientID,
PC.CaseID,
RxOnly.[Value] AS RxOnly
FROM PATIENTCASES PC
LEFT OUTER JOIN PatientCaseDetail RxOnly
ON RxOnly.CompanyId = PC.CompanyID
AND RxOnly.ClientId = PC.ClientID
AND RxOnly.ProgramId = PC.ProgramID
AND RxOnly.PatientID = PC.PatientID
AND RxOnly.CaseID = PC.CaseID
AND RxOnly.PatientCaseAdditionalElementId = @RxOnly
WHERE PC.CompanyID = @CompanyID
AND PC.ClientID = @ClientID
AND PC.ProgramID = @ProgramID
AND PC.CaseCreateDateTime >= @StartDate
AND PC.CaseCreateDateTime < @EndDate
只需将 更改为@ProgramID VarChar(MAX) = 'Foundation')即可@ProgramID VarChar(10) = 'Foundation'返回更精简的计划和更好的性能（即使在此特定查询中并不明显）。（粘贴计划）
数据类型大小的这种变化会导致执行计划的变化有什么特别的原因吗？@ProgarmId VarChar(8000)使用而不是@Program VarChar(10)运行 相同的查询与@Program VarChar(10). （粘贴计划）
我的猜测是，VarChar(MAX)由于它的大小，它有不同的比较规则，这只是一种状态VarChar(MAX)。但我不知道是否有人比我有更好/更具技术性的答案，所以我可以在未来更好地应用这一课。
问题
你有两件事对你不利：
一些进一步的阅读：
尤其是
在您更糟糕的计划中，有两个过滤器运算符：
每一个都是用来处理 MAX 类型的
@ProgramId。在您达到最大类型之前，参数的长度并不重要，只要它是正确的类型，例如varchar = varchar或nvarchar = nvarchar您可能还会看到
GetRangeThroughMismatchedType或GetRangeThroughConvert那里处理date\datetime不匹配：通过添加重新编译提示，您可能会发现一些整体缓解，但它不会解决您在 MAX 变量周围遇到的问题。
长度是优化器用来确定最佳执行计划的因素之一。
使用 MAX 时，如果 SQL Server 想要使用索引，它必须将等于您的变量转换为列。
我们可以
Compute Scalar在您的执行计划上看到运营商。它是一个轻量级的运算符，但使用Compute ScalarwithNested Loop会降低性能。因为每行来Nested Loop就去Compute Scalar操作。VARCHAR(N)VARCHAR(MAX)有点像和两种不同的数据DateTime类型Date。VARCHAR(MAX)是BLOB和VARCHAR(n)In-Row data。因此，如果您的列类型不是 VARCHAR(MAX)，SQL Server 需要转换您的变量以使用索引。Paul White有一篇深入的博客文章。但是，不包括你的例子。