代码之家 › 专栏 › 技术社区 › adrianbanks

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

tuples performance sql-server sql

adrianbanks · 技术社区 · 15 年前

我有一个表,其中包含一些数据:

 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})

有什么想法吗?

编辑: (回答评论中的一些问题)

田野 科尔布 和 科尔克 存储guid并声明为 uniqueidentifier 类型。
这需要在SQL Server 2005以上版本(所有版本)上工作。
这个表有数百万行的顺序,我不反对添加任何要使其工作所需的索引。

3 回复 | 直到 15 年前

Martin Smith 15 年前

你可以这样做。(如果您在SQL Server 2008上,可以使用 values 行构造函数而不是 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 nonclustered index 在任一 (colb, colc) 或 (colc, colb) 上面应该给你一个100个索引查找和100个书签查找的计划。如果没有,您可以尝试添加一个索引提示,让它使用新的索引。您需要将I/O与完整扫描的I/O进行比较 or S可能会给你。

通过在非聚集索引中包含额外的必需列,可以避免书签查找的成本。你已经用过 * 尽管如此,我不知道这有多可行。您需要平衡这个查询的好处与可能的数据修改操作的不一致。

Lieven Keersmaekers 15 年前

你考虑过加一个 calculated column 作为两列的串联?

它将简化select语句并允许添加索引。

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')

它使用索引。

Andy Jones 15 年前

为什么不创建一个新表来筛选结果集?有大约100个值可供筛选,如果您的筛选条件在将来发生变化(即,您只需更改筛选表,而不是更改将嵌入到select语句中的“where”子句),则此解决方案将更加灵活:

如果存在(从sys.objects中选择*,其中object_id=object_id(n'dbo.filter'),然后键入(n'u'))
删除表dbo.filter

创建表dbo.filter(
colb char(1)不为空,
colc char(1)不为空,
约束pkfilter主键聚集(colb,colc),fillFactor=100)

插入dbo.filter(colb,colc)值('a','y')、('b','y')、('c','y')、('d','z')

从[表]中选择*作为T
内部连接dbo.filter as f on t.colb=f.colb and t.colc=f.colc