如何使用多个 OR AND IN 语句优化查询?

Question

我有一个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。如何优化此查询？

Answer 1

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

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：

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;

然而，这有点猜测，因为缺乏样本数据和预期结果，但应该(无论如何(让你走上正确的道路

Answer 2

您有以下几个优化选项： 1. 在 where 子句的列上添加索引，这应该会加快查询速度 2. 使用连接类型、设备类型创建一个帮助表(关联表(并连接它 3. 调整存储过程并使用动态查询，如下所示：

DECLARE @ConnectionType INT = 0,
@UserId INT = 2,
@DeviceTypeString NVARCHAR(100) = NULL
IF @ConnectionType = 0
SET @DeviceTypeString = N'0,1,2,3,4,5,7,8'
IF @ConnectionType = 1
SET @DeviceTypeString = N'4'
IF @ConnectionType = 2
SET @DeviceTypeString = N'0,1,2,3,7'
IF @ConnectionType = 3
SET @DeviceTypeString = N'5'
IF @ConnectionType = 4
SET @DeviceTypeString = N'0,1,2,3,4,7'
IF @ConnectionType = 5
SET @DeviceTypeString = N'4,5'
IF @ConnectionType = 6
SET @DeviceTypeString = N'0,1,2,3,5,7'
IF @ConnectionType = 7
SET @DeviceTypeString = N'8'
IF @ConnectionType = 8
SET @DeviceTypeString = N'0,1,2,3,7,8'
EXEC sp_executesql N'SELECT * FROM CallDetails AS cd WHERE (@1 IS NULL OR @1 = @1) AND (cd.DeviceType IN (SELECT [Value] FROM STRING_SPLIT(@2, '','')) OR cd.UserID = @3)',
N'@1 INT, @2 NVARCHAR(100), @3 INT',
@ConnectionType,
@DeviceTypeString,
@UserID

我不想固执，并调整了我的例子来与sp_executesql一起工作;-(

Answer 3

您可以创建一个包含两列(连接类型、设备类型(的表，这些列对每种连接类型都有多条记录。然后将呼叫详细信息与此表联接。