Скорость сравнения строк SQL 'like' и 'patindex'

#sql #sql-server #string #performance #tsql

#sql #sql-сервер #строка #Производительность #tsql

Вопрос:

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

 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 просто возвращает ЛОГИЧЕСКОЕ значение, тогда как 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 (SQLServer, MySQL, Oracle и т.д.) Вы используете?

2. 90 секунд против мгновения ока предположительно указывают на то, что планы выполнения кардинально отличаются (тип соединения) или происходило что-то еще (блокировка или чтение с диска по сравнению с чтением из кэша). Я сильно сомневаюсь, что это связано исключительно с разницей во времени процессора при изменении patindex на like . Пожалуйста, опубликуйте планы выполнения и выходные данные SET STATISTICS IO ON; SET STATISTICS TIME ON;

3. Возможно, это вопрос кэширования БД? Вы пытались сбросить кэш перед запуском каждого запроса с помощью помощников DBCC? ( DBCC DROPCLEANBUFFERS , DBCC FREEPROCCACHE )

4. @Oleg — Ведущий подстановочный знак означает, что индекс тоже не поможет.

5. Спасибо за комментарии. В ответ на некоторые: это SQLServer, набор данных, который я использовал, — это текущие данные, которые (после устранения проблемы с производительностью) сейчас использует наш клиент, поэтому мне придется вернуть его на наш тестовый сайт, чтобы запустить планы выполнения. Я обновлю, как только сделаю это.

Ответ №1:

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

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

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

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

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

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

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

[ПРАВИТЬ]

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

[ПРАВИТЬ]

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

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

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

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

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

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

1. Хм! Я только что повторно запустил запросы с аналогичным по размеру (но другим) набором данных в нашей существующей тестовой базе данных, и нигде нет такой большой разницы. Итак, похоже, что это может зависеть от фактических данных. Как я уже говорил выше, я восстановлю текущие данные, как только смогу, чтобы точно воспроизвести то, что я делал раньше, и посмотреть, как выглядят планы выполнения.

2. Кстати, я действительно запускал запросы несколько раз, каждый с одинаковыми результатами (когда у меня были исходные данные для воспроизведения!).

3. Я не согласен с этим анализом. Можете ли вы предоставить воспроизведение, в котором наличие этих внутренних функций с аналогичными в остальном планами имеет какое-либо существенное значение вообще?

Ответ №2:

Меня совсем не убеждает тезис о том, что это дополнительные накладные расходы 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)

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

Нравится

 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 таблицу вместо переменной table, то расчетное количество строк, поступающих в совокупный поток, значительно отличается.

В LIKE версии примерно 330 596 и PATINDEX примерно 1 875 000 экземпляров.

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

Ответ №3:

Возможно, это вопрос кэширования БД…

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