Report Builder 3.0多嵌套过滤SQL Where子句优化求助

我需要用Report Builder 3.0查询数据库，任务是查找对应日期范围内有特定事件的人员数据，且每位人员的日期范围各不相同。我写了包含AND/OR组合的嵌套过滤SQL，但当人员数量超过5000（实际要处理约80000人）时查询超时。

以健身房场景举例：管理层要生成会员及使用报告，提供会员编号和对应活跃日期范围，需获取活动详情。我写了两个查询：

Query 1 #

SELECT
  Member.Name
  ,Member.Address
  ,Visit.VisitDate
  ,Visit.ArrivalTime
  ,Visit.DepatureTime
  ,Visit.PersonalTrainerUsage
FROM
  Member
  INNER JOIN Visit
    ON Member.MemberID = Visit.MemberID
WHERE
  Member.MemberID IN (@MemberIDs)
  OR Visit.VisitID IN (@VisitIDs)

Query 2 #

SELECT
  Member.MemberID
  ,Visit.VisitID
FROM
  Member
  INNER JOIN Visit
    ON Member.MemberID = Visit.MemberID
WHERE
  Member.MembershipState IN ('WA', 'OR', 'CA', 'ID', 'NV', 'UT', 'AZ', 'NM')
  AND(
    (MemberID = 12345
    AND ((Member.StartDate >= '2021-01-05'
        AND Member.StartDate <= '2023-05-31')
      OR (Visit.VisitDate >= '2021-01-05'
        AND Visit.VisitDate <= '2023-05-31')))
    OR
    (MemberID = 23456
    AND ((Member.StartDate >= '2020-09-16'
        AND Member.StartDate <= '2022-12-31')
      OR (Visit.VisitDate >= '2020-09-16'
        AND Visit.VisitDate <= '2022-12-31')))
    OR
    (MemberID = 34567
    AND ((Member.StartDate >= '2019-02-14'
        AND Member.StartDate <= '2023-01-31')
      OR (Visit.VisitDate >= '2021-02-14'
        AND Visit.VisitDate <= '2023-01-31')))
)

现寻求更高效的实现方式，望提供优化建议。

1. 用表值参数/临时表存储会员-日期映射 #

80000条会员+日期范围的条件用OR拼接会彻底拖垮查询性能，改用表值参数或临时表存储映射关系，通过JOIN替代OR条件是核心优化方案：

表值参数实现示例 #

先在数据库中定义表值参数类型（仅需执行一次）：

CREATE TYPE MemberDateRange AS TABLE (
    MemberID INT PRIMARY KEY,
    StartDate DATE,
    EndDate DATE
);

然后在Report Builder的查询中使用该参数：

-- @MemberDateRanges为传入的表值参数，存储所有会员ID及对应日期范围
SELECT
    M.Name,
    M.Address,
    V.VisitDate,
    V.ArrivalTime,
    V.DepatureTime,
    V.PersonalTrainerUsage
FROM
    Member M
INNER JOIN Visit V ON M.MemberID = V.MemberID
INNER JOIN @MemberDateRanges R ON M.MemberID = R.MemberID
WHERE
    (M.StartDate BETWEEN R.StartDate AND R.EndDate)
    OR (V.VisitDate BETWEEN R.StartDate AND R.EndDate)
AND M.MembershipState IN ('WA', 'OR', 'CA', 'ID', 'NV', 'UT', 'AZ', 'NM');

如果Report Builder不支持表值参数，可改用临时表：先批量插入会员-日期数据到#MemberDateRange临时表，再执行上述JOIN逻辑。

2. 针对性创建复合索引 #

给查询的过滤、JOIN字段创建复合索引，直接提升数据检索效率：

• 给Member表建索引：

  CREATE NONCLUSTERED INDEX IX_Member_MemberID_State_StartDate 
  ON Member(MemberID, MembershipState, StartDate) 
  INCLUDE (Name, Address);
  

• 给Visit表建索引：

  CREATE NONCLUSTERED INDEX IX_Visit_MemberID_VisitDate 
  ON Visit(MemberID, VisitDate) 
  INCLUDE (ArrivalTime, DepatureTime, PersonalTrainerUsage);
  

3. 拆分OR逻辑避免索引失效 #

OR条件会导致数据库无法高效利用索引，可拆分为两个独立查询后用UNION ALL合并，同时排除重复数据：

-- 分支1：会员起始日期在对应范围内
SELECT
    M.Name,
    M.Address,
    V.VisitDate,
    V.ArrivalTime,
    V.DepatureTime,
    V.PersonalTrainerUsage
FROM
    Member M
INNER JOIN Visit V ON M.MemberID = V.MemberID
INNER JOIN @MemberDateRanges R ON M.MemberID = R.MemberID
WHERE
    M.StartDate BETWEEN R.StartDate AND R.EndDate
AND M.MembershipState IN ('WA', 'OR', 'CA', 'ID', 'NV', 'UT', 'AZ', 'NM')

UNION ALL

-- 分支2：访问日期在对应范围内（排除已在分支1的数据）
SELECT
    M.Name,
    M.Address,
    V.VisitDate,
    V.ArrivalTime,
    V.DepatureTime,
    V.PersonalTrainerUsage
FROM
    Member M
INNER JOIN Visit V ON M.MemberID = V.MemberID
INNER JOIN @MemberDateRanges R ON M.MemberID = R.MemberID
WHERE
    V.VisitDate BETWEEN R.StartDate AND R.EndDate
AND NOT (M.StartDate BETWEEN R.StartDate AND R.EndDate)
AND M.MembershipState IN ('WA', 'OR', 'CA', 'ID', 'NV', 'UT', 'AZ', 'NM');

4. 替换超大IN子句 #

Query 1中IN (@MemberIDs)如果传入80000个值，会导致查询计划劣化，直接用临时表/表值参数的JOIN方式替代，性能提升显著。

5. 修正原查询的语法问题 #

• 字符串类型的州代码、日期常量必须加单引号，比如'WA'、'2021-01-05'
• 列之间的逗号不能省略，比如Query 2中Member.MemberID后需添加逗号

内容的提问来源于stack exchange，提问作者HawaiianShirts