SQL String сравнивает скорость 'like' vs 'patindex'

У меня был запрос следующим образом (упрощенный)...

SELECT     *
FROM       table1 AS a
INNER JOIN table2 AS b ON (a.name LIKE '%' + b.name + '%')

Для моего набора данных это заняло около 90 секунд, чтобы выполнить, поэтому я искал способы ускорить его. По уважительной причине, я думал, что попробую PATINDEX вместо LIKE...

SELECT     *
FROM       table1 AS a
INNER JOIN table2 AS b ON (PATINDEX('%' + b.name + '%', a.name) > 0)

В том же наборе данных это выполняется в мгновение ока и возвращает те же результаты.

Может ли кто-нибудь объяснить, почему LIKE намного медленнее PATINDEX? Учитывая, что LIKE просто возвращает BOOLEAN, тогда как PATINDEX возвращает фактическое местоположение, я бы ожидал, что последнее будет медленнее, если что-нибудь, или это просто вопрос того, насколько эффективно эти две функции были написаны?

Хорошо, вот каждый запрос полностью, за которым следует его план выполнения. "#StakeholderNames" - это только временная таблица вероятных имен, с которыми я сопоставляюсь.

Я отложил данные в реальном времени и несколько раз запускал каждый запрос. Первый занимает около 17 секунд (поэтому несколько меньше, чем исходные 90 секунд в базе данных), а второй менее 1 секунды...

SELECT              sh.StakeholderID,
                    sh.HoldingID,
                    i.AgencyCommissionImportID,
                    1

    FROM            AgencyCommissionImport AS i
    INNER JOIN      #StakeholderNames AS sn ON REPLACE(REPLACE(i.ClientName,' ',''), ',','') LIKE '%' + sn.Name + '%'
    INNER JOIN      Holding AS h ON (h.ProviderName = i.Provider) AND (h.HoldingReference = i.PlanNumber)
    INNER JOIN      StakeholderHolding AS sh ON (sn.StakeholderID = sh.StakeholderID) AND (h.HoldingID = sh.HoldingID)
    WHERE           i.AgencyCommissionFileID = @AgencyCommissionFileID
                AND (i.MatchTypeID = 0)
                AND ((i.MatchedHoldingID IS NULL)
                    OR (i.MatchedStakeholderID IS NULL))

   |--Table Insert(OBJECT:([tempdb].[dbo].[#Results]), SET:([#Results].[StakeholderID] = [AttivoGroup_copy].[dbo].[StakeholderHolding].[StakeholderID] as [sh].[StakeholderID],[#Results].[HoldingID] = [AttivoGroup_copy].[dbo].[StakeholderHolding].[HoldingID] as [sh].[HoldingID],[#Results].[AgencyCommissionImportID] = [AttivoGroup_copy].[dbo].[AgencyCommissionImport].[AgencyCommissionImportID] as [i].[AgencyCommissionImportID],[#Results].[MatchTypeID] = [Expr1014],[#Results].[indx] = [Expr1013]))
        |--Compute Scalar(DEFINE:([Expr1014]=(1)))
             |--Compute Scalar(DEFINE:([Expr1013]=getidentity((1835869607),(2),N'#Results')))
                  |--Top(ROWCOUNT est 0)
                       |--Hash Match(Inner Join, HASH:([h].[ProviderName], [h].[HoldingReference])=([i].[Provider], [i].[PlanNumber]), RESIDUAL:([AttivoGroup_copy].[dbo].[Holding].[ProviderName] as [h].[ProviderName]=[AttivoGroup_copy].[dbo].[AgencyCommissionImport].[Provider] as [i].[Provider] AND [AttivoGroup_copy].[dbo].[Holding].[HoldingReference] as [h].[HoldingReference]=[AttivoGroup_copy].[dbo].[AgencyCommissionImport].[PlanNumber] as [i].[PlanNumber] AND [Expr1015] like [Expr1016]))
                            |--Nested Loops(Inner Join, OUTER REFERENCES:([sh].[HoldingID]))
                            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([sn].[StakeholderID]))
                            |    |    |--Compute Scalar(DEFINE:([Expr1016]=('%'+#StakeholderNames.[Name] as [sn].[Name])+'%', [Expr1017]=LikeRangeStart(('%'+#StakeholderNames.[Name] as [sn].[Name])+'%'), [Expr1018]=LikeRangeEnd(('%'+#StakeholderNames.[Name] as [sn].[Name])+'%'), [Expr1019]=LikeRangeInfo(('%'+#StakeholderNames.[Name] as [sn].[Name])+'%')))
                            |    |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#StakeholderNames] AS [sn]))
                            |    |    |--Clustered Index Seek(OBJECT:([AttivoGroup_copy].[dbo].[StakeholderHolding].[PK_StakeholderHolding] AS [sh]), SEEK:([sh].[StakeholderID]=#StakeholderNames.[StakeholderID] as [sn].[StakeholderID]) ORDERED FORWARD)
                            |    |--Clustered Index Seek(OBJECT:([AttivoGroup_copy].[dbo].[Holding].[PK_Holding] AS [h]), SEEK:([h].[HoldingID]=[AttivoGroup_copy].[dbo].[StakeholderHolding].[HoldingID] as [sh].[HoldingID]) ORDERED FORWARD)
                            |--Compute Scalar(DEFINE:([Expr1015]=replace(replace([AttivoGroup_copy].[dbo].[AgencyCommissionImport].[ClientName] as [i].[ClientName],' ',''),',','')))
                                 |--Clustered Index Scan(OBJECT:([AttivoGroup_copy].[dbo].[AgencyCommissionImport].[PK_AgencyCommissionImport] AS [i]), WHERE:([AttivoGroup_copy].[dbo].[AgencyCommissionImport].[AgencyCommissionFileID] as [i].[AgencyCommissionFileID]=[@AgencyCommissionFileID] AND [AttivoGroup_copy].[dbo].[AgencyCommissionImport].[MatchTypeID] as [i].[MatchTypeID]=(0) AND ([AttivoGroup_copy].[dbo].[AgencyCommissionImport].[MatchedHoldingID] as [i].[MatchedHoldingID] IS NULL OR [AttivoGroup_copy].[dbo].[AgencyCommissionImport].[MatchedStakeholderID] as [i].[MatchedStakeholderID] IS NULL)))


SELECT              sh.StakeholderID,
                    sh.HoldingID,
                    i.AgencyCommissionImportID,
                    1

    FROM            AgencyCommissionImport AS i
    INNER JOIN      #StakeholderNames AS sn ON (PATINDEX('%' + sn.Name + '%', REPLACE(REPLACE(i.ClientName,' ',''), ',','')) > 0)
    INNER JOIN      Holding AS h ON (h.ProviderName = i.Provider) AND (h.HoldingReference = i.PlanNumber)
    INNER JOIN      StakeholderHolding AS sh ON (sn.StakeholderID = sh.StakeholderID) AND (h.HoldingID = sh.HoldingID)
    WHERE           i.AgencyCommissionFileID = @AgencyCommissionFileID
                AND (i.MatchTypeID = 0)
                AND ((i.MatchedHoldingID IS NULL)
                    OR (i.MatchedStakeholderID IS NULL))

   |--Table Insert(OBJECT:([tempdb].[dbo].[#Results]), SET:([#Results].[StakeholderID] = [AttivoGroup_copy].[dbo].[StakeholderHolding].[StakeholderID] as [sh].[StakeholderID],[#Results].[HoldingID] = [AttivoGroup_copy].[dbo].[StakeholderHolding].[HoldingID] as [sh].[HoldingID],[#Results].[AgencyCommissionImportID] = [AttivoGroup_copy].[dbo].[AgencyCommissionImport].[AgencyCommissionImportID] as [i].[AgencyCommissionImportID],[#Results].[MatchTypeID] = [Expr1014],[#Results].[indx] = [Expr1013]))
        |--Compute Scalar(DEFINE:([Expr1014]=(1)))
             |--Compute Scalar(DEFINE:([Expr1013]=getidentity((1867869721),(2),N'#Results')))
                  |--Top(ROWCOUNT est 0)
                       |--Hash Match(Inner Join, HASH:([h].[ProviderName], [h].[HoldingReference])=([i].[Provider], [i].[PlanNumber]), RESIDUAL:([AttivoGroup_copy].[dbo].[Holding].[ProviderName] as [h].[ProviderName]=[AttivoGroup_copy].[dbo].[AgencyCommissionImport].[Provider] as [i].[Provider] AND [AttivoGroup_copy].[dbo].[Holding].[HoldingReference] as [h].[HoldingReference]=[AttivoGroup_copy].[dbo].[AgencyCommissionImport].[PlanNumber] as [i].[PlanNumber] AND patindex([Expr1015],[Expr1016])>(0)))
                            |--Nested Loops(Inner Join, OUTER REFERENCES:([sh].[HoldingID]))
                            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([sn].[StakeholderID]))
                            |    |    |--Compute Scalar(DEFINE:([Expr1015]=('%'+#StakeholderNames.[Name] as [sn].[Name])+'%'))
                            |    |    |    |--Table Scan(OBJECT:([tempdb].[dbo].[#StakeholderNames] AS [sn]))
                            |    |    |--Clustered Index Seek(OBJECT:([AttivoGroup_copy].[dbo].[StakeholderHolding].[PK_StakeholderHolding] AS [sh]), SEEK:([sh].[StakeholderID]=#StakeholderNames.[StakeholderID] as [sn].[StakeholderID]) ORDERED FORWARD)
                            |    |--Clustered Index Seek(OBJECT:([AttivoGroup_copy].[dbo].[Holding].[PK_Holding] AS [h]), SEEK:([h].[HoldingID]=[AttivoGroup_copy].[dbo].[StakeholderHolding].[HoldingID] as [sh].[HoldingID]) ORDERED FORWARD)
                            |--Compute Scalar(DEFINE:([Expr1016]=replace(replace([AttivoGroup_copy].[dbo].[AgencyCommissionImport].[ClientName] as [i].[ClientName],' ',''),',','')))
                                 |--Clustered Index Scan(OBJECT:([AttivoGroup_copy].[dbo].[AgencyCommissionImport].[PK_AgencyCommissionImport] AS [i]), WHERE:([AttivoGroup_copy].[dbo].[AgencyCommissionImport].[AgencyCommissionFileID] as [i].[AgencyCommissionFileID]=[@AgencyCommissionFileID] AND [AttivoGroup_copy].[dbo].[AgencyCommissionImport].[MatchTypeID] as [i].[MatchTypeID]=(0) AND ([AttivoGroup_copy].[dbo].[AgencyCommissionImport].[MatchedHoldingID] as [i].[MatchedHoldingID] IS NULL OR [AttivoGroup_copy].[dbo].[AgencyCommissionImport].[MatchedStakeholderID] as [i].[MatchedStakeholderID] IS NULL)))

Ответы

Ответ 1

Такая повторяемая разница в производительности, скорее всего, связана с различием в планах выполнения для двух запросов.

Попросите SQL Server вернуть фактический план выполнения при каждом запуске запроса и сравнить планы выполнения.

Кроме того, запустите каждый запрос дважды и выкиньте время для первого запуска, когда вы сравните производительность двух запросов. (Первый запуск запроса может включать в себя много тяжелой работы (разбор операторов и ввод-вывод базы данных). Второй прогон даст вам прошедшее время, которое более справедливо по сравнению с другим запросом.

Может ли кто-нибудь объяснить, почему LIKE намного медленнее PATINDEX?

План выполнения для каждого запроса, скорее всего, объяснит разницу.

Это просто вопрос, насколько эффективно эти две функции были написаны?

Это не вопрос того, насколько эффективно записываются функции. Что действительно важно, так это сгенерированный план выполнения. Важно то, что предикаты являются мобильными и определяет ли оптимизатор использование доступных индексов.

[EDIT]

В ходе быстрого теста я побежал, я вижу разницу в планах выполнения. С оператором LIKE в предикате соединения план включает операцию "Table Spool (Lazy Spool)" в таблице2 после операции "Computer Scalar". С функцией PATINDEX я не вижу в проекте "Table Spool". Но планы, которые я получаю, могут существенно отличаться от планов, которые вы получаете, учитывая различия в запросах, таблицах, индексах и статистике.

[EDIT]

Единственное отличие, которое я вижу в выводе плана выполнения для двух запросов (кроме имен замеров выражений), - это вызовы трех внутренних функций (LikeRangeStart, LikeRangeEnd и LikeRangeInfo вместо одного вызова к функции PATINDEX. Эти функции вызывают для каждой строки в результирующем наборе, а результирующее выражение используется для сканирования внутренней таблицы во вложенном цикле.

Итак, похоже, что три вызова функции для оператора LIKE могут быть более дорогими (истекшее время), чем один вызов функции PATINDEX. (План объяснения показывает, что функции, вызываемые для каждой строки во внешнем наборе результатов вложенного объединения цикла, для большого количества строк даже небольшое различие в прошедшем времени может быть умножено достаточно много раз, чтобы продемонстрировать значительную разницу в производительности.)

После запуска некоторых тестовых примеров в моей системе, я все еще недооцениваю результаты, которые вы видите.

Возможно, это проблема с производительностью вызовов функции PATINDEX по сравнению с вызовами трех внутренних функций (LikeRangeStart, LikeRangeEnd, LikeRangeInfo.)

Возможно, что с теми, которые выполняются на "большом" достаточно большом наборе результатов, небольшая разница в прошедшем времени может быть умножена на значительную разницу.

Но на самом деле я нахожу, что несколько удивительно, что запрос с использованием оператора LIKE займет значительно больше времени, чем эквивалентный запрос, используя функцию PATINDEX.

Ответ 2

Возможно, речь идет о кэшировании DB...

Попробуйте кэшировать reset перед запуском каждого запроса с помощью помощников DBCC:

Ответ 3

Я вовсе не убежден тезисом в том, что это дополнительные накладные расходы функций LikeRangeStart, LikeRangeEnd, LikeRangeInfo, которые отвечают за временное несоответствие.

Это просто не воспроизводится (по крайней мере, в моем тесте, настройке по умолчанию и т.д.). Когда я попробую следующее

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

DECLARE @T TABLE (name sysname )
INSERT INTO @T
SELECT TOP 2500 name + '...' + 
   CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS VARCHAR)
FROM sys.all_columns

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
PRINT '***'
SELECT     COUNT(*)
FROM       @T AS a
INNER JOIN @T AS b ON (a.name LIKE '%' + b.name + '%')

PRINT '***'
SELECT     COUNT(*)
FROM       @T AS a
INNER JOIN @T AS b ON (PATINDEX('%' + b.name + '%', a.name) > 0)

Что дает практически тот же план для обоих, но также содержит эти различные внутренние функции, я получаю следующее.

LIKE

Table '#5DB5E0CB'. Scan count 2, logical reads 40016
CPU time = 26953 ms,  elapsed time = 28083 ms.

PATINDEX

Table '#5DB5E0CB'. Scan count 2, logical reads 40016
CPU time = 28329 ms,  elapsed time = 29458 ms.

Однако я замечаю, что если вместо таблицы использовать вместо таблицы таблицу #temp, оцененное количество строк, входящих в агрегат потока, существенно отличается.

Версия LIKE имеет, по оценкам, 330 596 и PATINDEX, по оценкам, 1,875,000.

Я заметил, что у вас также есть хеш-соединение в вашем плане. Возможно потому, что версия PATINDEX, по-видимому, оценивает большее количество строк, чем LIKE, этот запрос получает больший объем памяти, поэтому не нужно проливать хеш-операцию на диск. Попробуйте отслеживать предупреждения хеширования в Profiler, чтобы убедиться, что это так.