Lange habe ich auf die Gelegenheit und einen Anwendungsfall gewartet, um eine CLR Funktion für den SQL Server schreiben zu können. Für ein Migrationsprojekt habe ich nun einen Einsatzzweck gefunden. ;-)

Hauptgrund dafür ist, dass eine Anwendung abgelöst werden soll, die ihre Business Logik in C# implementiert hat. Der Code ist nach den gängigen Clean Code Regeln erstellt, das ist sofort ersichtlich, die Datenhaltung ist dafür sehr kreativ.

Da der imperative Code die Logik aus den Daten zusammenstellt, habe ich mich für den Ansatz entschieden, eine CLR Funktion für den SQL Server zu schreiben. Würde ich diese Logik in T-SQL abbilden, wäre die Performanz und Verständlichkeit grausam. Da eine grosse Datenmenge migriert werden muss, ist ein Ansatz notwendig, der einerseits performant läuft, anderseits wenig Aufwand verursacht.

So habe ich nun die Möglichkeit eine SQL Server Eigenschaft zu nutzen, die seit der Version 2005 existiert.

Gehen wir nun der Reihe nach vor. Zuerst wird ein Projekt vom Typ Klassenbibliothek angelegt. Anschliessend lege ich einen Schlüssel an, damit die Assembly auch signiert werden kann. Die Implementierung ist recht einfach. Es werden die Referenzen:

• System.Data.Sql
• System.Data.SqlTypes
• Microsoft.SqlServer.Server

benötigt. Das Grundgerüst der Klasse hat folgenden Aufbau:

Einstiegspunkt ist in meinem Beispiel die Methode Initialize. Diese wird mit dem Attribute SqlFunction versehen. Mit diesem wird festgelegt, dass die Methode FillRow ein Tupel für die Verwendung in T-SQL aufbereitet. Der erste Parameter enthält die Informationen dafür. Die eigentliche Logik beziehe ich dabei aus einer anderen Bibliothek und ist nicht Bestandteil des Codebeispiels.

Nachdem diese CLR Funktion erstellt ist, muss diese auf dem SQL-Server registriert werden. Dies erfolgt in mehreren Schritten. Zuerst wird die Assembly registriert und anschliessend eine Funktion erstellt, welche auf die Logik in der Assembly zeigt. Nachfolgendes Beispiel zeigt den Ablauf:

Falls die Fehlermeldung

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

erscheint, muss diese Option aktiviert werden.

Mit folgendem Befehl ist dies möglich:

Ich habe nun einen praktikablen und pragmatischen Weg, der auf die Situation abgestimmt mit akzeptabler Performanz die Daten (kreative Datenlogik) in auswertbarer Form für die Migration bereitstellt.

Mein nächster Schritt besteht darin das Ganze mit parallelisierter Ausführung weiter zu beschleunigen.

Weitere Informationen zum Thema:

• CLR Tabellenwertfunktionen
• CLR Aktivierung

Von Haus aus bringt das Entity Framework keine erweiterte Cache-Funktionalität mit. Wer für Projekte solche Szenarien benötigt, kann aber auf die EFProviderWrappers zurückgreifen. In früheren Beiträgen habe ich aus diesem Toolkit der Möglichkeiten des EFTracingProviders beschrieben. Vor allem gefällt mir hier, dass ich das generierte SQL an spezifische Vorstellungen anpassen kann. Es gibt DBA's die sind in dieser Hinsicht wenig kompromissbereit.

Es lassen sich durch dieses Toolkit auch eigene Provider realisieren.

Mit dem EFCachingProvider besteht die Möglichkeit, dem Entity Framework einen 2nd-Level Cache zu verpassen. So kann es auch weiter zu NHibernate aufschliessen.

Cachevarianten die zur Verfügung stehen sind:

• InMemoryCache (einfache Variante, die nicht sehr leistungsfähig ist)
• Der ASP.NET Cache
• und Velocity (AppFabric)

Gerade mit Velocity steht dann ein sehr mächtiges Caching zur Verfügung. Haufig werden jedoch Probleme mit dem Caching berichtet, wenn View-Generation benutzt wird, da dass Caching mit den Hashes nicht umgehen kann. Dieses Phänomen konnte ich nicht reproduzieren, es liegt evtl. daran, dass ich diese Views mittels T4 und dem zur Verfügungen stehenden EntityViewGenerator erstellen lasse.

Mit der CachingPolicy besteht zudem die Möglichkeit, dass ich definieren kann, ob einzelne Entitäten oder alle zwischengespeichert werden sollen, es ist jedoch nicht möglich, eine Art "partitioniertes" Caching auf einzelne Entitäten eines Sets zu definieren.

Damit das Caching funktioniert, muss der entsprechende Wrapper um die normale Verbindung "gewickelt" werden, das geschieht beim Konstruktor mit folgender Helperklasse:

Provider-Wrapper C#
public ORMSamplesEntities()
            : base(EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(
                        ConnectionString,
                        "EFTracingProvider",
                        "EFCachingProvider"
                ), ContainerName)
        {
            Initialize();
        }

In diesem Szenario wird die Verbindung mit dem Tracing- und Caching-Provider umhüllt. In meinem Context habe ich zudem 2 statische Eigenschaften angelegt, die den Zugriff auf die Einstellungen an die Basisklasse weiterleiten:

Eigenschaften für die Cache-Initialisierung C#
public static ICache DefaultCache
{
  get
  {
    return EFCachingProviderConfiguration.DefaultCache;
  }
    		
  set
  {
    EFCachingProviderConfiguration.DefaultCache = value;
  }
}    	
    	
public static CachingPolicy DefaultCachingPolicy
{
  get
  {
    return EFCachingProviderConfiguration.DefaultCachingPolicy;
  }
    		
  set
  {
    EFCachingProviderConfiguration.DefaultCachingPolicy = value;
  }
}

Ein interessantes Detail, diese Eigenschaften müssen vor der Erstellung einer Instanz des Kontexts gesetzt werden, da mit diesen Angaben die Wrapper in den Konstruktoren gefüttert werden.

Machen wir ein kleines Testbeispiel mithilfe des SQL-Profilers und einem Unit-Test. Es soll 4-mal die gleiche Order geladen werden. Mit den Standardmöglichkeiten des Entity Frameworks wird dann die gleiche Abfrage 4-mal an den Datenbankserver gesendet.

Test ohne Cache C#
    [TestMethod]
    public void NonCachingProviderTest()
    {
      // Arrange
      var ctx = new ORMSamplesEntities();

      // Act
      var result = (from o in ctx.Orders.Include("OrderItems")
                   where o.Id == 1
                   select o).ToList();
      
      var result2 = (from o in ctx.Orders.Include("OrderItems")
                     where o.Id == 1
                     select o).ToList();

      var result3 = (from o in ctx.Orders.Include("OrderItems")
                     where o.Id == 1
                     select o).ToList();

      var result4 = (from o in ctx.Orders.Include("OrderItems")
                     where o.Id == 1
                     select o).ToList();
    
      // Assert
      // see Sql-Profiler log ;-)
    }

Führen wir den Test erneut aus, diesmal jedoch mit aktivierten 2nd-Level Cache und dem InMemory-Cache.

Test mit Cache C#
    [TestMethod]
    public void CachingProviderInMemoryTest()
    {
      ORMSamplesEntities.DefaultCache = new InMemoryCache();
      ORMSamplesEntities.DefaultCachingPolicy = CachingPolicy.CacheAll;

      // Arrange
      var ctx = new ORMSamplesEntities();

      // Act
      var result = (from o in ctx.Orders.Include("OrderItems")
                    where o.Id == 1
                    select o).ToList();

      var result2 = (from o in ctx.Orders.Include("OrderItems")
                     where o.Id == 1
                     select o).ToList();

      var result3 = (from o in ctx.Orders.Include("OrderItems")
                     where o.Id == 1
                     select o).ToList();

      var result4 = (from o in ctx.Orders.Include("OrderItems")
                     where o.Id == 1
                     select o).ToList();

      InMemoryCache cache = ORMSamplesEntities.DefaultCache as InMemoryCache;
      // Assert
      Assert.AreEqual(3, cache.CacheHits);
      Assert.AreEqual(1, cache.CacheMisses);
      Assert.AreEqual(0, cache.CacheItemInvalidations);
      Assert.AreEqual(1, cache.CacheItemAdds);

      // see Sql-Profiler log
    }

Die Provider-Wrapper sind nicht nur ein "nettes Spielzeug", sondern auch sehr hilfreich, da diese fehlende Funktionalitäten für das Entity Framework bereitstellen.

Ich gehe stark davon aus, das mit der nächsten Version - ich rede hier jetzt nicht von der EF CTP 4 - diese Funktionalitäten fester Bestandteil des Entity Frameworks werden.

Hier soll jetzt die Aufgabe darin bestehen, zu allen Bestellungen die Anzahl der Bestellpositionen und das Total zu ermitteln.

Die Linq-Abfrage dazu:

C# (Linq)
var resultList = 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)
                        };

Für diese Abfrage wird folgendes SQL-Statement erstellt:

SQL (EF-Generiert)
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] -- Sub-Select
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]

Dieses Statement verwendet ein Sub-Select für die Bildung der Summe für das Total. Handgeschrieben könnte es in etwa so aussehen:

SQL
SELECT
    o.Id
    , COUNT(oi.Id) as C1 -- AnzahlItems
    , SUM(oi.Price * oi.Quantity) as C2 --Total
FROM QuerySchema.[Order] o
INNER JOIN QuerySchema.OrderItem oi ON oi.OrderId = o.Id
GROUP BY
o.Id

Mit dem EFTracingProvider wird es möglich, das SQL auszutauschen. So kann mithilfe eines Dictionary das bestehende SQL durch das effizientere ersetzt werden. Quasi kann so das Query Rewrite simuliert werden.

In diesem Beispiel kann im Kontext des Event CommandExecuting abonniert werden. Der Codeausschnitt dafür sieht so aus:

C# (Codeausschnitt)
   [TestMethod]
    public void ChangeGeneratedSqlTest()
    {
      // Arrange
      string fileContent;
      string efGeneratedSqlString;
      var ctx = new ORMSamplesEntities();
      ctx.CommandExecuting += ctx_CommandExecuting;
      
      this.queryRewriteSqlString = @"SELECT
	            o.Id
	            , COUNT(oi.Id) as C1 -- AnzahlItems
	            , SUM(oi.Price * oi.Quantity) as C2 --Total
            FROM QuerySchema.[Order] o
            INNER JOIN QuerySchema.OrderItem oi ON oi.OrderId = o.Id
            GROUP BY
            o.Id";

      // Act
      // ctx_CommandExecuting
      var resultList = 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)
                        };

      efGeneratedSqlString = (resultList as ObjectQuery).ToTraceString();
      // ctx_CommandExecuting
      resultList.ToList();

      fileContent = File.ReadAllText(this.filePath);

      // Assert
      Assert.IsTrue(fileContent.Contains(this.queryRewriteSqlString));
      Assert.IsFalse(fileContent.Contains(efGeneratedSqlString));
      ctx.Dispose();
    }

    private void ctx_CommandExecuting(object sender, CommandExecutionEventArgs e)
    {
      e.Command.CommandText = this.queryRewriteSqlString;
    }

Mit diesem Event sind aber auch andere Szenarien denkbar.

Ein Hinweis zum Schluss, dieses Beispiel rechtfertigt in meinen Augen nicht unbedingt, das SQL zu ersetzen, es soll nur die Möglichkeit des simulierten Query Rewrites mit dem EFTracingProvider aufzeigen. Zum Einsatz kann es auch bei kompromisslosen Richtlinien kommen, zum Beispiel wenn in einer OLTP-Umgebung der Einsatz von Sub-Selects nicht erlaubt ist. Dann ist diese Variante effizienter als der Austausch des O/R-Mappers in einem fortgeschrittenen Projektstatus, nur wegen ein paar einzelner Statements.

Alternativ kann dafür aber auch ExecuteStoreQuery bzw. eine gespeicherte Prozedur verwendet werden. Es muss jedoch dabei berücksichtigt werden, dass diese beiden Varianten nicht mit dem Laden der Objekthierarchien klarkommen und durch das Nachladen das Select N+1-Problem auftreten kann.

Query Rewrite ist eigentlich eine Funktionalität von Oracle in Verbindung mit materialisierten Sichten. Mit dem SQL-Server funktioniert das in Verbindung mit indexierten Sichten nur, wenn die Enterprise- oder Developer-Version verwendet wird.

Die Funktionsweise ist denkbar einfach, wenn eine Anwendung Code mit vielen Joins an die Datenbank sendet, kann mit Hilfe einer materialsierten/indexierten Sicht diese optimiert werden. Der Vorteil dieses Features, es können Optimierungen vorgenommen werden, ohne den Code der zugrunde liegenden Applikation anpassen zu müssen.

Am Beispiel eines TPT-Szenarios probiere ich aus, wie sich Query Rewrite mit dem Entity Framework nutzen lässt. Dazu zuerst eine handgeschriebene SQL-Abfrage.

SQL 
SELECT     
	ProductTPT.Product.ProductId
	, ProductTPT.Product.Name
	, ProductTPT.Product.Description
	, ProductTPT.Book.ISBN10
	, ProductTPT.Book.ISBN13
	, ProductTPT.Book.LanguageCd
	, ProductTPT.Book.Pages
	, ProductTPT.EBook.EBookId
	, ProductTPT.EBook.Filename
FROM ProductTPT.Product 
INNER JOIN ProductTPT.Book ON ProductTPT.Product.ProductId = ProductTPT.Book.BookId 
INNER JOIN ProductTPT.EBook ON ProductTPT.Book.BookId = ProductTPT.EBook.EBookId

Es kann Situationen geben, da führen Join-Abfragen aus der Anwendung heraus zu Performanzeinbussen. Bei Standard-Software besteht dann häufig das Problem, dass diese Abfragen innerhalb der Anwendung nicht optimiert werden können. Hier greift ein DBA häufig auf indexierte Sichten zurück.

Beim SQL-Server muss dazu eine Sicht mit Schemabinding und ein passender gruppierter Index angelegt werden.

SQL (Indexierte View)
CREATE VIEW [dbo].[vwEBook]
WITH SCHEMABINDING
AS
SELECT     
	ProductTPT.Product.ProductId
	, ProductTPT.Product.Name
	, ProductTPT.Product.Description
	, ProductTPT.Book.ISBN10
	, ProductTPT.Book.ISBN13
	, ProductTPT.Book.LanguageCd
	, ProductTPT.Book.Pages
	, ProductTPT.EBook.EBookId
	, ProductTPT.EBook.Filename
FROM ProductTPT.Product 
INNER JOIN ProductTPT.Book ON ProductTPT.Product.ProductId = ProductTPT.Book.BookId 
INNER JOIN ProductTPT.EBook ON ProductTPT.Book.BookId = ProductTPT.EBook.EBookId

Mit diesem Ausflug ist Query Rewrite erklärt. Es gibt aber auch Grenzen dieser Funktionalität, diese werden schnell bei Abfragen mit Inline-Views erreicht, so wie es das Entity Framework auch fabriziert. Betrachten wir nun, was für SQL das Entity Framework an die Datenbank sendet.

Die Abfrage in der Anwendung

C# (Linq-Abfrage)
var ebookList = ctx.Products.OfType<EBook>().ToList();

führt zu folgender SQL-Abfrage:

SQL (Entity Framework)
SELECT 
'0X0X0X' AS [C1], 
[Extent1].[EBookId] AS [EBookId], 
[Extent2].[Name] AS [Name], 
[Extent2].[Description] AS [Description], 
[Extent3].[ISBN10] AS [ISBN10], 
[Extent3].[ISBN13] AS [ISBN13], 
[Extent3].[LanguageCd] AS [LanguageCd], 
[Extent3].[Pages] AS [Pages], 
[Extent1].[Filename] AS [Filename]
FROM   [ProductTPT].[EBook] AS [Extent1]
INNER JOIN [ProductTPT].[Product] AS [Extent2] ON [Extent1].[EBookId] = [Extent2].[ProductId]
INNER JOIN [ProductTPT].[Book] AS [Extent3] ON [Extent1].[EBookId] = [Extent3].[BookId]

Der Unterschied, die Spalte C1 erzeugt zusätzlich einen Compute Scalar-Task, der jedoch keine Kosten verursacht. Der Optimizer merkt, dass er mit der indexierten Sicht die Anfrage viel schneller abarbeiten kann und verwendet diese.

Dieses Beispiel verdeutlicht, dass auch die Datenbank selbst gute Dienste bei der Optimierung leisten kann, sie müssen nur genutzt werden.

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

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.