SQL Server连接性能

Question

我在SQL Server连接中遇到了性能问题，因为SQL Server连接存在多列比较。

SELECT T1.* FROM T1, T2
WHERE T1.LASTNAME = T2.LASTNAME AND
T1.FIRSTNAME = T2.FIRSTNAME AND
T1.MIDDLENAME = T2.MIDDLENAME AND
DAY(T1.DoB) = T2.DOBDay AND
MONTH(T1.DoB) = T2.DOBMonth

这些表中的每个表都有超过20,000条记录，这确实会导致性能问题。我运行了它，它花费了超过6分钟，所以我取消了它！

有没有办法提高这条语句的性能？

Answer 1

在此之前，您必须在两个表上创建下面的非集群索引。

CREATE NONCLUSTERED INDEX IX_First_Middle_T2  ON T2 (FIRSTNAME,MIDDLENAME); 

现在运行下面的查询

SELECT T1.*,DAY(T1.DOB) as DOBDay, MONTH(T1.DOB) as DOBMonth INTO #T1
CREATE NONCLUSTERED INDEX IX_First_Middle_T1  ON #T1 (FIRSTNAME,MIDDLENAME); 

SELECT T1.*
FROM #T1 T1
JOIN T2
ON T1.LASTNAME     = T2.LASTNAME
and T1.FIRSTNAME   = T2.FIRSTNAME
and T1.MIDDLENAME  = T2.MIDDLENAME
and T1.DOBDay      = T2.DOBDay
and T1.DOBMonth    = T2.DOBMonth

上面的查询给出了两个额外的列，其中包含month和date，而不是T1。*用列名替换它。

Answer 2

因为您只需要来自T1表的数据，所以可以使用left join或exists子句。

  select T1.* 
  from T1
  where
      exists (
                  select 1
                  from T2
                  where
                     T1.LASTNAME = T2.LASTNAME
                     and T1.FIRSTNAME = T2.FIRSTNAME
                     and T1.MIDDLENAME = T2.MIDDLENAME
                     and DAY(T1.DoB) = T2.DOBDay
                     and MONTH(T1.DoB) = T2.DOBMonth
             )