EFTracingProvider – SQL-Log im Database Engine Tuning Advisor (DETA) verwenden

In meinen letzten Ausführungen habe ich erwähnt, dass der Output des EFTracingProvider in Verbindung mit dem Database Engine Tuning Advisor gute Dienste leisten kann.

Betrachten wir den Einsatz an folgendem Beispiel. Innerhalb zweier Tests mache ich ein paar komplexere Abfragen, deren SQL-Statements in eine Textdatei geschrieben werden. Wer das Ganze selbst ausprobieren will, kann das zugrunde liegende Datenbankskript auf http://www.dnug-bern.ch herunterladen.

Die Linq-Abfragen sind in folgenden Test ersichtlich:

C# Linq-Abfragen im Unittest

namespace EFProviderToolkitTemplateTest
{
  using System;
  using System.Text;
  using System.Collections.Generic;
  using System.Linq;
  using Microsoft.VisualStudio.TestTools.UnitTesting;
  using System.IO;
  using EFProviderToolkitTemplate;
  using System.Data.Objects;

  [TestClass]
  public class UnitTest2
  {
    
    [TestMethod]
    public void ComplexLinqQueryTest()
    {
      // Arrange
      string sql1;
      ORMSamplesEntities.LogToFile = @"C:\Users\db\Desktop\EfSqlLogTest.txt";

      // Act
      using (var ctx = new ORMSamplesEntities())
      {
        var kpiList = (from o in ctx.Orders.Include("OrderItems")
                       select new
                       {
                         Id = o.Id,
                         AnzahlItems = o.OrderItems.Count,
                         Total = o.OrderItems.Sum(s => s.Price * s.Quantity)
                       });
        sql1 = (kpiList as ObjectQuery).ToTraceString();
        kpiList.ToList();
      }

      string fileContent = File.ReadAllText(ORMSamplesEntities.LogToFile);

      // Assert
      Assert.IsTrue(fileContent.Contains(sql1));
    }

    [TestMethod]
    public void PagedLinqQueryTest()
    {
      // Arrange
      string sql1;
      int size = 20;
      int page = 4;
      ORMSamplesEntities.LogToFile = @"C:\Users\db\Desktop\EfSqlLogTest.txt";

      // Act
      using (var ctx = new ORMSamplesEntities())
      {
        var productList = ctx.Products.OrderBy(p => p.Id).
          Skip(page * size).Take(size);
        sql1 = (productList as ObjectQuery).ToTraceString();
        productList.ToList();
      }

      string fileContent = File.ReadAllText(ORMSamplesEntities.LogToFile);

      // Assert
      Assert.IsTrue(fileContent.Contains(sql1));
    }
  }
}

Die Ausgabedatei mit den SQL-Statements enthält folgenden Inhalt:

SQL (EF)

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[C1] AS [C1], 
(SELECT 
	SUM([Filter2].[A1]) AS [A1]
	FROM ( SELECT 
		[Extent3].[Price] * [Extent3].[Quantity] AS [A1]
		FROM [QuerySchema].[OrderItem] AS [Extent3]
		WHERE [Project1].[Id] = [Extent3].[OrderId]
	)  AS [Filter2]) AS [C2]
FROM ( SELECT 
	[Extent1].[Id] AS [Id], 
	(SELECT 
		COUNT(1) AS [A1]
		FROM [QuerySchema].[OrderItem] AS [Extent2]
		WHERE [Extent1].[Id] = [Extent2].[OrderId]) AS [C1]
	FROM [QuerySchema].[Order] AS [Extent1]
)  AS [Project1]

SELECT TOP (20) 
[Extent1].[Id] AS [Id], 
[Extent1].[ProductTypeNbr] AS [ProductTypeNbr], 
[Extent1].[Title] AS [Title], 
[Extent1].[Price] AS [Price], 
[Extent1].[Filename] AS [Filename], 
[Extent1].[ISBN10] AS [ISBN10], 
[Extent1].[ISBN13] AS [ISBN13], 
[Extent1].[Pages] AS [Pages], 
[Extent1].[Weight] AS [Weight], 
[Extent1].[LanguageCD] AS [LanguageCD]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[ProductTypeNbr] AS [ProductTypeNbr], [Extent1].[Title] AS [Title], [Extent1].[Price] AS [Price], [Extent1].[Filename] AS [Filename], [Extent1].[ISBN10] AS [ISBN10], [Extent1].[ISBN13] AS [ISBN13], [Extent1].[Pages] AS [Pages], [Extent1].[Weight] AS [Weight], [Extent1].[LanguageCD] AS [LanguageCD], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
	FROM [QuerySchema].[Product] AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 80
ORDER BY [Extent1].[Id] ASC

Also geht es daran, den Output im Database Engine Tuning Advisor zu verwenden. Dazu wird dieser gestartet und folgende Maske erscheint:

: Abbildung 1 DETA mit neuer Sitzung nach dem Start

Als Erstes wird der Pfad zur Datei ausgewählt. Bei der Datenbank zur Analyse muss in meinen Fall die ORMSamples ausgewählt werden, bei der Auflistung der Datenbank ebenfalls.

: Abbildung 2 Einstellungen im DETA

Nach diesen Einstellungen kann die Schaltfläche "Analyse starten" gedrückt werden. Anschliessend werden die Empfehlungen und die geschätzte Verbesserung angezeigt.

: Abbildung 3 Empfehlungen des DETA

In diesem Fall kann eine Verbesserung erzielt werden, indem ein Index auf der Order-Tabelle und zwei auf der OrderItem-Tabelle erstellt werden.

Es lassen sich diverse Log-Files verwenden, die SQL enthalten bzw. die Trace-Dateien des SQL Server Profilers, es muss also nicht zwingend der Output von EFTracingProvider sein.

Ein weiteres Feature zur Optimierung von SQL mithilfe des EFTracingProvider gibt es noch, aber mehr dazu ein anderes Mal.