Introducere
În au fost discutate unele metode de optimizare interogărilor LINQ.
În continuare, vom prezenta și alte abordări pentru optimizarea codului, legate de interogările LINQ.
Este cunoscut faptul că LINQ(Language-Integrated Query) — este un limbaj simplu și convenabil pentru interogarea surselor de date.
Un LINQ to SQL este o tehnologie de acces la date în SGBD. Este un instrument puternic pentru manipularea datelor, unde printr-un limbaj declarativ se construiesc interogări, care apoi sunt transformate în . Le este mult mai aproape să opereze în termeni de OOP. Am redactat instrucțiuni despre cum să lucrăm cu obiectele bazei de date, s-a format interogarea SQL și a fost executată. Noua versiune a bazei de date este gata, a fost testată — totul este bine, totul funcționează. platformă și trimise către serverul de baze de date pentru execuție. În cazul nostru, SGBD înseamnă MS SQL Server.
Totuși, interogările LINQ nu sunt transformate în interogări optimizate . Le este mult mai aproape să opereze în termeni de OOP. Am redactat instrucțiuni despre cum să lucrăm cu obiectele bazei de date, s-a format interogarea SQL și a fost executată. Noua versiune a bazei de date este gata, a fost testată — totul este bine, totul funcționează., pe care un DBA experimentat le-ar scrie având în vedere toate nuanțele optimizării interogărilor SQL:
- îmbinări optime (JOIN) și filtrarea rezultatelor (WHERE)
- o mulțime de nuanțe în utilizarea îmbinărilor și a condițiilor de grup
- o mulțime de variații în înlocuirea condițiilor IN pe [START WITH …] CONNECT BYși NOT IN, la [START WITH …] CONNECT BY
- cache-ul intermediar al rezultatelor prin tabele temporare, CTE, variabile de tip tabel
- utilizarea clauzei (OPTION) cu instrucțiuni și sugestii de indexare WITH (…)
- utilizarea vederilor indexate, ca una dintre metodele de a elimina citirile excesive de date la selecții
Principalele puncte slabe de performanță rezultate interogărilor SQL în timpul compilării interogărilor LINQ sunt:
- consolidarea întregului mecanism de selecție a datelor într-o singură interogare
- duplicarea blocurilor identice de cod, ceea ce duce în cele din urmă la citiri multiple inutile de date
- grupuri de condiții compuse (logice „și” și „sau”) — ȘI și OR, combinându-se în condiții complexe, duce la faptul că optimizatorul, având indecși neclusterizați adecvați, pe câmpurile necesare, în cele din urmă totuși începe să facă un scanare pe indexul clusterizat (INDEX SCAN) pe grupurile de condiții
- o înnădescere profundă a subinterogărilor face foarte problematică analiza instrucțiunilor SQL și analiza planului de interogare din partea dezvoltatorilor și DBA
Metode de optimizare
Acum să trecem direct la metodele de optimizare.
1) Indexare suplimentară
Cel mai bine este să examinăm filtrele pe tabelele principale de selecție, deoarece foarte des întreaga interogare se construiește în jurul una sau două tabele principale (cereri-persoane-operațiuni) și cu un set standard de condiții (IsClosed, Canceled, Enabled, Status). Este important să se creeze indecși corespunzători pentru selecțiile identificate.
Această soluție are sens atunci când selecția pe aceste câmpuri limitează semnificativ mulțimea de rezultate returnate de interogare.
De exemplu, avem 500000 de cereri. Totuși, cererile active sunt doar 2000 de înregistrări. Atunci, un index bine ales ne va scuti de INDEX SCAN o interogare pe o masă mare și ne va permite să selectăm rapid datele printr-un index neclusterizat.
De asemenea, lipsa indexurilor poate fi identificată prin sugestiile de analiză a planurilor de interogare sau colectarea statisticilor din vizualizările sistemului MS SQL Server:
Toate datele din vizualizări conțin informații despre indexurile lipsă, cu excepția indexurilor spațiale.
Cu toate acestea, indexurile și cachingul sunt adesea metode de combatere a consecințelor unor interogări prost scrise. interogărilor LINQ și interogărilor SQL.
Așa cum arată practica dură a vieții de afaceri, de multe ori implementarea funcțiilor de afaceri la date limită este esențială. Din această cauză, interogările complexe sunt adesea mutate în fundal cu caching.
Parțial, acest lucru este justificat, deoarece utilizatorului nu îi sunt întotdeauna necesare cele mai recente date și se obține un nivel acceptabil de răspuns al interfeței utilizator.
Această abordare permite rezolvarea cerințelor de afaceri, dar în final reduce eficiența sistemului informațional, amânând pur și simplu soluțiile problemelor.
De asemenea, este important să ne amintim că în procesul de căutare a indexurilor noi de adăugat, sugestiile Graf*, document de optimizare pot fi incorecte și în condițiile următoare:
- dacă există deja indexuri cu un set similar de câmpuri
- dacă câmpurile din tabel nu pot fi indexate din cauza restricțiilor de indexare (despre acest lucru este descris în detaliu ).
2) Combinarea atributelor într-un nou atribut
Uneori, unele câmpuri dintr-un tabel, care sunt utilizate în grupuri de condiții, pot fi înlocuite prin introducerea unui singur câmp nou.
Acest lucru este deosebit de relevant pentru câmpurile de stare, care de obicei sunt fie de tip bit, fie întregi.
Exemplu:
IsClosed = 0 ȘI Canceled = 0 ȘI Enabled = 0 se înlocuiește cu Status = 1.
Aici se introduce un atribut întreg Status, asigurat prin completarea acestor stări în tabel. Ulterior, se procedează la indexarea acestui nou atribut.
Aceasta este o soluție fundamentală pentru problema performanței, deoarece obținem datele fără calculuri suplimentare.
3) Materializarea vizualizării
Din păcate, în Interogări LINQ nu se pot folosi direct tabele temporare, CTE și variabile de tip tabel.
Totuși, mai există încă o modalitate de optimizare în acest caz - vizualizările indexate.
Grupul de condiții (din exemplul de mai sus) IsClosed = 0 ȘI Canceled = 0 ȘI Enabled = 0 (sau un set de alte condiții similare) devine o opțiune bună pentru a fi utilizat într-o vizualizare indexată, cache-uind un mic subset de date dintr-un număr mare.
Dar există anumite restricții în materie de materializare a vizualizării:
- utilizarea subinterogărilor, propunerilor [START WITH …] CONNECT BY trebuie înlocuită cu utilizarea JOIN
- nu se pot folosi propuneri UNION, UNION ALL, EXCEPȚIE, Variabile de tabel
- nu se pot folosi sugestii pentru tabel și propuneri OPTION
- nu există posibilitatea de a lucra cu bucle
- nu este posibil să se extragă date dintr-o singură vizualizare din tabele diferite
Este important să ne amintim că beneficiul real al utilizării unei vizualizări indexate poate fi obținut de fapt doar prin indexarea acesteia.
Dar la apelarea vizualizării, aceste indecși nu pot fi utilizați, iar pentru a-i folosi explicit trebuie să specifici WITH (NOEXPAND).
Deoarece în Interogări LINQ nu se pot defini sugestii pentru tabel, așa că trebuie să facem o altă vizualizare - un "wrapper" de următoarea formă:
CREATE VIEW NUME_VIZUALIZARE AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);
4) Utilizarea funcțiilor de tabel
Adesea în Interogări LINQ blocuri mari de subinterogări sau blocuri care folosesc vizualizări cu o structură complexă formează o interogare finală cu o structură foarte complicată și neoptimă de execuție.
Principalele avantaje ale utilizării funcțiilor de tabel în Interogări LINQ:
- Posibilitatea, la fel ca în cazul vizualizărilor, de a folosi și specifica ca obiect, dar se pot transmite un set de parametri de intrare:
FROM FUNCTION(@param1, @param2 …)
în final se poate obține o selecție flexibilă de date - În cazul utilizării funcției de tabel, nu există restricții atât de stricte, ca în cazul vizualizărilor indexate descrise mai sus:
- Sugestiile pentru tabel:
prin LINQ nu se pot specifica ce indecși trebuie utilizați și se poate determina nivelul de izolare a datelor în timpul interogării.
Dar în funcție aceste posibilități există.
Cu funcția se poate obține un plan de execuție a interogării destul de constant, unde sunt stabilite regulile de lucru cu indecșii și nivelurile de izolare a datelor - Utilizarea funcției permite, comparativ cu vizualizările indexate, să obțină:
- logica complexă de selecție a datelor (inclusiv utilizarea ciclurilor)
- selecția datelor din mai multe tabele diferite
- utilizarea UNION și [START WITH …] CONNECT BY
- Sugestiile pentru tabel:
- Ofertă OPTION foarte utilă atunci când trebuie să asigurăm gestionarea concurenței OPTION(MAXDOP N), ordinea planului de execuție a interogării. De exemplu:
- se poate specifica recrearea forțată a planului de interogare OPTION (RECOMPILE)
- se poate specifica necesitatea de a asigura utilizarea forțată de către planul de interogare a ordinii de conectare specificate în interogare OPTION (FORCE ORDER)
Mai detaliat despre OPTION este descris .
- Utilizarea celui mai îngust și necesar subset de date:
Nu este nevoie să păstrăm seturi mari de date în cache-uri (ca în cazul cu vitrine indexate), din care trebuie să filtrăm datele pe parametru.
De exemplu, există un tabel, care are pentru filtrare WHERE trei câmpuri (a, b, c).Condițional, pentru toate interogările există o condiție constantă a = 0 și b = 0.
Cu toate acestea, interogarea pe câmpul c este mai variabilă.
Să presupunem că condiția a = 0 și b = 0 ne ajută într-adevăr să restricționăm setul de date rezultate la câteva mii de înregistrări, dar condiția pe de ne restrânge selecția la o sută de înregistrări.
Aici, funcția tabelară poate fi o opțiune mai avantajoasă.
De asemenea, funcția tabelară este mai predictibilă și constantă în timp de execuție.
Exemple
Să luăm în considerare un exemplu de implementare pe baza de date Questions.
Există o interogare SELECT, care combină mai multe tabele și utilizează o vitrină (OperativeQuestions), în care se verifică prin email apartenența (prin [START WITH …] CONNECT BY) la „Întrebările active”([OperativeQuestions]):
Interogarea nr. 1
(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4]
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL)
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));
Vitrina are o structură destul de complexă: conține conexiuni de subinterogări și utilizarea sortării DISTINCT, care în general este o operațiune destul de consumatoare de resurse.
Selectarea din OperativeQuestions constă din aproximativ zece mii de înregistrări.
Principală problemă a acestei interogări este că pentru înregistrările din interogarea externă se execută o subinterogare pe vizualizarea [OperativeQuestions], care trebuie să restricționeze ieșirea pentru [Email] = @p__linq__0 (prin [START WITH …] CONNECT BY) la sute de înregistrări.
Și poate părea că subinterogarea ar trebui să calculeze o dată înregistrările pentru [Email] = @p__linq__0, iar apoi aceste câteva sute de înregistrări ar trebui să se unească după Id cu Questions, iar interogarea ar fi rapidă.
În realitate, are loc o unire secvențială a tuturor tabelelor: și verificarea corespondenței Id Questions cu Id-urile din OperativeQuestions, și filtrarea pe baza Email.
De fapt, interogarea lucrează cu toate zecile de mii de înregistrări OperativeQuestions, în timp ce sunt necesare doar datele relevante pentru Email.
Textul vizualizării OperativeQuestions:
Interogare nr. 2
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM [dbo].Questions AS Q INNER JOIN
[dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id
OUTER APPLY
(SELECT 1 AS HasNoObjects
WHERE NOT EXISTS
(SELECT 1
FROM [dbo].ObjectUserAccesses AS BOU
WHERE BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
[dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL
AND (BO.HasNoObjects = 1 OR
EXISTS (SELECT 1
FROM [dbo].ObjectUserAccesses AS BOU INNER JOIN
[dbo].ObjectQuestions AS QBO
ON QBO.[Object_Id] =BOU.ObjectId
WHERE BOU.ProcessUserAccessId = BPU.Id
AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));
Mappingul inițial al vizualizării în DbContext (EF Core 2)
public class QuestionsDbContext : DbContext
{
\/\/...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
\/\/...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
Interogarea LINQ inițială
var businessObjectsData = await context
.OperativeQuestions
.Where(x => x.Email == Email)
.Include(x => x.Question)
.Select(x => x.Question)
.SelectMany(x => x.ObjectQuestions,
(x, bo) => new
{
Id = x.Id,
ObjectId = bo.Object.Id,
ObjectTypeId = bo.Object.ObjectType.Id,
ObjectTypeName = bo.Object.ObjectType.Name,
ObjectExternalId = bo.Object.ExternalId
})
.ToListAsync();
În acest caz specific, se analizează soluția acestei probleme fără modificări ale infrastructurii, fără a introduce un tabel separat cu rezultate gata preparate ("Întrebări active"), pentru care ar fi necesar un mecanism de completare a datelor și menținerea acestora actualizate.
Deși aceasta este o soluție bună, există și o altă variantă de optimizare a acestei sarcini.
Scopul principal este de a salva în cache înregistrările după [Email] = @p__linq__0 din vista OperativeQuestions.
Introducem funcția tabelară [dbo].[OperativeQuestionsUserMail] în baza de date.
Prin trimiterea ca parametru de intrare a Emailului, obținem înapoi un tabel de valori:
Cererea nr. 3
CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
@Email nvarchar(4000)
)
RETURNS
@tbl TABLE
(
[Id] uniqueidentifier,
[Email] nvarchar(4000)
)
AS
BEGIN
INSERT INTO @tbl ([Id], [Email])
SELECT Id, @Email
FROM [OperativeQuestions] AS [x] WHERE [x].[Email] = @Email;
RETURN;
END
Aici se returnează un tabel de valori cu o structură de date predefinită.
Pentru ca solicitările către OperativeQuestionsUserMail să fie optime și să aibă planuri de interogare optime, este necesară o structură strictă, nu RETURNS TABLE AS RETURN…
În acest caz, interogarea căutată 1 se transformă în interogarea 4:
Cererea nr. 4
(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4]
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);
Maparea viziunii și funcției în DbContext (EF Core 2)
public class QuestionsDbContext : DbContext
{
//...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
//...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
public static class FromSqlQueries
{
public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
=> source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}
Interogarea finală LINQ
var businessObjectsData = await context
.OperativeQuestions
.GetByUserEmail(Email)
.Include(x => x.Question)
.Select(x => x.Question)
.SelectMany(x => x.ObjectQuestions,
(x, bo) => new
{
Id = x.Id,
ObjectId = bo.Object.Id,
ObjectTypeId = bo.Object.ObjectType.Id,
ObjectTypeName = bo.Object.ObjectType.Name,
ObjectExternalId = bo.Object.ExternalId
})
.ToListAsync();
Timpul de execuție a scăzut de la 200-800 ms la 2-20 ms, etc., adică de zeci de ori mai rapid.
Dacă privim mai general, în loc de 350 ms am obținut 8 ms.
Printre avantajele evidente, obținem și:
- o reducere generală a sarcinii de citire,
- o scădere semnificativă a probabilității blocajelor
- o reducere a timpului mediu de blocaj la valori acceptabile
Ieșire
Optimizarea și ajustarea apelurilor la baza de date Graf*, document prin LINQ reprezintă o sarcină care poate fi rezolvată.
În această lucrare, atenția și consecvența sunt foarte importante.
La începutul procesului:
- trebuie să verificăm datele cu care lucrează cererea (valorile, tipurile de date selectate)
- să efectuăm indexarea corectă a acestor date
- să verificăm corectitudinea condițiilor de unire între tabele
La următoarea iterație a optimizării se identifică:
- fundamentul cererii și se determină filtrul principal al cererii
- blocurile repetitive similare ale cererii și se analizează intersecția condițiilor
- în SSMS sau alt GUI pentru SQL Server se optimizează în sine Interogare SQL (alocarea unui depozit intermediar de date, construirea cererii de rezultat utilizând acest depozit (pot exista mai multe))
- în etapa finală, luând ca bază rezultatul Interogare SQL, se restructurează structura a cererii LINQ
În cele din urmă, rezultatul obținut Interogarea LINQ trebuie să devină din punct de vedere structural identic cererii SQL optime identificate din punctul 3. Mulțumiri enorme colegilor
Mulțumiri
jobgemws și Fortis pentru ajutorul acordat în pregătirea acestui material. Introducere În acest articol au fost discutate câteva metode de optimizare a cererilor LINQ. Aici vom prezentăm și alte câteva.
Sursa: habr.com
