LINQ wurde als neue leistungsstarke Sprache zur Datenmanipulation in .NET eingeführt. LINQ to SQL als Teil davon ermöglicht eine recht bequeme Kommunikation mit einer Datenbank, beispielsweise über Entity Framework. Allerdings vergessen Entwickler häufig, darauf zu achten, welche SQL-Abfrage der queryable provider in Ihrem Fall — Entity Framework — generieren wird.
Betrachten wir zwei grundlegende Punkte anhand eines Beispiels.
Dazu erstellen wir in SQL Server die Datenbank Test und erzeugen darin mit der folgenden Abfrage zwei Tabellen:
Tabellen erstellen
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ref](
[ID] [int] NOT NULL,
[ID2] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_Ref] 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]
GO
ALTER TABLE [dbo].[Ref] ADD CONSTRAINT [DF_Ref_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Ref_ID] [int] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
[Ref_ID2] [int] NOT NULL,
CONSTRAINT [PK_Customer] 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]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_Ref_ID] DEFAULT ((0)) FOR [Ref_ID]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
Nun füllen wir die Tabelle Ref mit dem folgenden Skript:
Befüllung der Tabelle Ref
USE [TEST]
GO
DECLARE @ind INT=1;
WHILE(@ind<1200000)
BEGIN
INSERT INTO [dbo].[Ref]
([ID]
,[ID2]
,[Name])
SELECT
@ind
,@ind
,CAST(@ind AS NVARCHAR(255));
SET @ind=@ind+1;
END
GO
Analog fügen wir die Tabelle Customer mit dem folgenden Skript hinzu:
Befüllung der Tabelle Customer
VERWENDEN [TEST]
GO
ERKLÄREN @ind INT=1;
ERKLÄREN @ind_ref INT=1;
WÄHREND(@ind<=12000000)
BEGINN
WENN(@ind%3=0) SET @ind_ref=1;
SONST WENN (@ind%5=0) SET @ind_ref=2;
SONST WENN (@ind%7=0) SET @ind_ref=3;
SONST WENN (@ind=0) SET @ind_ref=4;
SONST WENN (@ind=0) SET @ind_ref=5;
SONST WENN (@ind=0) SET @ind_ref=6;
SONST WENN (@ind=0) SET @ind_ref=7;
SONST WENN (@ind=0) SET @ind_ref=8;
SONST WENN (@ind=0) SET @ind_ref=9;
SONST WENN (@ind=0) SET @ind_ref=10;
SONST WENN (@ind=0) SET @ind_ref=11;
SONST SET @ind_ref=@ind90000;
EINFÜGEN IN [dbo].[Customer]
([ID]
,[Name]
,[Ref_ID]
,[Ref_ID2])
AUSWÄHLEN
@ind,
CAST(@ind AS NVARCHAR(255)),
@ind_ref,
@ind_ref;
SET @ind=@ind+1;
END
GO
Somit haben wir zwei Tabellen erhalten, von denen eine über 1 Million Datensätze und die andere mehr als 10 Millionen Datensätze enthält.
Jetzt müssen wir in Visual Studio ein Testprojekt Visual C# Console App (.NET Framework) erstellen:
Um mit der Datenbank zu interagieren, müssen wir eine Bibliothek für Entity Framework hinzufügen.
Um dies hinzuzufügen, klicken wir mit der rechten Maustaste auf das Projekt und wählen im Kontextmenü "Manage NuGet Packages":
Dann geben wir im erscheinenden Fenster zur Verwaltung von NuGet-Paketen im Suchfeld den Begriff "Entity Framework" ein, wählen das Paket Entity Framework aus und installieren es:
Anschließend müssen wir in der Datei App.config nach dem schließenden Element configSections den folgenden Block hinzufügen:
In der connectionString muss die Verbindungszeichenfolge eingetragen werden.
Jetzt erstellen wir in separaten Dateien 3 Schnittstellen:
- Implementierung der Schnittstelle IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementierung der Schnittstelle IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementierung der Schnittstelle IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Und in einer separaten Datei erstellen wir die Basisklasse BaseEntity für unsere beiden Entitäten, die gemeinsame Felder enthalten wird:
Implementierung der Basisklasse BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Dann erstellen wir in separaten Dateien unsere beiden Entitäten:
- Implementierung der Klasse Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementierung der Klasse Customer
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Customer")] public class Customer: BaseEntity { public int Ref_ID { get; set; } public int Ref_ID2 { get; set; } } }
Jetzt erstellen wir in einer separaten Datei den Kontext UserContext:
Implementierung der Klasse UserContext
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer(null);
}
public DbSet Customer { get; set; }
public DbSet Ref { get; set; }
}
}
Wir haben eine einsatzbereite Lösung für Tests zur Optimierung mit LINQ to SQL über EF für MS SQL Server erhalten:
Jetzt fügen wir in die Datei Program.cs den folgenden Code ein:
Datei Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
namespace TestLINQ
{
class Program
{
static void Main(string[] args)
{
using (UserContext db = new UserContext())
{
var dblog = new List();
db.Database.Log = dblog.Add;
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
var result = query.Take(1000).ToList();
Console.WriteLine(dblog[1]);
Console.ReadKey();
}
}
}
}
Fahren wir fort mit unserem Projekt.
Am Ende der Ausführung wird in der Konsole ausgegeben:
Der generierte SQL-Abfrage
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])
Das heißt, insgesamt hat die LINQ-Abfrage eine SQL-Abfrage an die MS SQL Server-Datenbank generiert, die durchaus ansprechend ist.
Jetzt ändern wir die Bedingung von UND auf ODER in der LINQ-Abfrage:
LINQ-Abfrage
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
|| (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
Und erneut starten wir unsere Anwendung.
Die Ausführung wird mit einem Fehler abgebrochen, der mit der Überschreitung der Ausführungszeit von 30 Sekunden zusammenhängt:
Wenn wir uns anschauen, welche Abfrage dabei von LINQ generiert wurde:
, kann man feststellen, dass die Auswahl über das kartesische Produkt von zwei Mengen (Tabellen) erfolgt:
Der generierte SQL-Abfrage
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
CROSS JOIN [dbo].[Ref] AS [Extent2]
WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]
Lassen Sie uns die LINQ-Abfrage wie folgt umschreiben:
Optimierte LINQ-Abfrage
var query = (from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID equals e2.ID
select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID2 equals e2.ID2
select new { Data1 = e1.Name, Data2 = e2.Name });
Dann erhalten wir die folgende SQL-Abfrage:
SQL-Abfrage
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3]
FROM ( SELECT DISTINCT TOP (1000)
[UnionAll1].[C1] AS [C1],
[UnionAll1].[Name] AS [C2],
[UnionAll1].[Name1] AS [C3]
FROM (SELECT
1 AS [C1],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
UNION ALL
SELECT
1 AS [C1],
[Extent3].[Name] AS [Name],
[Extent4].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent3]
INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
) AS [Limit1]
Leider kann in LINQ-Abfragen nur eine Bedingung für den Join angegeben werden, daher ist es möglich, eine äquivalente Abfrage durch zwei separate Abfragen für jede Bedingung zu erstellen und diese dann über Union zu kombinieren, um Duplikate der Zeilen zu entfernen.
Ja, die Abfragen werden im Allgemeinen nicht äquivalent sein, da vollständige Duplikate von Zeilen zurückgegeben werden können. In der Praxis sind vollständige Duplikate jedoch nicht erforderlich und man versucht, sie zu vermeiden.
Vergleichen wir jetzt die Ausführungspläne dieser beiden Abfragen:
- Für CROSS JOIN beträgt die durchschnittliche Ausführungszeit 195 Sekunden:
- Für INNER JOIN-UNION beträgt die durchschnittliche Ausführungszeit weniger als 24 Sekunden:
Wie aus den Ergebnissen ersichtlich, arbeitet die optimierte LINQ-Abfrage für zwei Tabellen mit Millionen von Einträgen um ein Vielfaches schneller als die nicht optimierte.
Für den Fall mit AND in den Bedingungen, sieht die LINQ-Abfrage wie folgt aus:
LINQ-Abfrage
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
Fast immer wird eine korrekte SQL-Abfrage generiert, die im Durchschnitt etwa 1 Sekunde benötigt:
Auch für Manipulationen mit LINQ to Objects anstelle einer Abfrage wie folgt:
LINQ-Abfrage (1. Variante)
var query = from e1 in seq1
from e2 in seq2
where (e1.Key1==e2.Key1)
&& (e1.Key2==e2.Key2)
select new { Data1 = e1.Data, Data2 = e2.Data };
kann eine Abfrage wie folgt verwendet werden:
LINQ-Abfrage (2. Variante)
var query = from e1 in seq1
join e2 in seq2
on new { e1.Key1, e1.Key2 } equals new { e2.Key1, e2.Key2 }
select new { Data1 = e1.Data, Data2 = e2.Data };
{REPOSITORY_ABSOLUTE_PATH}
Definition von zwei Arrays
Para[] seq1 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 4, Data = "999" } };
Para[] seq2 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 5, Data = "999" } };
, und der Typ Para wird wie folgt definiert:
Definition des Typs Para
class Para
{
public int Key1, Key2;
public string Data;
}
Damit haben wir einige Aspekte der Optimierung von LINQ-Abfragen für MS SQL Server betrachtet.
Leider vergessen selbst erfahrene und führende .NET-Entwickler oft, dass sie verstehen müssen, was die Anweisungen im Hintergrund bewirken, die sie verwenden. Andernfalls werden sie zu Konfiguratoren und können eine Zeitbombe für die Zukunft legen, sowohl beim Skalieren der Softwarelösung als auch bei geringfügigen Änderungen der äußeren Umgebungsbedingungen.
Es wurde auch eine kurze Übersicht durchgeführt und .
Die Quellcodes für den Test - das Projekt selbst, die Erstellung von Tabellen in der TEST-Datenbank sowie die Befüllung dieser Tabellen befinden sich .
In diesem Repository befinden sich auch im Ordner Plans die Pläne zur Durchführung von Abfragen mit den Bedingungen ODER.
Quelle: habr.com
