Entity Framework – Performanz mit Compiled Query

Um im Entity Framework die Performanz verbessern zu können, besteht seit EF 1 die Möglichkeit der kompilierten Abfrage. Dieser Ansatz lohnt sich bei komplexen Abfragen, da der Expression Tree nicht jedes Mal abgearbeitet wird, um daraus das SQL-Statement zu generieren. Alternativ dazu kann auch eSQL oder im EF 4 ExecuteStoreQuery genutzt werden. Was mit dieser Funktionalität nicht erreicht werden kann, die Datenbank – SQL Server oder Oracle – kann die Abfrage nicht schneller oder besser ausführen.

Zum Beispiel könnte eine Abfrage so aussehen:

C#
public List<Mandator> GetMandatorsWithBusinessUnits()
{
    int minCount = 10;

    var result = from m in this.Mandators.Include("BusinessUnits")
                 where m.BusinessUnits.Count > minCount
                 select m;

    return result.ToList();
}

In meinem Beispiel sieht das SQL-Statement dazu folgendermassen aus:

T-SQL
exec sp_executesql N'SELECT 
[Project2].[maAutoID] AS [maAutoID], 
[Project2].[maName] AS [maName], 
[Project2].[maSubDirectory] AS [maSubDirectory], 
[Project2].[maBKBeforeRBK] AS [maBKBeforeRBK], 
[Project2].[maBKPercent] AS [maBKPercent], 
[Project2].[maImprint] AS [maImprint], 
[Project2].[maPhone] AS [maPhone], 
[Project2].[maStreet] AS [maStreet], 
[Project2].[maPOBox] AS [maPOBox], 
[Project2].[maPC] AS [maPC], 
[Project2].[maCity] AS [maCity], 
[Project2].[maURL] AS [maURL], 
[Project2].[maVendorAccount] AS [maVendorAccount], 
[Project2].[maAccountingCostCenter1] AS [maAccountingCostCenter1], 
[Project2].[maAccountingCostCenter2] AS [maAccountingCostCenter2], 
[Project2].[C1] AS [C1], 
[Project2].[buAutoID] AS [buAutoID], 
[Project2].[buMandatorAutoID] AS [buMandatorAutoID], 
[Project2].[buName] AS [buName], 
[Project2].[buInactive] AS [buInactive]
FROM ( SELECT 
	[Project1].[maAutoID] AS [maAutoID], 
	[Project1].[maName] AS [maName], 
	[Project1].[maSubDirectory] AS [maSubDirectory], 
	[Project1].[maBKBeforeRBK] AS [maBKBeforeRBK], 
	[Project1].[maBKPercent] AS [maBKPercent], 
	[Project1].[maImprint] AS [maImprint], 
	[Project1].[maPhone] AS [maPhone], 
	[Project1].[maStreet] AS [maStreet], 
	[Project1].[maPOBox] AS [maPOBox], 
	[Project1].[maPC] AS [maPC], 
	[Project1].[maCity] AS [maCity], 
	[Project1].[maURL] AS [maURL], 
	[Project1].[maVendorAccount] AS [maVendorAccount], 
	[Project1].[maAccountingCostCenter1] AS [maAccountingCostCenter1], 
	[Project1].[maAccountingCostCenter2] AS [maAccountingCostCenter2], 
	[Extent3].[buAutoID] AS [buAutoID], 
	[Extent3].[buMandatorAutoID] AS [buMandatorAutoID], 
	[Extent3].[buName] AS [buName], 
	[Extent3].[buInactive] AS [buInactive], 
	CASE WHEN ([Extent3].[buAutoID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
	FROM   (SELECT 
		[Extent1].[maAutoID] AS [maAutoID], 
		[Extent1].[maName] AS [maName], 
		[Extent1].[maSubDirectory] AS [maSubDirectory], 
		[Extent1].[maBKBeforeRBK] AS [maBKBeforeRBK], 
		[Extent1].[maBKPercent] AS [maBKPercent], 
		[Extent1].[maImprint] AS [maImprint], 
		[Extent1].[maPhone] AS [maPhone], 
		[Extent1].[maStreet] AS [maStreet], 
		[Extent1].[maPOBox] AS [maPOBox], 
		[Extent1].[maPC] AS [maPC], 
		[Extent1].[maCity] AS [maCity], 
		[Extent1].[maURL] AS [maURL], 
		[Extent1].[maVendorAccount] AS [maVendorAccount], 
		[Extent1].[maAccountingCostCenter1] AS [maAccountingCostCenter1], 
		[Extent1].[maAccountingCostCenter2] AS [maAccountingCostCenter2], 
		(SELECT 
			COUNT(1) AS [A1]
			FROM [dbo].[tbBusinessUnit] AS [Extent2]
			WHERE [Extent1].[maAutoID] = [Extent2].[buMandatorAutoID]) AS [C1]
		FROM [dbo].[tbMandator] AS [Extent1] ) AS [Project1]
	LEFT OUTER JOIN [dbo].[tbBusinessUnit] AS [Extent3] ON [Project1].[maAutoID] = [Extent3].[buMandatorAutoID]
	WHERE [Project1].[C1] > @p__linq__0
)  AS [Project2]
ORDER BY [Project2].[maAutoID] ASC, [Project2].[C1] ASC',N'@p__linq__0 int',@p__linq__0=10

Wenn Abfragen in dieser Form sehr häufig ausgeführt werden, kann der Einsatz der kompilierten Abfragen etwas bringen.

Der Code erfordert eine Anpassung, damit der Expression Tree nur einmal ausgewertet wird. Hierbei gefällt mir der Aufwand nicht, da es negative Auswirkungen auf die Lesbarkeit des Codes hat. 

C#
private static Func<ContentEntities, int, IQueryable<Mandator>> COMPILEDMANDATOR;

public List<Mandator> GetMandatorsWithBusinessUnitsCompiled()
{
    int minCount = 10;

    if (COMPILEDMANDATOR == null)
    {
        COMPILEDMANDATOR = CompiledQuery.Compile<ContentEntities, int, IQueryable<Mandator>>(
            (ctx, minCountParameter) => from m in ctx.Mandators.Include("BusinessUnits")
                        where m.BusinessUnits.Count > minCountParameter
                        select m);
    }

    return COMPILEDMANDATOR.Invoke(this, minCount).ToList();
}

Diese Form der Abfrage braucht ein geübtes Auge, jedoch wird das Ziel erreicht, wenn die statische Variable deklariert ist, wird der Expression Tree nicht mehr ausgewertet.

Wie bereits erwähnt, kann dieses Feature keine Verbesserungen auf Seite der Datenbank bewirken, wenn das Problem also beim generierten SQL-Statement liegt, wird dieses Feature keine Verbesserung bringen.