LINQ sisestas .NET-i võimsa uue andmetöötluskeelena. LINQ to SQL selle osana võimaldab DBMS-iga üsna mugavalt suhelda, kasutades näiteks Entity Frameworki. Seda üsna sageli kasutades unustavad arendajad aga vaadata, millise SQL-päringu päringupakkuja, teie puhul Entity Framework, genereerib.
Vaatleme näite varal kahte põhipunkti.
Selleks looge SQL Serveris testandmebaas ja looge selles kaks tabelit järgmise päringu abil:
Tabelite koostamine
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
Nüüd täidame tabeli Ref, käivitades järgmise skripti:
Ref tabeli täitmine
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
Täidame sarnaselt kliendi tabeli, kasutades järgmist skripti:
Klientide tabeli täitmine
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
Nii saime kaks tabelit, millest ühes on üle 1 miljoni andmerida ja teises üle 10 miljoni andmerida.
Nüüd peate Visual Studios looma Visual C# konsoolirakenduse (.NET Framework) testprojekti:
Järgmiseks peate andmebaasiga suhtlemiseks lisama üksuse raamistiku raamatukogu.
Selle lisamiseks paremklõpsake projektil ja valige kontekstimenüüst Manage NuGet Packages:
Seejärel sisestage ilmuvas NuGeti paketihaldusaknas otsinguaknasse sõna "Entity Framework" ja valige pakett Entity Framework ja installige see:
Järgmisena peate pärast elemendi configSections sulgemist faili App.config lisama järgmise ploki:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
In connectionString peate sisestama ühenduse stringi.
Nüüd loome 3 liidest eraldi failides:
- IBaseEntityID liidese juurutamine
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Liidese IBaseEntityName juurutamine
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Liidese IBaseNameInsertUTCDate juurutamine
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ja eraldi failis loome meie kahe olemi jaoks baasklassi BaseEntity, mis sisaldab ühiseid välju:
Alusklassi BaseEntity juurutamine
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Järgmisena loome oma kaks olemit eraldi failides:
- Ref klassi rakendamine
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Kliendiklassi juurutamine
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; } } }
Nüüd loome UserContexti konteksti eraldi failis:
UserContexi klassi juurutamine
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; }
}
}
Saime valmis lahenduse optimeerimistestide läbiviimiseks LINQ to SQL-iga EF-i kaudu MS SQL Serverile:
Nüüd sisestage faili Program.cs järgmine kood:
Program.cs faili
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();
}
}
}
}
Järgmisena käivitame oma projekti.
Töö lõpus kuvatakse konsoolil:
Loodud SQL päring
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])
See tähendab, et üldiselt genereeris LINQ-päring MS SQL Serveri DBMS-i SQL-päringu üsna hästi.
Nüüd muudame LINQ-päringus tingimuse JA väärtuseks OR:
LINQ päring
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 };
Ja käivitame oma rakenduse uuesti.
Täitmine jookseb kokku veaga, kuna käsu täitmisaeg ületab 30 sekundit:
Kui vaatate päringut, mille LINQ genereeris:
, siis saate veenduda, et valik toimub kahe hulga (tabelite) Descartes'i korrutise kaudu:
Loodud SQL päring
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]
Kirjutame LINQ-päringu ümber järgmiselt:
Optimeeritud LINQ-päring
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 });
Seejärel saame järgmise SQL-päringu:
SQL päring
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]
Paraku saab LINQ-päringutes olla ainult üks liitumise tingimus, nii et siin on võimalik teha samaväärne päring, kasutades iga tingimuse jaoks kahte päringut ja kombineerides need seejärel Unioni kaudu, et eemaldada ridade hulgast duplikaadid.
Jah, päringud ei ole üldjuhul samaväärsed, võttes arvesse, et võidakse tagastada täielikud topeltread. Päriselus pole aga täielikke dubleerivaid ridu vaja ja inimesed püüavad neist lahti saada.
Võrdleme nüüd nende kahe päringu täitmisplaane:
- CROSS JOINi keskmine täitmisaeg on 195 sekundit:
- INNER JOIN-UNIONi puhul on keskmine täitmisaeg alla 24 sekundi:
Nagu tulemustest näha, on kahe miljonite kirjetega tabeli puhul optimeeritud LINQ-päring kordades kiirem kui optimeerimata päring.
Tingimustes AND-ga valiku puhul LINQ-päring järgmisel kujul:
LINQ päring
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 };
Peaaegu alati genereeritakse õige SQL-päring, mis käivitatakse keskmiselt umbes 1 sekundiga:
Samuti LINQ to Objects manipulatsioonide jaoks päringu asemel nagu:
LINQ-päring (1. valik)
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 };
võite kasutada päringut nagu:
LINQ-päring (2. valik)
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 };
kus:
Kahe massiivi defineerimine
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" } };
, ja Para tüüp on määratletud järgmiselt:
Para tüübi määratlus
class Para
{
public int Key1, Key2;
public string Data;
}
Seega uurisime mõningaid aspekte LINQ-päringute optimeerimisel MS SQL Serverile.
Kahjuks unustavad isegi kogenud ja juhtivad .NET-i arendajad, et nad peavad mõistma, mida nende kasutatavad juhised kulisside taga teevad. Vastasel juhul saavad neist konfiguraatorid ja võivad tulevikus viitsütikuga pommi istutada nii tarkvaralahenduse skaleerimisel kui ka väiksemate muudatustega välistes keskkonnatingimustes.
Samuti tehti lühike ülevaade .
Testi allikad - projekt ise, tabelite loomine andmebaasis TEST, samuti nende tabelite andmetega täitmine asuvad .
Ka selles hoidlas kaustas Plaanid on plaanid VÕI-tingimustega päringute täitmiseks.
Allikas: www.habr.com