🥇Metodi di ottimizzazione delle query LINQ in C#.NET

Introduzione

In questo articolo sono stati considerati alcuni metodi di ottimizzazione delle query LINQ.
In questa sede presenteremo ulteriori approcci all'ottimizzazione del codice legati a query LINQ.

È noto che LINQ(Language-Integrated Query) è un linguaggio di interrogazione semplice e conveniente per la fonte di dati.

E LINQ to SQL è una tecnologia di accesso ai dati nei database. Si tratta di uno strumento potente per la gestione dei dati, in cui tramite un linguaggio dichiarativo vengono costruite le query, che saranno poi trasformate in query SQL per la piattaforma e inviate al server del database per l'esecuzione. Nel nostro caso, per database intendiamo MS SQL Server.

Tuttavia, Le query LINQ non vengono convertite in query SQL ottimizzate query SQL, che un DBA esperto scriverebbe tenendo conto di tutte le sfumature di ottimizzazione delle query SQL:

connessioni ottimali (JOIN) e filtraggio dei risultati (DOVE)
numerosi dettagli nell'uso delle connessioni e delle condizioni di raggruppamento
numerose variazioni nella sostituzione delle condizioni IN con EXISTSe NOT IN, <> su EXISTS
caching intermedio dei risultati tramite tabelle temporanee, CTE, variabili di tabella
uso della clausola (OPTION) con istruzioni e suggerimenti di tabella WITH (…)
l'uso di viste indicizzabili come uno dei mezzi per eliminare letture di dati ridondanti durante le selezioni

I principali colli di bottiglia nelle prestazioni sono delle query SQL durante la compilazione delle query LINQ sono:

la consolidazione dell'intero meccanismo di selezione dei dati in un'unica query
la duplicazione di blocchi di codice identici, che porta infine a letture di dati superflue multiple
gruppi di condizioni composite (logiche ‘e’ e ‘o’) — AND e OR, che, unendosi in condizioni complesse, porta il ottimizzatore, avendo gli indici non clusterizzati adeguati sui campi necessari, a iniziare a eseguire la scansione sull'indice cluster (INDEX SCAN) per gruppi di condizioni
l'annidamento profondo delle sottoquery rende molto problematico l'analisi delle istruzioni SQL e l'analisi del piano delle query da parte degli sviluppatori e DBA

Metodi di ottimizzazione

Passiamo ora direttamente ai metodi di ottimizzazione.

1) Indicizzazione aggiuntiva

È meglio analizzare i filtri sulle tabelle principali di campionamento, poiché molto spesso l'intera query è costruita attorno a una o due tabelle principali (richieste-persone-operazioni) e con un set standard di condizioni (IsClosed, Canceled, Enabled, Status). È importante creare indici appropriati per i campioni identificati.

Questa soluzione ha senso quando la selezione su questi campi riduce significativamente l'insieme restituito dalla query.

Ad esempio, abbiamo 500000 richieste. Tuttavia, le richieste attive sono solo 2000 record. Un indice ben selezionato ci permetterà di INDEX SCAN navigare su una grande tabella e scegliere rapidamente i dati tramite un indice non clusterizzato.

Inoltre, la mancanza di indici può essere identificata attraverso i suggerimenti dell'analisi dei piani di query o la raccolta di statistiche delle viste di sistema. MS SQL Server:

Tutte le informazioni delle viste contengono dati sugli indici mancanti, ad eccezione degli indici spaziali.

Tuttavia, indici e caching sono spesso metodi per affrontare le conseguenze di codice mal scritto. delle query LINQ e delle query SQL.

Come dimostra la cruda realtà della vita aziendale, è spesso cruciale implementare le funzionalità di business entro scadenze specifiche. Pertanto, spesso richieste pesanti vengono tradotte in modalità in background con caching.

In parte, questo è giustificato, poiché l'utente non ha sempre bisogno dei dati più recenti, garantendo un livello di risposta dell'interfaccia utente accettabile.

Questo approccio consente di soddisfare le richieste aziendali ma riduce l'efficacia del sistema informativo, ritardando semplicemente la risoluzione dei problemi.

È anche importante ricordare che durante la ricerca degli indici necessari da aggiungere, le proposte MS SQL per l'ottimizzazione potrebbero essere errate, specialmente nelle seguenti condizioni:

se esistono già indici con un insieme simile di campi
se i campi nella tabella non possono essere indicizzati a causa di vincoli di indicizzazione (ne discutiamo più in dettaglio qui).

2) Unione degli attributi in un nuovo attributo

A volte, alcuni campi di una tabella, su cui si basano condizioni di gruppo, possono essere sostituiti introducendo un nuovo campo.

Particolarmente rilevante per i campi di stato, che per tipo sono solitamente bit o interi.

Esempio:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 viene sostituito con Status = 1.

Qui viene inserito un attributo intero Status, fornito dal riempimento di questi stati nella tabella. Successivamente, viene effettuata l'indicizzazione di questo nuovo attributo.

Questa è una soluzione fondamentale al problema delle prestazioni, poiché accediamo ai dati senza calcoli superflui.

3) Materializzazione della vista

Sfortunatamente, in query LINQ non è possibile utilizzare direttamente tabelle temporanee, CTE e variabili di tabella.

Tuttavia, c'è un altro modo di ottimizzare in questo caso: le viste indicizzate.

Un gruppo di condizioni (dell'esempio precedente) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (o un insieme di altre condizioni simili) diventa una buona opzione per utilizzarle in una vista indicizzabile, memorizzando un piccolo sottoinsieme di dati da una grande quantità.

Ma ci sono alcune limitazioni nella materializzazione della vista:

l'utilizzo di sottoquery, le clausole EXISTS devono essere sostituite dall'uso di JOIN
non è possibile utilizzare clausole UNION, UNION ALL, ECCEZIONE, INTERSECT
non è possibile utilizzare suggerimenti sulla tabella e clausole OPTION
non è possibile lavorare con cicli
impossibile visualizzare dati in un'unica vista da tabelle diverse

È importante ricordare che il reale vantaggio derivante dall'uso di una vista indicizzabile può essere ottenuto solo una volta che è stata indicizzata.

Ma quando si richiama la vista, questi indici potrebbero non essere utilizzati, e per utilizzarli esplicitamente è necessario specificare WITH (NOEXPAND).

Poiché in query LINQ non è possibile definire hints tabellari, è necessario creare un'altra vista — un «wrapper» della seguente forma:

CREATE VIEW NOME_della_vista AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Utilizzo delle funzioni tabellari

Spesso in query LINQ grandi blocchi di sottoquery o blocchi che utilizzano viste con strutture complesse formano una query finale con una struttura di esecuzione molto complessa e non ottimale.

I principali vantaggi dell'uso delle funzioni tabellari in query LINQ:

La possibilità, come nel caso delle viste, di utilizzare e specificare come oggetto, ma è possibile passare un insieme di parametri in ingresso:
FROM FUNCTION(@param1, @param2 …)
alla fine si può ottenere una selezione flessibile dei dati
Quando si utilizza la funzione tabellare, ci sono meno restrizioni rispetto a quelle degli indici descritti sopra:
1. Suggerimenti tabellari:
  tramite LINQ non è possibile specificare quali indici devono essere utilizzati e definire il livello di isolamento dei dati durante la query.
  Ma con la funzione queste opzioni sono disponibili.
  Con la funzione è possibile ottenere un piano di esecuzione della query relativamente costante, dove sono definite le regole per la gestione degli indici e i livelli di isolamento dei dati.
2. L'uso della funzione consente, rispetto agli indici, di ottenere:
  - logiche complesse di estrazione dei dati (fino all'uso di cicli)
  - estrazione di dati da molte diverse tabelle
  - l'uso UNION e EXISTS
La direttiva OPTION è molto utile quando è necessario gestire la parallelizzazione. OPTION(MAXDOP N), legato all'ordine del piano di esecuzione della query. Ad esempio:
- è possibile forzare la ricreazione del piano di esecuzione della query. OPTION (RECOMPILE)
- è possibile specificare la necessità di garantire l'uso forzato dell'ordine di join specificato nella query. OPTION (FORCE ORDER)
Più dettagli su OPTION è descritto qui.
L'utilizzo della sezione più ristretta e necessaria dei dati:
Non è necessario mantenere grandi set di dati nelle cache (come nel caso delle viste indicizzate), da cui è poi necessario filtrare i dati per un parametro.
Ad esempio, c'è una tabella che utilizza per il campo DOVE tre colonne (a, b, c).
Convenzionalmente, per tutte le query c'è una condizione fissa a = 0 e b = 0.
Tuttavia, la query sul campo c è più variabile.
Supponiamo che la condizione a = 0 e b = 0 ci aiuti davvero a limitare il set di risultati richiesto a diverse migliaia di record, ma la condizione su con restringe il campione a cento record.
Qui una funzione tabellare può rivelarsi un'opzione più vantaggiosa.
Inoltre, la funzione tabellare è più prevedibile e costante in termini di tempo di esecuzione.

Esempi

Consideriamo un esempio di implementazione utilizzando il database Questions.

C'è una query SELECT, che combina più tabelle e utilizza una vista (OperativeQuestions), in cui si verifica l'appartenenza tramite email (attraverso EXISTS) agli 'Ordini Attivi' ([OperativeQuestions]):

Richiesta 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])
));

La vista ha una struttura piuttosto complessa: presenta join di sottoquery e utilizzo della classificazione DISTINCT, che in generale è un'operazione piuttosto dispendiosa in termini di risorse.

La selezione da OperativeQuestions contiene circa diecimila record.

Il problema principale di questa query è che per i record della sottoquery esterna viene eseguita una sottoquery interna sulla vista [OperativeQuestions], che deve limitare l'output per [Email] = @p__linq__0 (attraverso EXISTS) a centinaia di record.

E potrebbe sembrare che la subquery dovrebbe calcolare una volta le righe per [Email] = @p__linq__0, e poi queste poche centinaia di righe dovrebbero essere unite per Id con Questions, e la query sarebbe veloce.

In realtà, viene eseguita un'unione sequenziale di tutte le tabelle: sia il controllo della corrispondenza tra Id Questions e Id in OperativeQuestions, sia il filtraggio per Email.

In sostanza, la query lavora con tutte le decine di migliaia di record in OperativeQuestions, mentre servono solo i dati rilevanti per Email.

Testo di presentazione di OperativeQuestions:

Richiesta n. 2

 
CREA VISUALIZZAZIONE [dbo].[OperativeQuestions]
COME
SELEZIONA DISTINCT Q.Id, USR.email COME Email
DA [dbo].Questions COME Q INNER JOIN
[dbo].ProcessUserAccesses COME BPU ON BPU.ProcessId = CQ.Process_Id 
OUTER APPLY
(SELECT 1 AS HasNoObjects
WHERE NOT EXISTS
(SELECT 1
FROM [dbo].ObjectUserAccesses COME BOU
WHERE BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
[dbo].Users COME USR ON USR.Id = BPU.UserId
DOVE CQ.[Exp] = 0 E CQ.AnswerId È NULL E BPU.[To] È NULL
E (BO.HasNoObjects = 1 O
ESISTE (SELECT 1
DA [dbo].ObjectUserAccesses COME BOU INNER JOIN
[dbo].ObjectQuestions COME QBO 
ON QBO.[Object_Id] = BOU.ObjectId
DOVE BOU.ProcessUserAccessId = BPU.Id
E BOU.[To] È NULL E QBO.Question_Id = CQ.Id));

Mappatura originale della vista in DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}

Query LINQ originale

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 questo caso specifico si prende in considerazione la risoluzione di questo problema senza modifiche infrastrutturali, senza introdurre una tabella separata con risultati precompilati ("Richieste attive"), per la quale sarebbe necessario un meccanismo per popolarla e mantenerla aggiornata.

Sebbene questa sia una buona soluzione, esiste un'altra opzione per ottimizzare questo compito.

L'obiettivo principale è memorizzare nella cache i record per [Email] = @p__linq__0 dalla vista OperativeQuestions.

Introduciamo la funzione tabellare [dbo].[OperativeQuestionsUserMail] nel database.

Passando come parametro di input l'Email, otteniamo una tabella di valori:

Richiesta 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

Qui viene restituita una tabella di valori con una struttura dati predefinita.

Per ottimizzare le query a OperativeQuestionsUserMail e avere piani di query efficienti, è necessaria una struttura rigorosa, non RESTITUISCE TABELLA COME RITORNO…

In questo caso, la Query 1 viene convertita in Query 4:

Query 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]);

Mapping della vista e della funzione in DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable GetByUserEmail(this DbQuery source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

Query LINQ finale

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

Il tempo di esecuzione è diminuito da 200-800 ms a 2-20 ms, e così via, quindi è decine di volte più veloce.

Se consideriamo valori medi, invece di 350 ms abbiamo ottenuto 8 ms.

Tra i vantaggi evidenti, otteniamo anche:

una riduzione complessiva del carico di lettura,
una significativa diminuzione della probabilità di blocchi
una riduzione del tempo medio di blocco a valori accettabili

Risultato

L'ottimizzazione e la messa a punto delle chiamate al DB MS SQL tramite LINQ è un compito che può essere risolto.

In questo lavoro, attenzione e coerenza sono fondamentali.

All'inizio del processo:

è necessario verificare i dati con cui la query opera (valori, tipi di dati selezionati)
effettuare un indicizzazione corretta di questi dati
verificare la correttezza delle condizioni di join tra le tabelle

Nella successiva iterazione di ottimizzazione si identificano:

la base della query e si definisce il filtro principale della query
i blocchi simili ripetitivi della query e si analizzano le sovrapposizioni delle condizioni
in SSMS o in un altro GUI per SQL Server si ottimizza stesso la query SQL (creazione di un'archiviazione intermedia, costruzione della query finale utilizzando tale archiviazione (potrebbero essere più di una))
nell'ultimo passaggio, prendendo come base il risultato la query SQL, si ridefinisce la struttura della query LINQ

Alla fine, la query LINQ ottenuta deve diventare strutturalmente identica alla migliore query SQL di cui al punto 3.

Ringraziamenti

Un grande grazie ai colleghi jobgemws e alex_ozr dalla compagnia Fortis per l'aiuto nella preparazione di questo materiale.

Fonte: habr.com