SQL 查询 - 使用替换功能缓慢

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:CMTResumes', '')
             , 'internal_', '')
           , 'monster_', '')
      = REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(PC.[File], 'D:documents', '')
            ,'D:CMTResumes', '')
          ,'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表和结果表都有很多行。

我知道替换函数会导致可优化性问题，但是我需要这样做以确保匹配。

任何想法如何改进

Answer 1

您可以做的是在两个表上创建持久列并为其编制索引

ALTER TABLE Results ADD FixedPath AS REPLACE(
             REPLACE(
               REPLACE(
                 REPLACE([Resume], 'D:documents', '')
               ,'D:CMTResumes', '')
             , '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:CMTResumes', '')
                 , '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 将视图扩展到基础查询中。看这里。