LINQ gik ind i .NET som et kraftfuldt nyt datamanipulationssprog. LINQ til SQL som en del af det giver dig mulighed for at kommunikere med DBMS ganske bekvemt ved hjælp af for eksempel Entity Framework. Men ved at bruge det ret ofte, glemmer udviklere at se på, hvilken slags SQL-forespørgsel den forespørgbare udbyder vil generere, i dit tilfælde, Entity Framework.
Lad os se på to hovedpunkter med et eksempel.
For at gøre dette opretter vi en databasetest i SQL Server, og i den opretter vi to tabeller ved hjælp af følgende forespørgsel:
Oprettelse af tabeller
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
Lad os nu udfylde Ref-tabellen ved at køre følgende script:
Påfyldning af bordet 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
Lad os udfylde kundetabellen på samme måde ved hjælp af følgende script:
Udfyldning af kundetabellen
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
Således fik vi to tabeller, hvoraf den ene har mere end 1 million rækker data, og den anden har mere end 10 millioner rækker data.
Nu i Visual Studio skal du oprette et testprojekt for Visual C# Console App (.NET Framework):
Dernæst skal du tilføje et bibliotek, så Entity Framework kan interagere med databasen.
For at tilføje det skal du højreklikke på projektet og vælge Administrer NuGet-pakker fra kontekstmenuen:
Indtast derefter ordet "Entity Framework" i NuGet-pakkehåndteringsvinduet, der vises, i søgefeltet, vælg Entity Framework-pakken og installer den:
Dernæst i App.config-filen, efter at have lukket configSections-elementet, skal du tilføje følgende blok:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
I connectionString skal du indtaste forbindelsesstrengen.
Lad os nu oprette 3 grænseflader i separate filer:
- Implementering af IBaseEntityID-grænsefladen
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementering af IBaseEntityName-grænsefladen
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementering af IBaseNameInsertUTCDate-grænsefladen
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Og i en separat fil vil vi oprette en BaseEntity-basisklasse for vores to entiteter, som vil omfatte fælles felter:
Implementering af basisklassen BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Dernæst, i separate filer, vil vi oprette vores to enheder:
- Ref klasse implementering
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementering af kundeklassen
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; } } }
Lad os nu oprette en UserContext-kontekst i en separat fil:
Implementering af UserContex-klassen
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; }
}
}
Vi har fået en færdig løsning til at udføre optimeringstest med LINQ til SQL via EF til MS SQL Server:
Indtast nu følgende kode i filen Program.cs:
Program.cs fil
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();
}
}
}
}
Lad os derefter køre vores projekt.
I slutningen af arbejdet vil følgende blive vist på konsollen:
Genereret SQL-forespørgsel
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])
Det vil sige, at LINQ-forespørgslen generelt genererede en SQL-forespørgsel til MS SQL Server DBMS ganske godt.
Lad os nu ændre AND-betingelsen til OR i LINQ-forespørgslen:
LINQ forespørgsel
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 };
Lad os køre vores applikation igen.
Udførelsen vil gå ned med en fejl relateret til kommandoudførelsestiden, der overstiger 30 sekunder:
Hvis du ser på, hvilken forespørgsel der blev genereret af LINQ:
, så kan du sikre dig, at valget sker gennem det kartesiske produkt af to sæt (tabeller):
Genereret SQL-forespørgsel
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]
Lad os omskrive LINQ-forespørgslen sådan her:
Optimeret LINQ-forespørgsel
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 });
Så får vi følgende SQL-forespørgsel:
SQL-forespørgsel
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]
Desværre, i LINQ-forespørgsler kan der kun være én join-betingelse, derfor er det muligt at lave en tilsvarende forespørgsel gennem to forespørgsler for hver betingelse, efterfulgt af deres forening gennem Union for at fjerne dubletter blandt rækker.
Ja, forespørgslerne vil generelt være ikke-ækvivalente, da komplette duplikerede rækker kan returneres. Men i det virkelige liv er fulde duplikerede linjer ikke nødvendige, og de forsøger at slippe af med dem.
Lad os nu sammenligne udførelsesplanerne for disse to forespørgsler:
- for CROSS JOIN er den gennemsnitlige udførelsestid 195 sek.
- for INNER JOIN-UNION er den gennemsnitlige udførelsestid mindre end 24 sek.
Som du kan se fra resultaterne, er den optimerede LINQ-forespørgsel for to tabeller med millioner af poster mange gange hurtigere end den uoptimerede.
For varianten med OG i betingelserne for en LINQ-forespørgsel af formularen:
LINQ forespørgsel
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 };
den korrekte SQL-forespørgsel vil næsten altid blive genereret, som vil blive udført i gennemsnit i ca. 1 sekund:
Også for LINQ to Objects-manipulationer i stedet for at forespørge i visningen:
LINQ-forespørgsel (første mulighed)
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 };
Du kan bruge en forespørgsel som:
LINQ-forespørgsel (første mulighed)
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 };
hvor:
Definition af to 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" } };
, og Para-typen er defineret som følger:
Para type definition
class Para
{
public int Key1, Key2;
public string Data;
}
Derfor har vi overvejet nogle aspekter i optimering af LINQ-forespørgsler til MS SQL Server.
Desværre glemmer selv erfarne og førende .NET-udviklere, at det er nødvendigt at forstå, hvad de instruktioner, de bruger, gør bag kulisserne. Ellers bliver de konfiguratorer og kan lægge en tidsindstillet bombe i fremtiden, både ved skalering af en softwareløsning, og ved mindre ændringer i eksterne miljøforhold.
Der var også en lille anmeldelse .
Kilder til testen - selve projektet, oprettelse af tabeller i TEST-databasen, samt udfyldning af disse tabeller med data er placeret .
Også i dette lager i mappen Planer er planer for udførelse af forespørgsler med OR-betingelser.
Kilde: www.habr.com