LINQ įvedė .NET kaip galingą naują duomenų apdorojimo kalbą. LINQ to SQL kaip jos dalis leidžia gana patogiai bendrauti su DBVS naudojant, pavyzdžiui, Entity Framework. Tačiau naudodami jį gana dažnai, kūrėjai pamiršta pažiūrėti, kokią SQL užklausą sugeneruos užklausos teikėjas, jūsų atveju Entity Framework.
Pažvelkime į du pagrindinius dalykus naudodami pavyzdį.
Norėdami tai padaryti, sukurkite bandomąją duomenų bazę SQL serveryje ir sukurkite joje dvi lenteles naudodami šią užklausą:
Lentelių kūrimas
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
Dabar užpildykime nuorodų lentelę paleisdami šį scenarijų:
Nuorodų lentelės pildymas
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
Panašiai užpildykime klientų lentelę naudodami šį scenarijų:
Klientų lentelės užpildymas
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
Taigi gavome dvi lenteles, iš kurių vienoje yra daugiau nei 1 milijonas duomenų eilučių, o kitoje – daugiau nei 10 milijonų duomenų eilučių.
Dabar „Visual Studio“ turite sukurti bandomąjį „Visual C# Console App“ (.NET Framework) projektą:
Tada turite pridėti biblioteką, kad objekto struktūra galėtų sąveikauti su duomenų baze.
Norėdami jį pridėti, dešiniuoju pelės mygtuku spustelėkite projektą ir kontekstiniame meniu pasirinkite Tvarkyti „NuGet“ paketus:
Tada pasirodžiusiame „NuGet“ paketų valdymo lange paieškos lange įveskite žodį „Entity Framework“ ir pasirinkite „Entity Framework“ paketą ir jį įdiekite:
Tada faile App.config, uždarę elementą configSections, turite pridėti šį bloką:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
ConnectString turite įvesti ryšio eilutę.
Dabar sukurkime 3 sąsajas atskiruose failuose:
- IBaseEntityID sąsajos diegimas
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - IBaseEntityName sąsajos diegimas
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - IBaseNameInsertUTCDate sąsajos įdiegimas
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ir atskirame faile mes sukursime bazinę klasę BaseEntity mūsų dviem objektams, į kurią bus įtraukti bendrieji laukai:
Bazinės klasės BaseEntity įgyvendinimas
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Tada mes sukursime du objektus atskiruose failuose:
- Ref klasės įgyvendinimas
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Kliento klasės įgyvendinimas
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; } } }
Dabar sukurkime UserContext kontekstą atskirame faile:
UserContex klasės įdiegimas
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; }
}
}
Gavome paruoštą sprendimą atlikti optimizavimo testus su LINQ į SQL per EF for MS SQL Server:
Dabar įveskite šį kodą į Program.cs failą:
Program.cs failą
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();
}
}
}
}
Toliau pradėkime savo projektą.
Pasibaigus darbui, pulte bus rodoma:
Sugeneruota SQL užklausa
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])
Tai yra, apskritai, LINQ užklausa gana gerai sugeneravo SQL užklausą MS SQL Server DBVS.
Dabar LINQ užklausoje pakeiskime sąlygą IR į OR:
LINQ užklausa
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 };
Ir vėl paleisime programą.
Vykdymas sugenda ir atsiranda klaida, nes komandos vykdymo laikas viršija 30 sekundžių:
Jei pažvelgsite į užklausą, kurią sugeneravo LINQ:
, tada galite įsitikinti, kad pasirinkimas vyksta naudojant Dekarto sandaugą iš dviejų rinkinių (lentelių):
Sugeneruota SQL užklausa
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]
Perrašykime LINQ užklausą taip:
Optimizuota LINQ užklausa
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 });
Tada gauname tokią SQL užklausą:
SQL užklausa
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]
Deja, LINQ užklausose gali būti tik viena sujungimo sąlyga, todėl čia galima atlikti lygiavertę užklausą, naudojant dvi užklausas kiekvienai sąlygai ir sujungiant jas per Union, kad būtų pašalintos dublikatai tarp eilučių.
Taip, užklausos paprastai bus nelygiavertės, atsižvelgiant į tai, kad gali būti pateiktos visos pasikartojančios eilutės. Tačiau realiame gyvenime visiškai pasikartojančių eilučių nereikia ir žmonės stengiasi jų atsikratyti.
Dabar palyginkime šių dviejų užklausų vykdymo planus:
- CROSS JOIN vidutinis vykdymo laikas yra 195 sekundės:
- INNER JOIN-UNION vidutinis vykdymo laikas yra trumpesnis nei 24 sekundės:
Kaip matote iš rezultatų, dviejose lentelėse su milijonais įrašų optimizuota LINQ užklausa yra daug kartų greitesnė nei neoptimizuota.
Jei sąlygose yra parinktis su AND, formos LINQ užklausa:
LINQ užklausa
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 };
Beveik visada bus sugeneruota teisinga SQL užklausa, kuri vidutiniškai paleidžiama maždaug per 1 sekundę:
Taip pat LINQ to Objects manipuliavimui vietoj užklausos, pvz.:
LINQ užklausa (1-oji parinktis)
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 };
galite naudoti tokią užklausą:
LINQ užklausa (2-oji parinktis)
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 };
, jeigu:
Dviejų masyvų apibrėžimas
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" } };
, o Para tipas apibrėžiamas taip:
Para tipo apibrėžimas
class Para
{
public int Key1, Key2;
public string Data;
}
Taigi, mes išnagrinėjome kai kuriuos aspektus optimizuodami LINQ užklausas į MS SQL Server.
Deja, net patyrę ir pirmaujantys .NET kūrėjai pamiršta, kad jie turi suprasti, ką jų naudojamos instrukcijos daro užkulisiuose. Priešingu atveju jie tampa konfigūratoriais ir ateityje gali pasodinti uždelsto veikimo bombą tiek keičiant programinio sprendimo mastelį, tiek su nedideliais išorinės aplinkos sąlygų pasikeitimais.
Taip pat buvo atlikta trumpa apžvalga .
Yra testo šaltiniai - pats projektas, lentelių kūrimas TEST duomenų bazėje, taip pat šių lentelių užpildymas duomenimis .
Taip pat šioje saugykloje, aplanke Planai, yra užklausų su ARBA sąlygomis vykdymo planai.
Šaltinis: www.habr.com