在 WHERE 子句中使用分支的 SELECT-query 的性能

Question

我有一个以下遗留 SP：

CREATE PROCEDURE dbo.get_orders_history
(
@FirstDt DATETIME2(6),
@LastDt DATETIME2(6),
@Class VARCHAR(12),
@PeriodType SMALLINT
)
AS
SET NOCOUNT ON
CREATE TABLE #BufferTable (OrderId INT)
INSERT  INTO #BufferTable
SELECT  DISTINCT
O.OrderId
FROM
BaseOrders O JOIN Classes C ON O.ClassId = C.ClassId
WHERE
(O.Changed = 0) AND
(C.ClassCode = @ClassCode) AND
(
(@PeriodType = 1 AND O.LastActionDateTime >= @FirstDt AND O.OrderDateTime < @LastDt) OR
(@PeriodType = 2 AND O.OrderDateTime >= @FirstDt AND O.OrderDateTime <= @LastDt)
)
OPTION(RECOMPILE);
SELECT  A.Column,
C.Column,
OB.Column1,
...
OB.Column10,
O.Column1,
...
O.Column100,
FROM    BaseOrders OB
JOIN #BufferTable IDL ON (OB.OrderId = IDL.OrderId)
JOIN Orders O ON (O.OrderId = IDL.OrderId)
JOIN Classes C ON (O.ClassId = C.ClassId)
ORDER BY 
O.OrderId
DROP TABLE #BufferTable
GO

现在要添加参数"PeriodType"，我怀疑这种创建分支的方式(WHERE-子句中的条件(是否有效。

SP 很少被调用，但返回很多行 (100K+(，所以我认为在这种情况下，SELECT 的选项重新编译是合理的解决方案。

SQL专家可以建议更有效的方法来实现这样的分支吗？

--编辑：我将澄清SP的当前生产版本没有参数"周期类型"，WHERE如下：

WHERE
(O.Changed = 0) AND
(C.ClassCode = @ClassCode) AND
(O.LastActionDateTime >= @FirstDt AND O.OrderDateTime < @LastDt)

我的目标是在当前 SP 中实现两种类型的日期范围类型，而不会对性能造成最小损失。

Answer 1

您是否尝试从初始插入中删除OR，然后删除否定？下面是一个代码片段。

CREATE TABLE #BufferTable (OrderId INT, LastActionDateTime datetime, OrderDateTime datetime)
INSERT  INTO #BufferTable
SELECT  DISTINCT
o.OrderId
, o.LastActionDateTime
, o.OrderDateTime
FROM
BaseOrders O JOIN Classes C ON O.ClassId = C.ClassId
WHERE
(O.Changed = 0) AND
(C.ClassCode = @ClassCode);
delete p
from #BufferTable p
where not
(
(@PeriodType = 1 AND p.LastActionDateTime >= @FirstDt AND p.OrderDateTime < @LastDt) OR
(@PeriodType = 2 AND p.OrderDateTime >= @FirstDt AND p.OrderDateTime <= @LastDt)
)