SQL-Zeichenfolge Vergleich-Geschwindigkeit 'wie' vs 'patindex'

Hatte ich eine Abfrage wie folgt (vereinfacht)...

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

Für mein dataset, das war etwa 90 Sekunden ausgeführt werden, so bin ich auf der Suche nach Möglichkeiten, beschleunigt. Für keinen guten Grund, ich dachte, ich würde versuchen PATINDEX statt WIE...

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

Auf dem gleichen dataset dies führt im Handumdrehen ein Auge und liefert die gleichen Ergebnisse.

Kann mir jemand erklären, warum, WIE ist so viel langsamer als PATINDEX? Da GEFÄLLT ist die Rückgabe nur ein BOOLEAN in der Erwägung, dass PATINDEX ist Rückkehr die tatsächliche Lage hätte ich erwartet, daß das letztere langsamer, wenn überhaupt, oder ist es einfach eine Frage, wie effizient die zwei Funktionen geschrieben wurden?

Ok, hier ist jede Abfrage in die vollen, gefolgt von den Ausführungsplan. "#StakeholderNames" ist nur eine temp-Tabelle, der wahrscheinlich Namen, die ich bin ein Abgleich mit.

Ich zog wieder die live-Daten und führen Sie jede Abfrage mehrmals. Der erste ist etwa 17 Sekunden (also etwas weniger als die ursprünglichen 90 Sekunden auf der live-Datenbank) und die zweite unter 1 Sekunde...

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)))

Sind Tabelle1 und Tabelle2 hat FK-PK Beziehungen?
Haben Sie überprüft, die Abfrage-plan für beide Abfragen? Auch die, die SQL (SQLServer, MySQL, Oracle etc) verwendest du?
90 Sekunden vs Wimpernschlag vermutlich zeigt die Ausführungspläne sind deutlich unterschiedlich (join-Typ) oder es war etwas anderes passiert ist (Sperrung oder Lesen von disc-vs Lesen aus dem cache). Ich bezweifle stark, dass rein von einem Unterschied in der CPU-Zeit ändern patindex zu like. Poste bitte die Ausführungspläne und die Ausgabe von SET STATISTICS IO ON; SET STATISTICS TIME ON;
Vielleicht ist dies eine Frage von DB-Caching? Haben Sie versucht, zurücksetzen des Caches vor der Ausführung jeder Abfrage an, die mithilfe der DBCC-Helfer? (DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE)
WIE sollten ausführen, so schnell wie PATINDEX, wenn die Spalten, die Sie suchen indiziert sind
Die führenden Platzhalter bedeutet, dass ein index wird nicht helfen, entweder.
wie wissen Sie dieses?
Smith : .. im Falle, wenn der index auf einzelne name Spalte
In diesem Fall könnte es tun, ein scan der einzelnen Spalte name und lookups, wenn sich der index ist viel schmaler als die Tabelle und die Statistiken machen es zu glauben, dass das Prädikat sehr selektive, aber ich denke, das würde erfordern, die das Prädikat verwenden Sie ein string-literal-Ausdruck nicht '%' + b.name + '%'
OK, s/könnte/sollte
Danke für die Kommentare. In Antwort auf einige: Es ist SQLServer das dataset, das ich mit live-Daten (ohne Feste das performance-problem) unser Kunde ist heute mit, also werde ich haben, um es zurück zu ziehen, um unsere test-site in Bezug auf die Ausführung der Ausführungspläne. Ich werde aktualisieren, sobald ich getan habe.
BTW Haben Sie vereinfacht Ihre Abfrage vor der Veröffentlichung? Speziell ist Ihre eigentliche Abfrage parametriert?
RE: Ihre Bearbeitung, die Pläne sehen ziemlich Analog. Frage mich, ob es an unterschiedlicher Kardinalität-Schätzungen und nicht genügend Speicher grant " für das hash-join. Sehen Sie irgendwelche hash-Warnungen, wenn Sie trace-Ereignisses im Profiler?

InformationsquelleAutor David Roberts | 2011-11-08

4

Diese Art von wiederholbaren Unterschied in der Leistung ist den meisten wahrscheinlich durch einen Unterschied in der Ausführung der Pläne für die zwei Abfragen.

SQL Server Rückkehr der tatsächliche Ausführungsplan, wenn jede Abfrage wird ausgeführt, und vergleichen Sie die Ausführungspläne.

Außerdem führen Sie jede Abfrage zweimal, und werfen Sie den Zeitpunkt für den ersten Lauf, wenn Sie vergleichen die Leistung der beiden Abfragen. (Die erste Abfrage ausführen, kann eine Menge von schweren gewichten (statement-Analyse und Datenbank i/o). Der zweite Lauf wird Ihnen eine verstrichene Zeit ist mehr Respekt gegenüber den anderen Abfrage.

Kann mir jemand erklären, warum, WIE ist so viel langsamer als PATINDEX?

Den Ausführungsplan für jede Abfrage dürfte die Differenz erklären.

Ist es einfach eine Frage, wie effizient die zwei Funktionen geschrieben wurden?

Es ist nicht wirklich eine Frage, wie effizient die Funktionen geschrieben sind. Was wirklich zählt, ist der generierte Ausführungsplan. Was zählt, ist, wenn die Prädikate sind sargable und ob der Optimierer wählt die Verwendung der verfügbaren Indizes.

[BEARBEITEN]

In der quick-test lief ich, ich sehe einen Unterschied in der Ausführung der Pläne. Mit dem LIKE-operator in der join-Prädikat enthält der plan eine "Table Spool (Lazy Spool)" Betrieb auf Tabelle2 nach der "Computer Scalar" Betrieb. Mit der PATINDEX-Funktion, sehe ich nicht ein "Table Spool" Betrieb in den plan. Aber die Pläne, die ich bin immer möglicherweise deutlich anders aus als die Pläne, die Sie bekommen, angesichts der Unterschiede in den Abfragen, Tabellen, Indizes und Statistik.

[BEARBEITEN]

Der einzige Unterschied sehe ich in der Ausführung plan-Ausgabe, für die zwei Abfragen (abgesehen vom Ausdruck Platzhalter Namen) ist die Aufrufe der drei internen Funktionen (LikeRangeStart, LikeRangeEnd, und LikeRangeInfo in einem Aufruf an die PATINDEX Funktion. Diese Funktionen werden aufgerufen zu werden, für jede Zeile in einer Ergebnismenge, und der resultierende Ausdruck wird verwendet für den scan die innere Tabelle in einer verschachtelten Schleife.

So, es sieht so aus, als wenn die drei Funktionsaufrufe für die LIKE Betreiber könnte teurer werden (verstrichene Zeit klug) als die einzigen Aufruf der PATINDEX Funktion. (Die explain plan zeigt diese Funktionen werden aufgerufen, für jede Zeile in der äußeren Ergebnismenge eines nested-loop-join; für eine große Anzahl von Zeilen, selbst ein kleiner Unterschied in der verstrichenen Zeit konnten multipliziert werden oft genug weisen eine erhebliche performance-Unterschied.)

Nach einigen Testfällen auf meinem system, ich bin immer noch verblüfft von den Ergebnissen, die Sie sehen.

Vielleicht ist es ein Problem mit der Leistung der Aufrufe der PATINDEX-Funktion vs. die Aufrufe der drei internen Funktionen (LikeRangeStart, LikeRangeEnd, LikeRangeInfo.)

Ist es möglich, dass mit den auf ein "großer" genug ResultSet, einem kleinen Unterschied in der verstrichenen Zeit konnten vervielfacht werden, in einem signifikanten Unterschied.

Aber ich finde es eigentlich etwas verwunderlich, dass eine Abfrage mit dem LIKE-operator würde wesentlich länger dauern, um auszuführen als eine entsprechende Abfrage verwenden Sie die PATINDEX-Funktion.
- Hmm! Ich habe gerade neu die Abfragen mit einer ähnlichen Größe (aber verschiedenen) Daten auf unsere vorhandene Datenbank testen und es ist nirgendwo in der Nähe von wie groß ein differnec. So, es sieht aus wie könnte es werden, abhängig von den tatsächlichen Daten. Wie ich oben sagte, ich werde ziehen Sie zurück, die live-Daten so schnell wie ich kann zu replizieren genau das, was ich vorher gemacht habe und sehen, was die Ausführung der Pläne Aussehen.
- Übrigens, ich habe die Abfragen ausführen mehrere Zeit jeden mit dem gleichen Ergebnis (wenn ich die original Daten mit zu spielen!).
- Ich bin nicht einverstanden mit dieser Analyse. Können Sie ein repro, wo die Präsenz dieser internen Funktionen mit ähnlichen Plänen macht keinen Unterschied überhaupt?
InformationsquelleAutor spencer7593
2

Ich bin überhaupt nicht überzeugt von der these, dass es den zusätzlichen Aufwand der LikeRangeStart, LikeRangeEnd, LikeRangeInfo Funktionen, ist verantwortlich für die Zeit-Diskrepanz.

Es ist einfach nicht reproduzierbar (zumindest in meinem test -, Standard-Sortierung etc). Wenn ich versuche, den folgenden
```
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)
```
Gibt im wesentlichen den gleichen plan für beide, aber auch diese verschiedenen internen Funktionen bekomme ich die folgende.

WIE
```
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.
```
Ich merke jedoch, dass, wenn ich ersetzen Sie es mit einem #temp Tabelle anstatt der Tabelle die variable mit der geschätzten Zahl der Zeilen gehen in den stream aggregate ist deutlich anders.

Den LIKE version hat eine geschätzte 330,596 und PATINDEX schätzungsweise 1,875,000.

Ich merke, Sie haben auch einen hash-join in Ihren plan. Vielleicht, weil die PATINDEX version scheint zu schätzen, eine größere Anzahl von Zeilen als LIKE diese Abfrage bekommt einen größeren Speicher gewähren, so haben Sie nicht zu verschütten die hash-operation auf Disk. Versuchen Sie, die Verfolgung der hash-Warnungen im Profiler zu sehen, ob dies der Fall ist.

InformationsquelleAutor Martin Smith
1

Vielleicht ist dies eine Frage von DB-Caching...

Ausprobieren, cache zurücksetzen, bevor Sie laufen jede Abfrage, die mithilfe der DBCC Helfer:
- DBCC DROPCLEANBUFFERS
- DBCC FREEPROCCACHE
InformationsquelleAutor sll

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.