SQL查询-使用replace函数时速度很慢

Question

我有一个花了很长时间的问题，请看下面...

SELECT R.Email
      ,MAX(R.Id)
      ,MAX(R.Postcode)
FROM ParsedCandidates PC
    INNER JOIN Results R
        ON REPLACE(
             REPLACE(
               REPLACE(
                 REPLACE(R.[Resume], 'D:\documents\', '')
               ,'D:\CMT\Resumes\', '')
             , 'internal_', '')
           , 'monster_', '')
      = REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(PC.[File], 'D:\documents\', '')
            ,'D:\CMT\Resumes\', '')
          ,'internal_', '')
        , 'monster_', '') 
WHERE CONTAINS(PC.ParsedCV, '"Marketing Executive"')
    AND R.Email IS NOT NULL
    AND R.Email <> ''
    AND R.Postcode IS NOT NULL
    AND R.Postcode <> ''
    AND EXISTS (SELECT 1
                FROM Candidates_Sourcing CS
                WHERE CS.Email = R.Email
                    AND CS.Email IS NOT NULL
                    AND CS.Email <> ''
               )
GROUP BY R.Email;

candidates_sourcing表和results表都有很多行。

我知道替换函数会导致sargability的问题，但我需要这样做以确保匹配。

有什么想法可以改进这一点吗？

Answer 1

您可以做的是在这两个表上创建持久列，并对这些列进行索引

ALTER TABLE Results ADD FixedPath AS REPLACE(
             REPLACE(
               REPLACE(
                 REPLACE([Resume], 'D:\documents\', '')
               ,'D:\CMT\Resumes\', '')
             , 'internal_', '')
           , 'monster_', '') PERSISTED

CREATE NONCLUSTERED INDEX ixResults_FixedPath ON Results (FixedPath) INCLUDE (...) WHERE (...)

索引的INCLUDE和可能的WHERE将取决于您的查询。

如果不想更改表，可以在这两个表上创建索引视图，然后联接这些视图。

CREATE VIEW v_Results 
WITH SCHEMABINDING
AS
SELECT R.Id
--   , ... other columns ...
     , REPLACE(
                 REPLACE(
                   REPLACE(
                     REPLACE(R.[Resume], 'D:\documents\', '')
                   ,'D:\CMT\Resumes\', '')
                 , 'internal_', '')
               , 'monster_', '') AS FixedPath
  FROM dbo.Resume R
 WHERE R.Email IS NOT NULL
   AND R.Email <> ''
   AND R.Postcode IS NOT NULL
   AND R.Postcode <> ''
GO

但是，这里的索引必须是唯一的。

CREATE UNIQUE CLUSTERED INDEX ux ON dbo.v_Results (FixedPath, Id);

创建了这两个视图之后，您就可以加入

SELECT ...
  FROM v_Results R WITH (NOEXPAND)
  JOIN v_ParsedCandidates PC WITH (NOEXPAND)
    ON R.FixedPath = PC.FixedPath

NOEXPAND提示可防止SQL Server将视图展开到基础查询中。See here。