SQL server Abfrage Geschwindigkeit ist Unterschied bei der Filterung durch verschiedene Spalten

Ich habe eine database mit nur einer Tabelle Logs , die Spalten enthält:

  • Id (PK Clustered, int, nicht null),
  • ServiceName (nvarchar (255), nicht null) und einige andere Spalten wie
  • TaskVariant (nvarchar (1024)),
  • Source (nvarchar (1024)).

Ich habe einen Index INDEX_SERVICENAME (Non-Unique, Non-Clustered) in der Spalte ServiceName und das enthält alle Spalten außer Id, ServiceName .

  • databasegröße ca. 4 GB.
  • Tabelle enthält ca. 3 500 000 Reihen.
  • Tabelle enthält etwa 1 400 000 Zeilen mit Source = N'IpJob '.
  • Tabelle enthält ca. 2 400 000 Zeilen mit TaskVariant = N'Ip '.
  • Tabelle enthält etwa 600 000 Zeilen mit ServiceName = '1' und TaskVariant = N'Ip '.
  • Tabelle enthält etwa 350 000 Zeilen mit ServiceName = '1' und Source = N'IpJob '.

Problem:

Ich möchte alle Spalten aus der Tabellenfilterung nach ServiceName und nach TaskVariant oder nach Source mit Paging auswählen. Meine ursprüngliche Abfrage ist für die Auswahl der letzten 100 Elemente Filterung von Source ist:

 SELECT TOP (100) [Filter1].[Id] AS [Id], [Filter1].[Date] AS [Date], [Filter1].[Data] AS [Data], [Filter1].[ServiceName] AS [ServiceName], [Filter1].[LogLevel] AS [LogLevel], [Filter1].[StackTrace] AS [StackTrace], [Filter1].[TaskVariant] AS [TaskVariant], [Filter1].[Source] AS [Source], [Filter1].[Message] AS [Message] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Data] AS [Data], [Extent1].[ServiceName] AS [ServiceName], [Extent1].[LogLevel] AS [LogLevel], [Extent1].[StackTrace] AS [StackTrace], [Extent1].[TaskVariant] AS [TaskVariant], [Extent1].[Source] AS [Source], [Extent1].[Message] AS [Message], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number] FROM [dbo].[Logs] AS [Extent1] WHERE (@serviceName = [Extent1].[ServiceName]) AND (@source = [Extent1].[Source]) ) AS [Filter1] WHERE [Filter1].[row_number] > 0 ORDER BY [Filter1].[Id] DESC 

Diese Abfrage funktioniert sehr schnell ~ 00:00:00 Uhrzeit.

Aber als ich versuchte, nach TaskVariant zu filtern, TaskVariant Abfrage ~ 00:02:18 Minuten (nächste Abfrage).

 SELECT TOP (100) [Filter1].[Id] AS [Id], [Filter1].[Date] AS [Date], [Filter1].[Data] AS [Data], [Filter1].[ServiceName] AS [ServiceName], [Filter1].[LogLevel] AS [LogLevel], [Filter1].[StackTrace] AS [StackTrace], [Filter1].[TaskVariant] AS [TaskVariant], [Filter1].[Source] AS [Source], [Filter1].[Message] AS [Message] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Data] AS [Data], [Extent1].[ServiceName] AS [ServiceName], [Extent1].[LogLevel] AS [LogLevel], [Extent1].[StackTrace] AS [StackTrace], [Extent1].[TaskVariant] AS [TaskVariant], [Extent1].[Source] AS [Source], [Extent1].[Message] AS [Message], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number] FROM [dbo].[Logs] AS [Extent1] WHERE (@serviceName = [Extent1].[ServiceName]) AND (@taskVariant = [Extent1].[TaskVariant]) ) AS [Filter1] WHERE [Filter1].[row_number] > 0 ORDER BY [Filter1].[Id] DESC 

Frage: Warum läuft die zweite Abfrage so viel langsamer aus und wie kann man dieses Problem lösen?

Vielen Dank für Ihre Anregungen.

Ausführungspläne 1

Der Unterschied in der Ausführungszeit, die Sie sehen, liegt in erster Linie daran, dass der erste einen Index hat und der zweite nicht. Warum ist es so ein großer Unterschied, es ist wahrscheinlich die Tatsache, dass da es einen Index gibt, bedeutet das, dass die Werte sortiert sind.

Da die Werte sortiert sind, können Sie sehr effiziente Stringsuchalgorithmen verwenden, die die Anzahl der Operationen bei der Filterung von Größenordnungen kleiner machen können.

Darüber hinaus gibt es viele andere properties, die dies beeinflussen können. Es ist möglich, dass der gesamte Index im memory ist, während die Tabellen-data nicht sind, also kann die Filterung in der ersten Abfrage alles im memory durchgeführt werden und berühre die Scheibe nicht, wo wie die andere nicht.

Der Index arbeitet wie eine Hierarchie / tree, wobei die Ebenen den Spalten entsprechen.

Wenn also dein Index auf ServiceName, TaskVariant , kannst du schnell zu bestimmten ServiceName s filtern, weil das die oberste Ebene im tree ist.

Aber wenn du versuchst, nach TaskVariant zu filtern, TaskVariant du nun den ganzen Index TaskVariant : Du kannst nicht einfach zu einer bestimmten TaskVariant springen, weil die gleiche TaskVariant unter verschiedenen ServiceName s ist.

Wenn Sie auf TaskVariant filtern möchten, benötigen Sie einen anderen Index, der mit TaskVariant beginnt. NB: Erstellen Sie nicht nur ganze Indizes für jede Spalte: Jeder Index nimmt zusätzlichen Platz ein und erfordert mehr Arbeit an UPDATE s und INSERT s