Complex LIKE Query extrem langsam wieder NVARCHAR (450), verglichen mit VARCHAR (450)

Ich frage mich derzeit über einige performancesunterschiede bei VARCHAR / NVARCHAR, besonders bei der Verwendung von komplexen LIKE-Abfragen (die mit _ oder% beginnen).

Ich habe ein Testsetup auf Microsoft SQL server 2014. Ich habe 2 Tabellen. Beide haben ein ID-Feld (Identität (1, 1) und ein Wertfeld (entweder VARCHAR (450) ODER NVARCHAR (450)). Beide haben die identischen 1'000'000 random generierten Einträge.

Die Tabellen heißen tblVarCharNoIndex und tblNVarCharNoIndex (also gibt es keine Indizes. Das Verhalten ist fast das gleiche, wenn ich Indizes verwende).

Jetzt mache ich die folgenden Abfragen aus, um die Dauer zu testing (einmal auf VARCHAR, einmal auf NVARCHAR)

SELECT * FROM tblVarcharNoIndex WHERE Value LIKE '%ab%' SELECT * FROM tblNVarcharNoIndex WHERE Value LIKE '%ab%' 

Die Ausführungszeiten sind wild unterschiedlich. Es dauert 1540ms auf dem VARCHAR Tisch und 8630 ms auf dem NVARCHAR Tisch, also dauert es mehr als 5x länger mit NVARCHAR.

Ich verstehe, dass NVARCHAR Performance-Implikationen hat, da es 2 Bytes benötigt, um zu speichern, das macht völlig Sinn. Aber ich kann keine performancesverschlechterung um 500% erklären, das macht mir keinen Sinn.

Wie pro Anfrage, hier noch einige data.

Abfrage für Tabellenerstellung

 CREATE TABLE [dbo].[tblVarcharNoIndex]( [Id] [int] IDENTITY(1,1) NOT NULL, [Value] [varchar](450) NOT NULL, CONSTRAINT [PK_tblVarcharNoIndex] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[tblNVarcharNoIndex]( [Id] [int] IDENTITY(1,1) NOT NULL, [Value] [nvarchar](450) NOT NULL, CONSTRAINT [PK_tblNVarcharNoIndex] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

Abfrage zur Wertgenerierung

 DECLARE @cnt INT = 0; DECLARE @entries INT = 1000000 --1'000'000; DECLARE @maxLength INT = 450; DECLARE @minLength INT = 50; DECLARE @value VARCHAR(450) DECLARE @length INT WHILE @cnt < @entries BEGIN SELECT @value = '' SET @length = @minLength + CAST(RAND() * (@maxLength - @minLength) as INT) WHILE @length <> 0 BEGIN SELECT @value = @value + CHAR(CAST(RAND() * 96 + 32 as INT)) SET @length = @length - 1 END INSERT INTO tblBase(Value, NValue) VALUES (@value, @value) SET @cnt = @cnt + 1; END; 

(Werte werden später von tblBase kopiert)

LIKE Abfrage in Frage

 DECLARE @start DATETIME DECLARE @end DATETIME DECLARE @testname NVARCHAR(100) = 'INSERT FROM other table' --VARCHAR No Index PRINT 'starting ''' + @testname + ''' on VARCHAR (No Index)' SET @start = GETDATE() SELECT * FROM tblVarcharNoIndex WHERE Value LIKE '%ab%' --This takes 1540ms SET @end = GETDATE() PRINT '-- finished ''' + @testname + ''' on VARCHAR (No Index)' PRINT '-- Duration ' + CAST(DATEDIFF(mcs, @start, @end) AS VARCHAR(100)) + ' microseconds' --NVARCHAR No Index PRINT 'starting ''' + @testname + ''' on NVARCHAR (No Index)' SET @start = GETDATE() SELECT * FROM tblNVarcharNoIndex WHERE Value LIKE '%ab%' --This takes 8630ms SET @end = GETDATE() PRINT '-- finished ''' + @testname + ''' on NVARCHAR (No Index)' PRINT '-- Duration ' + CAST(DATEDIFF(mcs, @start, @end) AS VARCHAR(100)) + ' microseconds' 

Ausführungsplan Der Ausführungsplan für beide Abfragen sieht genauso aus (ich habe nirgends das image hochzuladen, aber es ist ganz einfach):

SELECT (0%) <— Parallelität (Gather Streams) (3%) <— Clustered Index Scan ON Primärschlüssel (97%)

Die Theorie aber ist Klang. LIKE ist ein Operator, der jeden Wert mit einem Teil eines Strings vergleicht. Wenn der Betreiber wirklich ordnungsgemäß basiert und wenn SQL server nicht bewusst war, die Vorteile eines Teils des Wertes über den anderen, dann würde SQL server notwendigerweise einen Algorithmus wie die folgenden (Beispiel in C# ) ausführen müssen:

  for (; foundValue == false && Start < (length - 2); Start += 1) { searchValue = x.Substring(Start, 2); if (searchValue == compareValue) foundValue = true; } 

nur es gibt doppelt so viele character in NVARCHAR .

Aus meinen eigenen Prüfungen bemerke ich folgendes:

Tabelle 'tblVarcharNoIndex'. Scan-Zähler 1, logisch liest 97, physikalische liest 0, read-ahead liest 0, lob logisch liest 0, lob physikalische liest 0, lob read-ahead liest 0.

Tabelle 'tblNVarcharNoIndex'. Scan-Zähler 1, logische liest 189, physikalische liest 0, read-ahead liest 0, lob logisch liest 0, lob physikalische liest 0, lob read-ahead liest 0.

Logical Reads impliziert, wie viel SQL für die Vergleiche gespeichert wurde, und wir bemerken, dass dies ein wenig über 2x ist. Ich denke, die Antwort kann gesehen werden, wenn man den tatsächlichen Ausführungsplan betrachtet und bemerkt die geschätzte Anzahl von Zeilen war 56 vs 73, sogar dachte, dass die gleiche Anzahl von Zeilen letztlich zurückgegeben wurde.

Ein Blick auf die Kundenstatistik zeigt jedoch, was Sie wahrscheinlich bemerkt haben:

  NVAR VAR AVERAGE Query Profile Statistics Number of INSERT, DELETE and UPDATE statements 0 0 0.0000 Rows affected by INSERT, DELETE, or UPDATE statements 0 0 0.0000 Number of SELECT statements 2 2 2.0000 Rows returned by SELECT statements 306 306 306.0000 Number of transactions 0 0 0.0000 Network Statistics Number of server roundtrips 1 1 1.0000 TDS packets sent from client 1 1 1.0000 TDS packets received from server 45 23 34.0000 Bytes sent from client 146 144 145.0000 Bytes received from server 180799 91692 136245.5000 Time Statistics Client processing time 286 94 190.0000 Total execution time 317 156 236.5000 Wait time on server replies 31 62 46.5000 

Beachten Sie, dass die TDS-Pakete, die vom server empfangen wurden, unterschiedlich waren (callback der Schätzung der Zeilen war disparate), die nicht nur mehr Bytes aufnimmt, sondern time zu verarbeiten. Ausführungszeit ca. 2x, aber die Bearbeitungszeit war 3x der Betrag.

Wie viel davon bezieht sich auf Ihren processor vs SQL server-Protokolle? Wahrscheinlich einige oder eine Menge davon (diese Abfrage wurde auf einem alten EDU Lenovo Laptop mit Windows 10, DuoCore 1.64Ghz, 16GB DDR3) ausgeführt. Obwohl die Besonderheiten bin ich unqualifiziert zu beantworten.

Dennoch können wir eine Sache abschließen: Die Schätzung von Zeilen von SQL server hat Auswirkungen auf den Client und data, die gesendet / empfangen wurden .

Es ist nicht möglich, Ihnen Einzelheiten ohne weitere data zu geben, beginnend mit einem Ausführungsplan für beide Abfragen.

Einige allgemeine Gründe:

  1. Wie du gesagt hast, gibt es doppelt so viele Bytes, um beim Scannen zu lesen
  2. Die Anzahl der Seitenlasten erhöht sich
  3. Die Menge an notwendigem memory wird erhöht, was zu Überlauf zu Plattenoperationen führen kann
  4. Die Menge der CPU kann sich erhöhen, die auf der Grundlage von OS- oder SQL-Einstellungen begrenzt werden könnte und CPU-Wartezeiten verursacht.

Unicode-Vergleichsregeln sind viel komplizierter als ascii-Regeln.

Die Wirkung von Unicode-data auf die performance wird durch eine Vielzahl von Faktoren kompliziert, die die folgenden umfassen:

  • Der Unterschied zwischen Unicode-Sortierregeln und Nicht-Unicode-Sortierregeln
  • Der Unterschied zwischen der sorting von Doppel-Byte- und Einzelbyte-character
  • Codepage-Konvertierung zwischen Client und server

Ref: https://msdn.microsoft.com/en-us/library/ms189617.aspx

Sie können dies bestätigen, indem Sie die sorting der Spalte in Binärdatei ändern.

 SELECT * FROM #temp2 where col1 COLLATE Latin1_General_Bin2 like '%str%' 

Schließlich, einige Überlegungen, wenn Sie NVARCHAR verwenden müssen und die performance verbessern wollen.

  • Betrachten Sie die datakomprimierung. Wenn Sie die Seite / Zeile in SQL server komprimieren, werden Ihre nvarchar cols automatisch auch UNICODE komprimiert. () https://msdn.microsoft.com/en-us/library/ee240835(v=sql.105).aspx
  • Betrachten Sie die Veränderung der Kollation. Dies hat Auswirkungen auf die sorting / search. So testing Sie, ob es gewünschte Ergebnisse über Sprachen / character gibt.

Wie in einigen der anderen Beiträge vorgeschlagen, ist der größte Einfluss auf Ihre performance in diesem Szenario die Unicode-Vergleichsregeln. Sie können das Problem in Bezug auf "LIKE" Vergleichsabfragen umgehen, indem Sie ein nicht beharrtes berechnetes Feld mit einer binären sorting zu Ihrer Tabelle hinzufügen:

 ALTER TABLE tblNVarcharNoIndex ADD ValueColBin AS UPPER(Value) COLLATE Latin1_General_100_Bin2; 

Anstatt Ihr persistiertes datafeld abzufragen, können Sie nun das berechnete Feld abfragen. Beachten Sie, dass Binärkollation Groß- und Kleinschreibung beachtet wird, um Fallunempfindliche Suchvorgänge durchzuführen, müssen Sie Ihre Suchzeichenfolgen in Großbuchstaben umwandeln. Das folgende Beispiel zeigt, wie die aktualisierte Abfrage aussieht:

 DECLARE @compare NVARCHAR(10) = N'%AB%' SELECT [Id] ,[Value] FROM tblNVarcharNoIndex WHERE [ValueColBin] LIKE @compare collate Latin1_General_100_Bin2 

Es wird noch ein Performance-Hit sein, aber es sollte innerhalb des erwarteten Bereichs von 1,5 bis 2 langsamer sein (zumindest theoretisch). Beachten Sie, dass diese Methode eine höhere CPU-Kosten entstehen wird.

Abfrage, die einen Varchar-Parameter verwendet, sucht ein Index aufgrund von Spaltenkollationssätzen.

Abfrage, die einen Nvarchar-Parameter verwendet, führt einen Index-Scan aufgrund von Spalten-sortingssätzen aus.

Die Grundregel zu folgen ist Scans sind schlecht, Seeks sind gut.

Index-Scan

Wenn SQL server einen Scan lädt, lädt er das object, das es von der Festplatte in den memory lesen möchte, liest dann dieses object von oben nach unten und sucht nach den datasätzen, die es braucht.

Index suchen

Wenn SQL server sucht es weiß, wo in den Index, dass die data werden, so dass es lädt den Index von Festplatte, geht direkt an den Teil des Index, den es braucht und liest, wo die data, die es benötigt endet . Dies ist offensichtlich eine viel effizientere Bedienung als ein Scan, da SQL bereits weiß, wo die data, die es sucht, befindet sich.

Wie kann ich einen Ausführungsplan ändern, um einen Seek anstelle eines Scans zu verwenden?

Wenn SQL server sucht nach Ihren data wahrscheinlich eines der größten Dinge, die SQL server-Switch von einem Suchlauf zu einem Scan machen wird, wenn einige der Spalten sind Sie suchen nicht in den Index, den Sie es verwenden möchten. In den meisten Fällen wird dies SQL server fallen zu einem Cluster-Index-Scan, da der Clustered-Index enthält alle Spalten in der Tabelle. Dies ist einer der größten Gründe (meiner Meinung nach zumindest), dass wir jetzt die Möglichkeit haben, Spalten in einem Index zu aktivieren, ohne diese Spalten zu den indizierten Spalten des Index hinzuzufügen. Durch das Einfügen der zusätzlichen Spalten in den Index erhöhen wir die Größe des Index, aber wir erlauben es SQL server, den Index zu lesen, ohne auf den gruppierten Index zurückzukehren, oder auf die Tabelle, die es selbst ist, diese Werte zu erhalten.

referenceen

Für Informationen über die Besonderheiten von jedem dieser Operatoren innerhalb eines SQL server Execution Plan siehe ….

https://msdn.microsoft.com/en-us/library/ms175184.aspx

https://technet.microsoft.com/en-us/library/ms190400.aspx