存储过程日期参数筛选器-如果为Null则忽略

Question

我在存储过程中使用以下SQL，如果参数为空，则不按日期进行筛选。

WHERE (Allocated >= ISNULL(@allocatedStartDate, '01/01/1900') 
       AND Allocated <= ISNULL(@allocatedEndDate,'01/01/3000'))
AND
(MatterOpened >= ISNULL(@matterOpenedStartDate, '01/01/1900') 
 AND MatterOpened <= ISNULL(@matterOpenedEndDate, '01/01/3000'))

在处理大量记录时，这会对性能产生任何影响吗？

有更好的方法吗？

记录数量-约500k

Answer 1

或者让查询优化器拥有它：

WHERE ( @allocatedStartDate is NULL or Allocated >= allocatedStartDate ) and
   ( @allocatedEndDate is NULL or Allocated <= @allocatedEndDate ) and
   ( @matterOpenedStartDate is NULL or MatterOpened >= @matterOpenedStartDate ) and
   ( @matterOpenedEndDate is NULL or MatterOpened <= @matterOpenedEndDate )

请注意，这在逻辑上并不等同于您的查询。最后一行使用了MatterOpened列，而不是Allocated列，因为我认为这是一个印刷错误。

如果性能确实是个问题，您可能需要考虑添加索引和更改存储过程，以便根据参数执行不同的查询。至少可以分为：无筛选器，仅在"已分配"上筛选，仅在MatterOpened上筛选，同时在两列上筛选。

Answer 2

在很多情况下，动态SQL对您来说可能更好，而不是试图依赖优化器来缓存NULL和非NULL参数的良好计划。

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT 
... 
WHERE 1 = 1';
SET @sql = @sql + CASE WHEN @allocatedStartDate IS NOT NULL THEN 
    ' AND Allocated >= ''' + CONVERT(CHAR(8), @allocatedStartDate, 112) + '''';
-- repeat for other clauses
EXEC sp_executesql @sql;

不，维护起来并不有趣，但每个变体都应该在缓存中有自己的计划。您将希望使用不同的设置来测试"针对特定工作负载进行优化"和数据库级别参数化设置。哎呀，刚刚注意到2005年。为未来（以及任何还没有停留在2005年的读者）记住这些。

还要确保使用EXEC sp_executesql而不是EXEC。

Answer 3

与其检查查询中的变量是否为null，不如在存储过程开始时检查变量，并将值更改为默认

SELECT
   @allocatedStartDate = ISNULL(@allocatedStartDate, '01/01/1900'),
   @allocatedEndDate = ISNULL(@allocatedEndDate,'01/01/3000'),
   @matterOpenedStartDate = ISNULL(@matterOpenedStartDate, '01/01/1900'),
   @matterOpenedEndDate = ISNULL(@matterOpenedEndDate, '01/01/3000')

Answer 4

也许是这样的：

DECLARE @allocatedStartDate DATETIME=GETDATE()
DECLARE @allocatedEndDate DATETIME=GETDATE()-2
;WITH CTE AS
(
    SELECT 
        ISNULL(@allocatedStartDate, '01/01/1900') AS allocatedStartDate,
        ISNULL(@allocatedEndDate,'01/01/3000') AS allocatedEndDate 
)
SELECT
    *
FROM
    YourTable
    CROSS JOIN CTE
WHERE (Allocated >= CTE.allocatedStartDate 
       AND Allocated <= CTE.allocatedEndDate)
AND
(MatterOpened >= CTE.allocatedStartDate 
 AND Allocated <= CTE.allocatedEndDate)