LINQ ist als leistungsstarke neue Datenbearbeitungssprache in .NET eingestiegen. Mit LINQ to SQL können Sie als Teil davon ganz komfortabel mit dem DBMS kommunizieren, beispielsweise über das Entity Framework. Bei der häufigen Verwendung vergessen Entwickler jedoch, darauf zu achten, welche Art von SQL-Abfrage der abfragbare Anbieter generiert, in Ihrem Fall das Entity Framework.
Schauen wir uns zwei Hauptpunkte anhand eines Beispiels an.
Dazu erstellen wir in SQL Server eine Datenbank Test und erstellen darin zwei Tabellen mit der folgenden Abfrage:
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
Füllen wir nun die Ref-Tabelle, indem wir das folgende Skript ausführen:
Ausfüllen 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
Füllen wir die Tabelle „Customer“ auf die gleiche Weise mit dem folgenden Skript auf:
Auffüllen der Kundentabelle
USE [TEST]
GO
DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;
WHILE(@ind<=12000000)
BEGIN
IF(@ind%3=0) SET @ind_ref=1;
ELSE IF (@ind%5=0) SET @ind_ref=2;
ELSE IF (@ind%7=0) SET @ind_ref=3;
ELSE IF (@ind%11=0) SET @ind_ref=4;
ELSE IF (@ind%13=0) SET @ind_ref=5;
ELSE IF (@ind%17=0) SET @ind_ref=6;
ELSE IF (@ind%19=0) SET @ind_ref=7;
ELSE IF (@ind%23=0) SET @ind_ref=8;
ELSE IF (@ind%29=0) SET @ind_ref=9;
ELSE IF (@ind%31=0) SET @ind_ref=10;
ELSE IF (@ind%37=0) SET @ind_ref=11;
ELSE SET @ind_ref=@ind%1190000;
INSERT INTO [dbo].[Customer]
([ID]
,[Name]
,[Ref_ID]
,[Ref_ID2])
SELECT
@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 mehr als 1 Million Datenzeilen und die andere mehr als 10 Millionen Datenzeilen enthält.
Jetzt müssen Sie in Visual Studio ein Testprojekt für die Visual C# Console App (.NET Framework) erstellen:
Als Nächstes müssen Sie eine Bibliothek hinzufügen, damit das Entity Framework mit der Datenbank interagieren kann.
Um es hinzuzufügen, klicken Sie mit der rechten Maustaste auf das Projekt und wählen Sie im Kontextmenü die Option „NuGet-Pakete verwalten“ aus:
Geben Sie dann im angezeigten NuGet-Paketverwaltungsfenster im Suchfeld das Wort „Entity Framework“ ein, wählen Sie das Entity Framework-Paket aus und installieren Sie es:
Fügen Sie als Nächstes in der Datei App.config nach dem Schließen des configSections-Elements den folgenden Block hinzu:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
In „connectionString“ müssen Sie die Verbindungszeichenfolge eingeben.
Lassen Sie uns nun drei Schnittstellen in separaten Dateien erstellen:
- Implementierung der IBaseEntityID-Schnittstelle
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementierung der IBaseEntityName-Schnittstelle
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementierung der IBaseNameInsertUTCDate-Schnittstelle
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Und in einer separaten Datei erstellen wir eine BaseEntity-Basisklasse für unsere beiden Entitäten, die gemeinsame Felder enthält:
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; }
}
}
Als nächstes erstellen wir in separaten Dateien unsere beiden Entitäten:
- Implementierung der Ref-Klasse
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementierung der Customer-Klasse
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 einen UserContext-Kontext in einer separaten Datei:
Implementierung der UserContex-Klasse
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer<UserContext>(null);
}
public DbSet<Customer> Customer { get; set; }
public DbSet<Ref> Ref { get; set; }
}
}
Wir haben eine fertige Lösung zur Durchführung von Optimierungstests mit LINQ to SQL über EF für MS SQL Server erhalten:
Geben Sie nun in der Datei Program.cs den folgenden Code ein:
Program.cs-Datei
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<string>();
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();
}
}
}
}
Lassen Sie uns als Nächstes unser Projekt ausführen.
Am Ende der Arbeit wird auf der Konsole Folgendes angezeigt:
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, im Allgemeinen generierte die LINQ-Abfrage recht gut eine SQL-Abfrage an das MS SQL Server-DBMS.
Jetzt ändern wir die AND-Bedingung in der LINQ-Abfrage in OR:
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 };
Lassen Sie uns unsere Anwendung erneut ausführen.
Die Ausführung stürzt mit einem Fehler ab, der darauf zurückzuführen ist, dass die Befehlsausführungszeit 30 Sekunden überschreitet:
Wenn Sie sich ansehen, welche Abfrage von LINQ generiert wurde:
, dann können Sie sicherstellen, dass die Auswahl durch das kartesische Produkt zweier Mengen (Tabellen) erfolgt:
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]
Schreiben wir die LINQ-Abfrage wie folgt um:
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 es in LINQ-Abfragen nur eine Join-Bedingung geben. Daher ist es möglich, eine äquivalente Abfrage über zwei Abfragen für jede Bedingung durchzuführen und sie anschließend über Union zu vereinen, um Duplikate zwischen Zeilen zu entfernen.
Ja, die Abfragen sind im Allgemeinen nicht äquivalent, da möglicherweise vollständige doppelte Zeilen zurückgegeben werden. Im wirklichen Leben sind jedoch keine vollständigen doppelten Zeilen erforderlich, und sie versuchen, sie loszuwerden.
Vergleichen wir nun 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 hervorgeht, ist die optimierte LINQ-Abfrage für zwei Tabellen mit Millionen von Datensätzen um ein Vielfaches schneller als die nicht optimierte.
Für die Variante mit AND in den Bedingungen einer LINQ-Abfrage der Form:
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 durchschnittlich etwa 1 Sekunde dauert:
Auch für LINQ to Objects-Manipulationen anstelle der Abfrage der Ansicht:
LINQ-Abfrage (1. Option)
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 };
Sie können eine Abfrage wie diese verwenden:
LINQ-Abfrage (2. Option)
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 };
wo:
Definieren 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 Para-Typ ist wie folgt definiert:
Para-Typdefinition
class Para
{
public int Key1, Key2;
public string Data;
}
Daher haben wir einige Aspekte bei der Optimierung von LINQ-Abfragen an MS SQL Server berücksichtigt.
Leider vergessen selbst erfahrene und führende .NET-Entwickler, dass es notwendig ist, zu verstehen, was die von ihnen verwendeten Anweisungen hinter den Kulissen bewirken. Andernfalls werden sie zu Konfiguratoren und können sowohl bei der Skalierung einer Softwarelösung als auch bei geringfügigen Änderungen der äußeren Umgebungsbedingungen in der Zukunft eine Zeitbombe legen.
Es gab auch eine kleine Rezension .
Quellen für den Test sind das Projekt selbst, das Erstellen von Tabellen in der TEST-Datenbank sowie das Füllen dieser Tabellen mit Daten .
Außerdem befinden sich in diesem Repository im Ordner „Pläne“ Pläne zum Ausführen von Abfragen mit ODER-Bedingungen.
Source: habr.com