LINQ je vstopil v .NET kot zmogljiv nov jezik za obdelavo podatkov. LINQ to SQL kot njegov del vam omogoča zelo priročno komunikacijo z DBMS z uporabo, na primer, Entity Framework. Vendar pa zaradi pogoste uporabe razvijalci pozabijo pogledati, kakšno poizvedbo SQL bo ustvaril ponudnik, ki ga je mogoče poizvedovati, v vašem primeru Entity Framework.
Oglejmo si dve glavni točki na primeru.
Če želite to narediti, ustvarite preizkusno bazo podatkov v strežniku SQL Server in v njej ustvarite dve tabeli z naslednjo poizvedbo:
Izdelava tabel
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
Zdaj pa napolnimo tabelo Ref z zagonom naslednjega skripta:
Polnjenje tabele 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
Podobno izpolnimo tabelo Stranka z naslednjim skriptom:
Polnjenje tabele Stranka
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
Tako smo dobili dve tabeli, od katerih ima ena več kot 1 milijon vrstic podatkov, druga pa več kot 10 milijonov vrstic podatkov.
Zdaj morate v Visual Studio ustvariti testni projekt Visual C# Console App (.NET Framework):
Nato morate dodati knjižnico za Entity Framework za interakcijo z bazo podatkov.
Če ga želite dodati, z desno miškino tipko kliknite projekt in v kontekstnem meniju izberite Upravljanje paketov NuGet:
Nato v oknu za upravljanje paketov NuGet, ki se prikaže, v iskalno okno vnesite besedo »Entity Framework« in izberite paket Entity Framework ter ga namestite:
Nato morate v datoteki App.config po zaprtju elementa configSections dodati naslednji blok:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
V ConnectionString morate vnesti povezovalni niz.
Zdaj pa ustvarimo 3 vmesnike v ločenih datotekah:
- Implementacija vmesnika IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementacija vmesnika IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Izvedba vmesnika IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
In v ločeni datoteki bomo ustvarili osnovni razred BaseEntity za naši dve entiteti, ki bo vključeval skupna polja:
Implementacija osnovnega razreda BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Nato bomo ustvarili naši dve entiteti v ločenih datotekah:
- Implementacija razreda Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementacija razreda 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; } } }
Zdaj pa ustvarimo kontekst UserContext v ločeni datoteki:
Implementacija razreda UserContex
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; }
}
}
Prejeli smo že pripravljeno rešitev za izvedbo optimizacijskih testov z LINQ to SQL preko EF za MS SQL Server:
Zdaj vnesite naslednjo kodo v datoteko Program.cs:
Datoteka 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<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();
}
}
}
}
Nato zaženimo naš projekt.
Na koncu dela bo na konzoli prikazano naslednje:
Ustvarjena poizvedba SQL
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])
To pomeni, da je na splošno poizvedba LINQ precej dobro ustvarila poizvedbo SQL za DBMS strežnika MS SQL Server.
Zdaj pa spremenimo pogoj IN v ALI v poizvedbi LINQ:
poizvedba LINQ
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 };
In znova zaženimo našo aplikacijo.
Izvedba se bo zrušila z napako, ker čas izvajanja ukaza presega 30 sekund:
Če pogledate poizvedbo, ki jo je ustvaril LINQ:
, potem se lahko prepričate, da izbira poteka prek kartezičnega produkta dveh nizov (tabel):
Ustvarjena poizvedba SQL
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]
Prepišimo poizvedbo LINQ na naslednji način:
Optimizirana poizvedba LINQ
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 });
Nato dobimo naslednjo poizvedbo SQL:
SQL poizvedba
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]
Žal, v poizvedbah LINQ je lahko samo en pogoj združevanja, zato je tukaj mogoče ustvariti enakovredno poizvedbo z uporabo dveh poizvedb za vsak pogoj in ju nato združiti prek Unije, da odstrani dvojnike med vrsticami.
Da, poizvedbe bodo na splošno neenakovredne, ob upoštevanju, da se lahko vrnejo celotne podvojene vrstice. Vendar pa v resničnem življenju popolni dvojniki vrstic niso potrebni in ljudje se jih poskušajo znebiti.
Zdaj pa primerjajmo izvedbene načrte teh dveh poizvedb:
- za CROSS JOIN je povprečni čas izvedbe 195 sekund:
- za INNER JOIN-UNION je povprečni čas izvedbe krajši od 24 sekund:
Kot lahko vidite iz rezultatov, je za dve tabeli z milijoni zapisov optimizirana poizvedba LINQ mnogokrat hitrejša od neoptimizirane.
Za možnost z IN v pogojih poizvedba LINQ v obliki:
poizvedba LINQ
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 };
Skoraj vedno bo ustvarjena pravilna poizvedba SQL, ki se bo v povprečju izvedla v približno 1 sekundi:
Tudi za manipulacije LINQ to Objects namesto poizvedbe, kot je:
Poizvedba LINQ (1. možnost)
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 };
lahko uporabite poizvedbo, kot je:
Poizvedba LINQ (2. možnost)
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 };
kjer je:
Definiranje dveh nizov
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" } };
, tip Para pa je definiran na naslednji način:
Definicija tipa para
class Para
{
public int Key1, Key2;
public string Data;
}
Tako smo preučili nekatere vidike optimizacije poizvedb LINQ do strežnika MS SQL.
Na žalost celo izkušeni in vodilni razvijalci .NET pozabljajo, da morajo razumeti, kaj navodila, ki jih uporabljajo, počnejo v zakulisju. V nasprotnem primeru postanejo konfiguratorji in lahko v prihodnosti podstavijo časovno bombo tako pri skaliranju programske rešitve kot ob manjših spremembah zunanjih okoljskih pogojev.
Opravljen je bil tudi kratek pregled .
Viri za test - sam projekt, ustvarjanje tabel v bazi podatkov TEST in polnjenje teh tabel s podatki se nahajajo .
Tudi v tem repozitoriju, v mapi Načrti, so načrti za izvajanje poizvedb s pogoji ALI.
Vir: www.habr.com