时间戳

首页 > 解决方案 > 如何优化具有多个 OR、AND、IN 语句的查询？

sql - 如何优化具有多个 OR、AND、IN 语句的查询？

问题描述

我有一个如下的 SQL 查询：

    Declare @ConnectionType int = 5, 
    @UserId int = 2

    select * from CallDetails 
    Where ((@ConnectionType = 0 AND CallDetails.DeviceType IN (0,1,2,3,4,5,7,8))
            OR (@ConnectionType = 1 AND CallDetails.DeviceType = 4)
            OR (@ConnectionType = 2 AND CallDetails.DeviceType IN (0,1,2,3,7))
            OR (@ConnectionType = 3 AND CallDetails.DeviceType = 5)
            OR (@ConnectionType = 4 AND CallDetails.DeviceType IN (0,1,2,3,4,7))
            OR (@ConnectionType = 5 AND CallDetails.DeviceType IN (4,5))
            OR (@ConnectionType = 6 AND CallDetails.DeviceType IN (0,1,2,3,5,7))
            OR (@ConnectionType = 7 AND CallDetails.DeviceType IN (8))
            OR (@ConnectionType = 8 AND CallDetails.DeviceType IN (0,1,2,3,7,8))
            OR (@ConnectionType = 9 AND CallDetails.DeviceType IN (5,8))
            OR (@ConnectionType = 10 AND CallDetails.DeviceType IN (4,8))
            OR (@ConnectionType = 11 AND CallDetails.DeviceType IN (0,1,2,3,4,8))
            OR (@ConnectionType = 12 AND CallDetails.DeviceType IN (0,1,2,3,5,8))
            OR (@ConnectionType = 13 AND CallDetails.DeviceType IN (4,5,8)) 
            OR (@ConnectionType = 14 AND CallDetails.DeviceType IN (0,1,2,3,7,4,5))
            OR @ConnectionType IS NULL)

查询的另一部分是：

            AND (@UserId IS NULL OR @ConnectionType IN (1,3,5,7,9,10,13)

            OR (@ConnectionType = 0 AND (CallDetails.DeviceType IN (4,5,8) OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 2 AND ((CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 4 AND (CallDetails.DeviceType = 4 OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 6 AND (CallDetails.DeviceType = 5 OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 8 AND (CallDetails.DeviceType = 8 OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 11 AND (CallDetails.DeviceType IN (4,8) OR (CallDetails.UserId = @UserId)))
            OR (@ConnectionType = 12 AND (CallDetails.DeviceType IN (5,8) OR (CallDetails.UserId = @UserId))) 
            OR (@ConnectionType = 14 AND (CallDetails.DeviceType IN (4,5) OR (CallDetails.UserId = @UserId)))
        )

@ConnectionType是多个设备的组合，并在此基础DeviceType上确定。将来一旦添加任何其他设备，@ConnectionType组合将增加，依此类推。这个查询也被多次使用Store Procedures。如何优化此查询？

标签： sqlsql-serveroptimizationquery-optimization

解决方案

正如我所提到的，这看起来使用查找表会更好。你的表可能看起来像这样简单（注意我没有添加索引、外键约束等，但你可能想要/需要它们）：

CREATE TABLE dbo.ConnectionLookup (ConnectionType int,
                                   DeviceType int);

INSERT INTO dbo.ConnectionLookup (ConnectionType,
                                  DeviceType)
VALUES(0,0),
      (0,1),
      (0,2),
      (0,3),
      (0,4),
      (0,5),
      (0,7),
      (0,8),
      (1,4),
      ...
      (14,0),
      (14,1),
      (14,2),
      (14,3),
      (14,4),
      (14,5),
      (14,7);

然后，您可以改为JOIN在查找表上执行 a：

DECLARE @ConnectionType int = 5;
        --@UserId int = 2; --Commented out, as never used.

SELECT {Your Columns}
FROM dbo.CallDetails CD
     JOIN dbo.ConnectionLookup CL On CD.DeviceType = CL.DeviceType
WHERE CL.ConnectionType = @ConnectionType;

然而，这有点猜测，因为缺乏样本数据和预期结果，但应该（不过）让你走上正确的道路

推荐阅读