join over charindex很慢

Question

我正在尝试优化Server 2017中的T过程。我无法直接访问服务器，因此无法更改任何设置，也无法启用FullTextSearch。

有两个表，一个具有关键字列表(varchar(50))和一个数字代码(int)，另一个具有大型自由输入的文本(varchar(max))和一个ID (int)。我要标记所有这些文本，其中关键字包含在另一个表中。使用游标，它的工作方式如下：

CREATE PROCEDURE markTexts 
AS
BEGIN
    DECLARE @code INT;
    DECLARE @keyword VARCHAR(50);

    DECLARE kcodes CURSOR LOCAL FAST_FORWARD FOR
        SELECT code, keyword
        FROM KeywordCode
        ORDER BY code ASC;

    OPEN kcodes;
  
    FETCH NEXT FROM kcodes INTO @code, @keyword;

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        INSERT INTO markedTexts (textid, keywordcode)
            SELECT t.id, @code AS keywordcode
            FROM fullTexts t
            WHERE CHARINDEX(@keyword, t.fullText) > 0;

        FETCH NEXT FROM kcodes INTO @code, @keyword;   
    END;

    CLOSE kcodes;
    DEALLOCATE kcodes;
END;

表KeywordCode有200行，fullTexts有50k行。运行该过程会产生~20k的结果，大约需要30秒，这太慢了(至少我希望它运行得更快)。由于我不能使用FullTextSearch (这可能对索引和所有这些.都更快)，所以我认为集合方法可能比迭代游标方法更快。看起来是这样的：

INSERT INTO markedTexts (textid, keywordcode)
    SELECT t.id, k.code 
    FROM fullTexts t 
    INNER JOIN KeywordCode k ON CHARINDEX(k.keyword, t.fullText) > 0;

看起来更简单更干净，结果也一样.但这大约需要7分钟(约3.5倍的时间)。

所以我的问题是:为什么联接方法比游标慢得多，有什么明显的方法可以加快速度吗？

Answer 1

首先，您不需要那个游标，我认为简单的联接就可以了。游标速度慢得可怜，一般没有必要。让我们关注插入的SELECT部分，因为这是另一个问题。首先是一些样本数据：

-- Sample Data
CREATE TABLE dbo.KeywordCode(code INT, keyword VARCHAR(100));
CREATE TABLE dbo.fullTexts(id INT PRIMARY KEY, FullTxt VARCHAR(500));

INSERT dbo.KeywordCode 
  VALUES(1,'yada yada'),(2,'blah'),(3,'abc'),(4,'xxx a');
INSERT dbo.fullTexts 
  VALUES(10,'...yada yada yada'),(11,'xxx'),(12,'xxx abc123'),(24,'blah blah');

1.解决光标问题

这应该比使用游标更快地获得相同的结果：

-- INSERT INTO markedTexts (textid, keywordcode)
SELECT t.id, k.keyword
FROM   dbo.KeywordCode AS k
JOIN   dbo.fullTexts   AS t
  ON   CHARINDEX(k.keyword,t.FullTxt) > 0;

2.解决SARGability问题

在筛选器(例如WHERE或JOIN子句)中使用CHARINDEX使查询非SARGable，这意味着执行引擎将无法对可用的索引执行查找，因此每次查询运行时都会进行完整的表扫描。请注意上述SELECT查询的执行计划：

​

​

虽然我们已经解决了光标问题，但我们仍然必须避免这种扫描。如果可能的话，我建议使用索引视图。

CREATE VIEW dbo.kw_txt WITH SCHEMABINDING AS
SELECT t.id, k.keyword
FROM   dbo.KeywordCode AS k
JOIN   dbo.fullTexts   AS t ON CHARINDEX(k.keyword,t.FullTxt) > 0;

现在，在索引视图就位后，您将得到这个计划，它为您预先加入了值。

​

​

这种方法的特别之处在于您将如何获得这个改进的计划，它利用索引视图，甚至不引用查询中的视图。请注意，这两个查询都将利用新视图上的索引：

SELECT t.id, t.keyword FROM dbo.kw_txt AS t;

以及：

SELECT t.id, k.keyword
FROM   dbo.KeywordCode AS k
JOIN   dbo.fullTexts   AS t ON CHARINDEX(k.keyword,t.FullTxt) > 0;

对于这类问题，还有其他更复杂的方法，但基于您提供的详细信息，我将使用这一方法。