🥇Metodi per ottimisà e dumande LINQ in C#.NET

Introduzione

В stu articulu alcuni metudi di ottimisazione sò stati cunsiderati dumande LINQ.
Quì avemu ancu prisentà un pocu di più avvicinamenti à l'ottimisazione di codice in relazione dumande LINQ.

Hè cunnisciutu chì LINQ(Language-Integrated Query) hè una lingua simplice è còmuda per dumandà una fonte di dati.

А LINQ à SQL hè una tecnulugia per accede à e dati in un DBMS. Questu hè un strumentu putente per travaglià cù dati, induve e dumande sò custruite per mezu di una lingua dichjarativa, chì sarà dopu cunvertita in dumande SQL piattaforma è mandatu à u servitore di basa di dati per l'esecuzione. In u nostru casu, per DBMS vulemu dì MS SQL Server.

Tuttavia, dumande LINQ ùn sò micca cunvertiti in scritti ottimali dumande SQL, chì un DBA espertu puderia scrive cù tutti i sfumaturi di ottimisazione dumande SQL:

cunnessione ottimali (JOIN) è filtra i risultati (Dà)
parechje sfumature in l'usu di cunnessione è cundizioni di gruppu
parechje variazioni in e cundizioni di rimpiazzamentu IN nantu ESISTEи NON IN, <> on ESISTE
caching intermediu di i risultati via tavule tempuranee, CTE, variabili di tabella
usu di frase (OPTION) cù struzzioni è cunsiglii di tavulinu CON (...)
utilizendu viste indexate cum'è unu di i mezi per sbarazzà di e letture di dati redundante durante e selezzione

I principali colli di bottiglia di rendiment di u risultatu dumande SQL quandu compie dumande LINQ sò:

cunsulidazione di tuttu u mecanismu di selezzione di dati in una dumanda
duplicà blocchi identici di codice, chì ultimamente porta à parechje letture di dati innecessarii
gruppi di cundizioni multi-cumpunenti (logicu "è" è "o") - AND и OR, cumminendu in cundizioni cumplessi, porta à u fattu chì l'ottimisatore, avendu indici adatti micca clustered per i campi necessarii, infine principia à scansà contru l'indici clustered (INDEX SCAN) per gruppi di cundizioni
a nidificazione profonda di sottoquestioni rende l'analisi assai problematica dichjarazioni SQL è analisi di u pianu di dumanda da parte di i sviluppatori è DBA

I metudi di ottimisazione

Avà andemu direttamente à i metudi di ottimisazione.

1) Indicizzamentu supplementu

Hè megliu di cunsiderà i filtri nantu à e tavule di selezzione principali, postu chì assai spessu tutta a quistione hè custruita intornu à una o duie tavule principali (applicazioni-persone-operazioni) è cù un settore standard di cundizioni (IsClosed, Canceled, Enabled, Status). Hè impurtante di creà indici adattati per i campioni identificati.

Questa suluzione hà sensu quandu selezziunate questi campi limita significativamente u settore restituitu à a dumanda.

Per esempiu, avemu 500000 2000 applicazioni. Tuttavia, ci sò solu XNUMX applicazioni attive. Allora un indice sceltu currettamente ci salverà da INDEX SCAN nantu à una grande tavula è vi permetterà di selezziunà rapidamente e dati attraversu un indexu micca cluster.

Inoltre, a mancanza d'indici pò esse identificata per mezu di richieste per l'analisi di i piani di dumanda o a cullezzione di statistiche di vista di u sistema. MS SQL Server:

Tutti i dati di vista cuntenenu infurmazione nantu à l'indici mancanti, cù l'eccezzioni di l'indici spaziali.

In ogni casu, l'indici è u caching sò spessu metudi di cumbattimentu di e cunsequenze di pocu scrittu dumande LINQ и dumande SQL.

Cum'è a dura pratica di a vita mostra, hè spessu impurtante per un affari di implementà e funzioni di l'affari per certi termini. È per quessa, e richieste pisanti sò spessu trasferite à u fondu cù caching.

Questu hè in parte ghjustificatu, postu chì l'utilizatore ùn hà micca sempre bisognu di l'ultimi dati è ci hè un livellu accettabile di rispunsibilità di l'interfaccia d'utilizatore.

Stu approcciu permette di risolve i bisogni di l'imprese, ma infine riduce u rendiment di u sistema d'informazione solu ritardà solu suluzione à i prublemi.

Hè vale a pena ricurdà chì in u prucessu di ricerca di l'indici necessarii per aghjunghje, suggerimenti MS SQL l'ottimisazione pò esse sbagliata, ancu in e seguenti cundizioni:

s'ellu ci sò digià indici cù un settore simili di campi
se i campi in a tavula ùn ponu micca esse indexati per via di restrizioni di indexazione (discritte in più detail ccà).

2) Unisce l'attributi in un novu attributu

Calchì volta certi campi da una tavula, chì serve com'è una basa per un gruppu di cundizioni, ponu esse rimpiazzati intruducendu un novu campu.

Questu hè soprattuttu veru per i campi di statutu, chì sò generalmente o bit o integer in tipu.

Esempiu:

IsClosed = 0 AND Cancelled = 0 AND Enabled = 0 hè rimpiazzatu da Status = 1.

Questu hè induve l'attributu Status integer hè introduttu per assicurà chì questi stati sò populati in a tavula. Dopu, stu novu attributu hè indexatu.

Questa hè una suluzione fundamentale à u prublema di rendiment, perchè Accessemu dati senza calculi innecessarii.

3) Materializazione di a vista

Sfurtunatamente in dumande LINQ E tavule tempuranee, CTE è e variàbili di tavule ùn ponu micca esse aduprate direttamente.

In ogni casu, ci hè un altru modu per ottimisà per questu casu - viste indexate.

Gruppu di cundizione (da l'esempiu sopra) IsClosed = 0 AND Cancelled = 0 AND Enabled = 0 (o un settore di altre cundizioni simili) diventa una bona opzione per aduprà in una vista indexata, cachendu una piccula fetta di dati da un grande settore.

Ma ci sò parechje restrizioni quandu si materializza una vista:

usu di subqueries, clause ESISTE deve esse rimpiazzatu cù usu JOIN
ùn pudete micca aduprà frasi UNION, UNIONE TUTTI, ECCEZIONE, INTERSETTU
Ùn pudete micca aduprà suggerimenti è clausole di tavule OPTION
nisuna pussibilità di travaglià cù ciculi
Hè impussibile di vede dati in una vista da diverse tavule

Hè impurtante di ricurdà chì u veru benefiziu di l'usu di una vista indexata pò esse ottenuta solu da l'indexazione.

Ma quandu chjamate una vista, questi indici ùn ponu micca esse utilizati, è per usà in modu esplicitu, deve specificà CUN (NOEXPAND).

Dapoi in dumande LINQ Hè impussibile di definisce i suggerimenti di a tavola, cusì avete da creà una altra rapprisintazioni - un "wrapper" di a forma seguente:

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Utilizà e funzioni di a tavola

Spessu in dumande LINQ Grandi blocchi di sottoquestioni o blocchi chì utilizanu viste cù una struttura cumplessa formanu una quistione finale cù una struttura di esecuzione assai cumplessa è subottimale.

Vantaggi chjave di l'usu di e funzioni di a tabella in dumande LINQ:

A capacità, cum'è in u casu di vista, per esse usatu è specificatu cum'è un ughjettu, ma pudete passà un set di parametri di input:
FROM FUNCTION(@param1, @param2 ...)
In cunsiquenza, pò esse realizatu un campionamentu di dati flexible
In u casu di utilizà una funzione di tavula, ùn ci sò micca restrizioni forti cum'è in u casu di viste indexate descritte sopra:
1. Cunsiglii di a tavola:
  attraversu LINQ Ùn pudete micca specificà quale indici deve esse utilizatu è determinà u livellu di isolamentu di dati quandu si dumanda.
  Ma a funzione hà sti capacità.
  Cù a funzione, pudete ottene un pianu di dumanda di esecuzione abbastanza constante, induve e regule per travaglià cù indici è livelli di isolamentu di dati sò definiti.
2. Utilizà a funzione permette, in cunfrontu cù viste indexate, per ottene:
  - logica cumplessa di campionamentu di dati (ancu usendu loops)
  - catturà dati da parechje tavule diverse
  - l 'usu di UNION и ESISTE
Offra OPTION assai utile quandu avemu bisognu di furnisce un cuntrollu di cuncurrenza OPZIONE (MAXDOP N), l'ordine di u pianu di esecuzione di a dumanda. Per esempiu:
- pudete specificà una ricreazione forzata di u pianu di a dumanda OPZIONE (RECOMPILE)
- pudete specificà s'ellu deve furzà u pianu di a dumanda à utilizà l'ordine di cunghjunzione specificatu in a dumanda OPZIONE (ORDINE FORZA)
Più dettagli nantu à OPTION descrittu ccà.
Utilizendu a fetta di dati più stretta è necessaria:
Ùn ci hè micca bisognu di almacenà grande setti di dati in cache (cum'è u casu cù viste indexate), da quale avete sempre bisognu di filtrà e dati per paràmetru.
Per esempiu, ci hè una tavola chì u so filtru Dà sò usati trè campi (a, b, c).

Convenzionalmente, tutte e dumande anu una cundizione constante a = 0 è b = 0.

Tuttavia, a dumanda di u campu c più variabile.

Lasciate a cundizione a = 0 è b = 0 Ci aiuta veramente à limità l'inseme risultatu necessariu à millaie di dischi, ma a cundizione hè с riduce a selezzione à centu records.

Quì a funzione di a tavula pò esse una opzione megliu.

Inoltre, una funzione di tavula hè più prevedibile è coherente in u tempu di esecuzione.

esempi

Fighjemu un esempiu di implementazione utilizendu a basa di dati Questions cum'è un esempiu.

Ci hè una dumanda SELECT, chì combina parechje tavule è usa una vista (OperativeQuestions), in quale l'affiliazione hè verificata per email (via ESISTE) à "Domande operative":

A dumanda n ° 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])
));

A vista hà una struttura piuttostu cumplessa: hà unizzioni di subquery è usa l'ordinamentu DISTINCT, chì in generale hè una operazione abbastanza intensiva di risorse.

Un sample da OperativeQuestions hè di circa deci mila dischi.

U prublema principali cù sta dumanda hè chì per i registri da a dumanda esterna, una subquery interna hè eseguita nantu à a vista [OperativeQuestions], chì deve per [E-mail] = @p__linq__0 ci permette di limità a selezzione di output (via ESISTE) finu à centinaie di dischi.

È pò pare chì a subquery duveria calculà i registri una volta per [Email] = @p__linq__0, è dopu questi paru di centu records deve esse cunnessi da Id with Questions, è a quistione serà rapida.

In fatti, ci hè una cunnessione sequenziale di tutte e tavule: cuntrollà a currispundenza di Id Questions cù Id da OperativeQuestions, è filtru per Email.

In fattu, a dumanda travaglia cù tutti i decine di millaie di registri OperativeQuestions, ma solu i dati di interessu sò necessarii via Email.

OperativeQuestions vede u testu:

A dumanda n ° 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));

Mappatura di vista iniziale in 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");
    }
}

Interrogazione LINQ iniziale

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();

In questu casu particulari, simu cunsiderendu una suluzione à stu prublema senza cambiamenti infrastrutturali, senza introduzione di una tavola separata cù risultati pronti ("Quistioni attivi"), chì avaristi bisognu di un mecanismu per u riempimentu di dati è di mantene a data. .

Ancu s'ellu hè una bona suluzione, ci hè una altra opzione per ottimisà stu prublema.

U scopu principale hè di cache l'entrata da [Email] = @p__linq__0 da a vista OperativeQuestions.

Introduce a funzione di tabella [dbo].[OperativeQuestionsUserMail] in a basa di dati.

Mandendu e-mail cum'è un paràmetru di input, ritruvemu una tabella di valori:

A dumanda n ° 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

Questu torna una tabella di valori cù una struttura di dati predefinita.

Per fà e dumande à OperativeQuestionsUserMail per esse ottimali è avè piani di ricerca ottimali, una struttura stretta hè necessaria, è micca. RETURNS TABLE AS RETURN...

In questu casu, a Query 1 necessaria hè cunvertita in Query 4:

A dumanda n ° 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]);

Mappatura di viste è funzioni in 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})");
}

Query finale 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();

L'ordine di u tempu di esecuzione hè cascatu da 200-800 ms, à 2-20 ms, etc., vale à dì decine di volte più veloce.

Se pigliemu più mediamente, allora invece di 350 ms avemu 8 ms.

Da i vantaghji evidenti avemu ancu:

riduzione generale di a carica di lettura,
riduzzione significativa di a probabilità di bloccu
riducendu u tempu di bloccu mediu à i valori accettabili

cunchiusioni

Ottimisazione è fine-tuning di e chjama di basa di dati MS SQL attraversu LINQ hè un prublema chì pò esse risolta.

L'attenzione è a coherenza sò assai impurtanti in stu travagliu.

À u principiu di u prucessu:

hè necessariu di verificà e dati cù quale travaglia a dumanda (valori, tipi di dati selezziunati)
realizà una indexazione curretta di questi dati
verificate a curretta di e cundizioni di cunghjunzione trà e tavule

A prossima iterazione di ottimisazione revela:

basa di a dumanda è definisce u filtru di dumanda principale
ripitendu blocchi di quistione simili è analizendu l'intersezzione di e cundizioni
in SSMS o altre GUI per Servidor SQL s'ottimizza sè stessu Query SQL (assignendu un almacenamentu di dati intermediu, custruendu a dumanda resultanti utilizendu stu almacenamentu (ci pò esse parechji))
in l'ultima tappa, pigliendu com'è basa u risultatu Query SQL, a struttura hè stata ricustruita dumanda LINQ

U risultatu dumanda LINQ deve diventà identica in struttura à l'ottima identificatu Query SQL da u puntu 3.

Ringraziamenti

Grazie mille à i culleghi i travaglii и alex_ozr da a cumpagnia Fortis per aiutà à preparà stu materiale.

Source: www.habr.com

Metudu per ottimisà e dumande LINQ in C#.NET