查询此数据的更有效方法？

Question

我有一个包含一些数据的表：

 ColA | ColB | ColC
------+------+------
  1   |  A   |  X
  2   |  A   |  Y
  3   |  B   |  Y
  4   |  C   |  Y
  5   |  C   |  Z
  6   |  D   |  Y
  7   |  D   |  Z

我想查询以获取 ColB 和 ColC 作为一对 匹配的所有行条件：

SELECT * FROM [Table] 
WHERE (ColB = A AND ColC = Y)
OR (ColB = B AND ColC = Y)
OR (ColB = C AND ColC = Y)
OR (ColB = D AND ColC = Z)

这应该返回第 2、3、4 和 7 行。

---

(ColB, ColC) 的值对可能很大（在 ~100 的范围内）。除了使用大量 OR 条件的大型查询之外，是否有更有效的方法来查询此数据？

我希望有一种方法可以使用元组的等价物，这意味着我可以做类似的事情：

SELECT * FROM [Table] 
WHERE (ColB, ColC) IN ({A, Y}, {B, Y}, {C, Y}, {D, Z})

有什么想法吗？

---

编辑：（回答评论中的一些问题）

ColB和ColC的字段存储guid并声明为uniqueidentifier类型。
这需要在 SQL Server 2005 及以上版本（所有版本）上运行。
该表有数百万行，我不反对添加其工作所需的任何索引。

Answer 1

你可以这样做。 （如果您使用的是 SQL Server 2008，则可以使用值行构造函数而不是 union alls）

您需要检查查询计划以查看它是否更有效。

SELECT * /*But don't use star*/
FROM [Table] 
JOIN 
(
SELECT 'A' AS BMatch, 'Y' AS CMatch UNION ALL
SELECT 'B' AS BMatch, 'Y' AS CMatch UNION ALL
SELECT 'C' AS BMatch, 'Y' AS CMatch UNION ALL
SELECT 'D' AS BMatch, 'Z' AS CMatch UNION ALL ...
) Matches
ON ColB = BMatch AND ColC = CMatch

您在评论中说 ColB 和 ColC 的组合是唯一的，因此（假设您的表已经有聚集索引）我想如果您创建一个 unique (colb, colc) 或 (colc, colb) 上的非聚集索引，上述内容应该为您提供一个包含 100 次索引查找和 100 次书签查找的计划。如果没有，您可以尝试添加索引提示以使其使用新索引。您需要将其 I/O 与完整扫描的 I/O 进行比较，因为大量 or 可能会给您带来结果。

通过在非聚集索引中包含额外的必需列，可以避免书签查找的成本。不过您已经使用了 * 所以我不知道这有多可行。您需要平衡此查询的好处与数据修改操作可能带来的坏处。

Answer 2

您是否考虑过添加一个计算列作为两列的串联？

它将简化您的选择语句并允许添加索引。

CREATE TABLE [dbo].[Table] (
  ColA  INTEGER
  , ColB VARCHAR(1)
  , ColC VARCHAR(1)
  , ColBC AS ColB + ColC
)  

CREATE UNIQUE INDEX IX_TABLE_COLBC ON [dbo].[Table] (ColBC)

INSERT INTO [Table] VALUES(1, 'A', 'X')
INSERT INTO [Table] VALUES(2, 'A', 'Y')
INSERT INTO [Table] VALUES(3, 'B', 'Y')
INSERT INTO [Table] VALUES(4, 'C', 'Y')
INSERT INTO [Table] VALUES(5, 'C', 'Z')
INSERT INTO [Table] VALUES(6, 'D', 'Y')
INSERT INTO [Table] VALUES(7, 'D', 'Z')

---

不完全等同于元组，但它确实允许您将选择更改为

SELECT * FROM [Table] 
WHERE (ColBC) IN ('AY', 'BY', 'CY', 'DZ')

并且它使用索引。

Answer 3

为什么不创建一个新表来过滤结果集？由于有大约 100 个过滤依据，如果您的过滤条件将来发生变化，此解决方案将更加灵活，即您只需更改过滤表，而不是更改将嵌入到 select 语句中的“where”子句： -

ifexists ( select * from sys.objects WHERE object_id = object_id(N'dbo.Filter') AND type in (N'U'))
删除表 dbo.Filter

创建表 dbo.Filter (
ColB char(1) 不为空，
ColC char(1) 不为空，
约束 pkFilter 主键聚集 (ColB,ColC) 且填充因子 = 100)

插入 dbo.Filter (ColB,ColC) 值 ('A','Y'),('B','Y'),('C' ,'Y'),('D','Z')

从[表]中选择 * 作为 t
内连接 dbo.Filter 作为 t.ColB = f.ColB 和 t.ColC = f.ColC 上的 f