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

Introduzione

In questo articolo sono stati considerati alcuni metodi di ottimizzazione di query LINQ.
Qui presenteremo anche alcuni approcci all’ottimizzazione del codice, relativi a query LINQ.

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

A LINQ to SQL è una tecnologia di accesso ai dati nei DBMS. È uno strumento potente per lavorare con i dati, dove attraverso un linguaggio dichiarativo si costruiscono query, che vengono poi trasformate in query SQL e inviate al server del database per l'esecuzione. In questo caso, consideriamo MS SQL Server.

Tuttavia, query LINQ non vengono trasformate in ciò che potrebbe scrivere un DBA esperto con tutte le sfumature di ottimizzazione query SQLdelle query SQL le connessioni ottimali (:

) e il filtraggio dei risultati (JOINuna serie di sfumature nell'uso delle connessioni e delle condizioni di gruppoDOVE)
una serie di variazioni nella sostituzione delle condizioni
NOT IN IN in ESISTEe , incaching intermedio dei risultati tramite tabelle temporanee, CTE, variabili di tabella ESISTE
l'uso della clausola (
OPTION) con indicazioni e suggerimenti sulle tabelleWITH l'uso delle viste indicizzate, come uno dei mezzi per eliminare letture ridondanti dei dati durante le selezioni (…)
I principali colli di bottiglia nelle prestazioni risultanti

durante la compilazione le connessioni ottimali ( sono: di query LINQ la consolidazione dell'intero meccanismo di selezione dei dati in un'unica query

la duplicazione di blocchi di codice identici, che porta quindi a letture multiple eccessive dei dati
gruppi di condizioni complesse (logiche «e» e «o») —
, unendosi in condizioni complesse, porta il ottimizzatore, avendo indici non cluster adatti sui campi necessari, a cominciare a eseguire scansioni su un indice cluster ( E e ORINDEX SCAN) per gruppi di condizionil'alta nidificazione delle sottoquery rende molto problematico l'analisi
delle istruzioni SQL e l'analisi del piano di query da parte degli sviluppatori e Metodi di ottimizzazione DBA

Ora passiamo direttamente ai metodi di ottimizzazione.

1) Indicizzazione aggiuntiva

È meglio considerare i filtri nelle tabelle principali delle query, poiché molto spesso l'intera query si basa su una o due tabelle principali (richieste-persone-operazioni) con un insieme standard di condizioni (IsClosed, Canceled, Enabled, Status). È importante creare indici appropriati per i risultati identificati.

Questa soluzione ha senso quando la selezione di questi campi limita sostanzialmente l'insieme restituito dalla query.

Ad esempio, abbiamo 500000 richieste. Tuttavia, ci sono solo 2000 richieste attive. Un indice ben progettato ci libererà ) per gruppi di condizioni da una grande tabella e permetterà di selezionare rapidamente i dati tramite un indice non clusterizzato.

È possibile anche identificare la mancanza di indici attraverso suggerimenti per l'analisi dei piani delle query o la raccolta di statistiche delle viste di sistema MS SQL Server:

Tutti i dati della vista contengono informazioni sugli indici mancanti, eccetto per gli indici spaziali.

Tuttavia, gli indici e la memorizzazione nella cache sono spesso metodi per affrontare le conseguenze di una scrittura scorretta. di query LINQ e le connessioni ottimali (.

Come dimostra la dura realtà della vita aziendale, spesso è importante realizzare funzionalità aziendali entro determinate scadenze. Per questo motivo, molte query pesanti vengono eseguite in background con caching.

Parzialmente, questo è giustificato, poiché l'utente non ha sempre bisogno dei dati più recenti e si verifica un livello di risposta accettabile per l'interfaccia utente.

Questo approccio consente di soddisfare le richieste aziendali, ma alla fine diminuisce l'efficienza del sistema informatico, rimandando semplicemente la risoluzione dei problemi.

Vale anche la pena ricordare che nel processo di ricerca di nuovi indici da aggiungere, le proposte MS SQL per l'ottimizzazione potrebbero essere errate, anche 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 (dettagli a riguardo sono descritti qui).

2) Combinazione di attributi in un nuovo attributo

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

Questo è particolarmente pertinente per i campi di stato, che di solito sono di tipo booleano o intero.

Esempio:

IsClosed = 0 E Canceled = 0 E Enabled = 0 è sostituito da Status = 1.

Qui si inserisce l'attributo intero Statuto, fornito dalla compilazione di questi stati nella tabella. Successivamente, viene effettuata l'indicizzazione di questo nuovo attributo.

Questa è una soluzione fondamentale per il problema delle prestazioni, poiché accediamo ai dati senza ulteriori calcoli.

3) Materializzazione della vista

Sfortunatamente, in LE 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 indicizzabili.

Il gruppo di condizioni (dell'esempio sopra) IsClosed = 0 E Canceled = 0 E Enabled = 0 (o un insieme di altre condizioni simili) diventa una buona opzione per utilizzarle in una vista indicizzabile, memorizzando un piccolo insieme di dati da un vasto insieme.

Ma ci sono alcune limitazioni nella materializzazione della vista:

l'uso di sottoquery, proposte ESISTE deve essere sostituito dall'uso di JOIN
non è possibile utilizzare proposte UNIONE, UNION ALL, ECCEZIONE, INTERSEZIONE
non è possibile utilizzare suggerimenti di tabella e proposte ) con indicazioni e suggerimenti sulle tabelle
non c'è possibilità di lavorare con cicli
non è possibile estrarre dati in una vista da tabelle diverse

È importante ricordare che il reale vantaggio dell'utilizzo di una vista indicizzabile può essere ottenuto effettivamente solo quando è indicizzata.

Ma quando si richiama la vista, questi indici potrebbero non essere utilizzati, e per utilizzare esplicitamente CON (NOEXPAND).

Poiché in LE QUERY LINQ non è possibile definire suggerimenti di tabella, è necessario creare un'altra vista - un 'involucro' della seguente forma:

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

4) Utilizzo di funzioni di tabella

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

I principali vantaggi dell'utilizzo di funzioni di tabella in LE QUERY LINQ:

La possibilità, come nel caso delle viste, di utilizzare e specificare come oggetto, ma è possibile passare un insieme di parametri di input:
FROM FUNCTION(@param1, @param2 …)
alla fine si può ottenere una selezione flessibile dei dati
Nel caso dell'uso della funzione di tabella, non ci sono limitazioni così severe come nel caso delle viste indicizzabili, descritte sopra:
1. Suggerimenti di tabella:
  tramite LINQ non è possibile specificare quali indici utilizzare e determinare il livello di isolamento dei dati durante la query.
  Ma nella funzione queste possibilità ci sono.
  Con la funzione si può ottenere un piano di esecuzione della query sufficientemente stabile, dove sono definite le regole di utilizzo degli indici e i livelli di isolamento dei dati.
2. L'uso della funzione consente, rispetto alle viste indicizzate, di ottenere:
  - logiche di estrazione dei dati complesse (fino all'uso di cicli)
  - estrazioni di dati da molteplici tabelle diverse
  - l'uso UNIONE e ESISTE
La proposta ) con indicazioni e suggerimenti sulle tabelle è molto utile quando dobbiamo gestire il parallelismo OPTION(MAXDOP N), in relazione al piano di esecuzione della query. Ad esempio:
- si può specificare la ricreazione forzata del piano di query OPTION (RECOMPILE)
- si può indicare la necessità di garantire l'uso forzato da parte del piano di query dell'ordine di join specificato nella query OPTION (FORCE ORDER)
Più in dettaglio su ) con indicazioni e suggerimenti sulle tabelle è descritto qui.
Utilizzo del più stretto e richiesto campione di dati:
Non c'è bisogno di mantenere grandi set di dati nella cache (come nel caso delle viste indicizzate), da cui è necessario filtrare ulteriormente i dati per parametro.
Ad esempio, c'è una tabella che per il filtro DOVE utilizza tre campi (a, b, c).
Condizionalmente, per tutte le query esiste una condizione fissa a = 0 e b = 0.
Tuttavia, la query sul campo c è più variabile.
Poniamo che la condizione a = 0 e b = 0 ci aiuti realmente a limitare il set di risultati necessario a mille record, ma la condizione per con ci restringe il campione a cento record.
Qui, la funzione tabellare potrebbe rivelarsi una soluzione più vantaggiosa.
Inoltre, la funzione tabellare è più prevedibile e costante in termini di tempo di esecuzione.

Esempi

Consideriamo un esempio di implementazione con il database Questions.

C'è una query SELECT, che unisce diverse tabelle e utilizza una vista (OperativeQuestions), nella quale si verifica l'appartenenza tramite email (attraverso ESISTE) a "Richieste Attive"([OperativeQuestions]):

Query n. 1

(@p__linq__0 nvarchar(4000))SELEZIONA
1 COME [C1],
[Extent1].[Id] COME [Id],
[Join2].[Object_Id] COME [Object_Id],
[Join2].[ObjectType_Id] COME [ObjectType_Id],
[Join2].[Name] COME [Name],
[Join2].[ExternalId] COME [ExternalId]
DA [dbo].[Questions] COME [Extent1]
INNER JOIN (SELEZIONA [Extent2].[Object_Id] COME [Object_Id],
[Extent2].[Question_Id] COME [Question_Id], [Extent3].[ExternalId] COME [ExternalId],
[Extent3].[ObjectType_Id] COME [ObjectType_Id], [Extent4].[Name] COME [Name]
DA [dbo].[ObjectQuestions] COME [Extent2]
INNER JOIN [dbo].[Objects] COME [Extent3] SU [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] COME [Extent4] 
SU [Extent3].[ObjectType_Id] = [Extent4].[Id] ) COME [Join2] 
SU [Extent1].[Id] = [Join2].[Question_Id]
DOVE ([Extent1].[AnswerId] È NULL) E (0 = [Extent1].[Exp]) E ( ESISTE (SELEZIONA
1 COME [C1]
DA [dbo].[OperativeQuestions] COME [Extent5]
DOVE (([Extent5].[Email] = @p__linq__0) O (([Extent5].[Email] È NULL) 
E (@p__linq__0 È NULL))) E ([Extent5].[Id] = [Extent1].[Id])
));

La vista ha una struttura piuttosto complessa: include unioni di sottoquery e utilizzo di ordinamenti. DISTINCT, che in generale è un'operazione piuttosto dispendiosa in termini di risorse.

Selezione da OperativeQuestions di circa diecimila registrazioni.

Il principale problema di questa query è che per le registrazioni dalla query esterna viene eseguita una sottoquery sulla vista [OperativeQuestions], che dovrebbe limitarci l'output per [Email] = @p__linq__0 (attraverso ESISTE) a centinaia di registrazioni.

Sembra che la sottoquery debba calcolare una sola volta le registrazioni per [Email] = @p__linq__0, e poi queste paio di centinaia di registrazioni dovrebbero unirsi per Id con Questions, e la query sarà veloce.

In realtà, avviene una connessione sequenziale di tutte le tabelle: sia la verifica della corrispondenza degli Id di Questions con gli Id di OperativeQuestions, sia il filtraggio per Email.

In sostanza, la query lavora su tutte le decine di migliaia di registrazioni di OperativeQuestions, mentre servono solo i dati pertinenti per Email.

Il testo della vista OperativeQuestions:

Query n. 2

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

Mapping 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 considera la soluzione 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, c'è anche 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 ingresso Email, otteniamo indietro una tabella di valori:

Query 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 di dati previamente definita.

Affinché le query su OperativeQuestionsUserMail siano ottimali, con piani di query ottimali, è necessaria una struttura rigorosa, e non RETURNS TABLE AS RETURN…

In questo caso, la Query 1 cercata viene trasformata nella 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]);

Mappatura 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, ecc., quindi decine di volte più veloce.

Se consideriamo in modo più medio, invece di 350 ms abbiamo ottenuto 8 ms.

Tra i vantaggi ovvi otteniamo anche:

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

Conclusione

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

In questo lavoro, l'attenzione e la coerenza sono molto importanti.

All'inizio del processo:

è necessario controllare i dati con cui lavora la query (valori, tipi di dati selezionati)
eseguire una corretta indicizzazione 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 determina il filtro principale della query
blocchi di query simili ripetuti e si analizza l'intersezione delle condizioni
in SSMS o in un altro GUI per SQL Server ottimizzare Query SQL (allocazione di uno storage intermedio dei dati, costruzione della query finale utilizzando questo storage (possono essere diversi))
nella fase finale, prendendo come base la risultante Query SQL, si ricostruisce la struttura della query LINQ

Il risultato finale Query LINQ deve diventare strutturalmente identico alla query SQL ottimale identificata al punto 3. Un enorme ringraziamento ai colleghi

Ringraziamenti

jobgemws alex_ozr e dell'azienda Fortis per l'aiuto nella preparazione di questo materiale. Introduzione In questo articolo sono stati esaminati alcuni metodi di ottimizzazione delle query LINQ. Qui presenteremo ancora alcuni metodi.

Fonte: habr.com