복붙노트

[SQL] 대 'PATINDEX' '와 같은'SQL 문자열 비교 속도

SQL

대 'PATINDEX' '와 같은'SQL 문자열 비교 속도

나는 (간체) 다음과 같은 질의를했다 ...

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

내 데이터 집합이 내가 그것을 가속화의 방법을 찾고있다, 그래서 실행하기 위해 약 90 초 복용했다. 이유없이, 내가 대신 LIKE의 PATINDEX을 시도 할 줄 알았는데 ...

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

동일한 세트에 눈 깜짝 할 사이에 수행하고 동일한 결과를 반환한다.

LIKE 너무 느린 PATINDEX보다 사람이 왜 설명 할 수 있습니까? 을 감안할 때처럼 그냥 아무것도 경우 느린 될 후자 ​​예상 한 것 실제 위치를 반환 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. ==============================

    1.성능 반복 차이 그런 종류의 인해 두 쿼리에 대한 실행 계획의 차이에 가능성이 높습니다.

    성능 반복 차이 그런 종류의 인해 두 쿼리에 대한 실행 계획의 차이에 가능성이 높습니다.

    유무 SQL Server는 각 쿼리가 실행되는 실제 실행 계획을 반환하고, 실행 계획을 비교합니다.

    또한, 두 번 각 쿼리를 실행하고 두 쿼리의 성능을 비교할 때, 첫 번째 실행의 타이밍을 밖으로 던져. (첫 번째 쿼리 실행이 무거운 (문 구문 분석 및 데이터베이스를 많이 포함 할 수 I / O). 두 번째 실행은 당신에게 더 많은 유효하게 다른 쿼리에 비교되는 경과 시간을 줄 것이다.

    LIKE 너무 느린 PATINDEX보다 사람이 왜 설명 할 수 있습니까?

    각 쿼리에 대한 실행 계획은 가능성의 차이를 설명 할 것이다.

    단순히 두 가지 기능이 얼마나 효율적으로 작성되었습니다의 문제인가?

    정말 기능이 작성하는 방법을 효율적으로의 문제가 아니다. 정말로 중요한 것은 생성 된 실행 계획이다. 술어가 스 SARGable하고 최적화가 가능한 인덱스를 사용하도록 선택 여부를 경우 중요한 것은입니다.

    [편집하다]

    빠른 테스트 I 달렸다, 나는 실행 계획의 차이를 참조하십시오. (가)의 술어 가입 LIKE 연산자, 계획은 "컴퓨터 스칼라"동작 후 표 2에 "테이블 스풀 (레이지 스풀)"연산을 포함한다. PATINDEX 기능으로, 나는 계획에서 "테이블 스풀"작업 표시되지 않습니다. 그러나 내가 갖는 계획은 쿼리, 테이블, 인덱스 및 통계의 차이 주어진 당신이 얻을 계획보다 상당히 다를 수 있습니다.

    [편집하다]

    I는 (별도로 표현 자리 이름)에서 두 개의 쿼리 실행 계획 출력에서 ​​볼 수있는 유일한 차이점은 PATINDEX 함수 하나 호출 대신에, 3 개 개의 내부 함수 (LikeRangeStart, LikeRangeEnd 및 LikeRangeInfo의 호출이다. 이러한 함수로 나타나는 결과 집합의 각 행, 얻어진 발현 호출 중첩 루프 내부 테이블의 검색에 이용된다.

    그래서, 그것은 PATINDEX 함수에 단일 호출보다 LIKE 연산자에 대한 세 가지 함수 호출이 더 비싼 수 있다면 같은 모양 (현명한 경과 시간)을한다. 합니다 (가입 그러한 기능이 중첩 루프의 외측 결과 집합의 각 행에 대해 호출 될 계획 프로그램 설명, 다수의 행, 경과 시간, 심지어 약간의 차이에 대해 상당한 성능 차이를 나타낼 수있는 충분한 시간을 곱해질 수있다.)

    내 시스템에 대한 몇 가지 테스트 케이스를 실행 한 후, 난 여전히 당신이보고있는 결과에 당황하고있다.

    어쩌면 그것은 세 가지 내부 기능에 대한 호출 대 PATINDEX 함수 호출의 성능에 문제가있다 (LikeRangeStart, LikeRangeEnd, LikeRangeInfo.)

    그것은 "큰"충분한 결과 세트에서 수행 사람들과, 경과 시간의 작은 차이가 큰 차이로 곱한 수있는 가능성이 있습니다.

    하지만 실제로는 LIKE 연산자를 사용하여 쿼리가 PATINDEX 함수를 사용하여 해당 쿼리보다 실행하는 데 훨씬 더 오래 걸릴 것이라는 다소 놀라운 일이 될 찾을 수 있습니다.

  2. ==============================

    2.나는 그것이 시간의 차이를 담당하는 LikeRangeStart, LikeRangeEnd, LikeRangeInfo 기능의 추가 오버 헤드이라는 논문으로 확신 전혀 아니에요.

    나는 그것이 시간의 차이를 담당하는 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.

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

    나는 테이블 변수 대신 #temp 테이블을 대체 할 경우 스트림 집합으로가는 예상 행 수는 크게 다르다는 것을 그러나 통지를 할.

    등의 버전은 약 330,596 및 PATINDEX 약 1,875,000있다.

    난 당신이 또한 해시 계획에 참여해야 알 수 있습니다. PATINDEX 버전이 쿼리가 더 큰 메모리 부여를 얻을 수 LIKE보다 행의 큰 번호를 추정하는 것 아마 때문에 너무 디스크에 해시 작업을 유출 할 필요가 없습니다. 이 경우 있는지 확인하기 위해 프로파일에 해시 경고를 추적 해보십시오.

  3. ==============================

    3.아마도 이것은 DB 캐싱의 질문은 ...

    아마도 이것은 DB 캐싱의 질문은 ...

    DBCC 도우미를 사용하여 각 쿼리를 실행하기 전에 다시 캐시를 사용 해보세요 :

    4. from https://stackoverflow.com/questions/8052425/sql-string-comparison-speed-like-vs-patindex by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 행이 PHP 존재하는지 확인  (0) 2020.07.02
[SQL] SQL 서버 DATETIME 포맷  (0) 2020.07.02
[SQL] 어떻게 분할 쉼표는 MySQL을 저장 프로 시저의 텍스트를 분리  (0) 2020.07.01
[SQL] 중복이 SQL과 테이블에 위에 각 그룹에서 3 값을 선택하는 방법 [중복]  (0) 2020.07.01
[SQL] SQL 쿼리에서 역 중복 제거  (0) 2020.07.01
,

티스토리툴바