🥇Metodi per ottimizzare le query LINQ in C#.NET

Introduzione

В questo articolo sono stati considerati alcuni metodi di ottimizzazione Query LINQ.
Qui presentiamo anche alcuni altri approcci all'ottimizzazione del codice relativi a Query LINQ.

E 'noto che LINQ(Language-Integrated Query) è un linguaggio semplice e conveniente per interrogare un'origine dati.

А LINQ a SQL è una tecnologia per accedere ai dati in un DBMS. Questo è un potente strumento per lavorare con i dati, in cui le query vengono costruite attraverso un linguaggio dichiarativo, in cui verranno poi convertite query SQL piattaforma e inviato al server del database per l'esecuzione. Nel nostro caso per DBMS intendiamo MS SQL Server.

Tuttavia, Query LINQ non vengono convertiti in scritti scritti in modo ottimale query SQL, che un DBA esperto potrebbe scrivere con tutte le sfumature dell'ottimizzazione query SQL:

connessioni ottimali (ISCRIVITI) e filtrando i risultati (DOVE)
molte sfumature nell’uso delle connessioni e delle condizioni di gruppo
molte variazioni nelle condizioni di sostituzione IN su ESISTEи NON IN, <> su ESISTE
memorizzazione nella cache intermedia dei risultati tramite tabelle temporanee, CTE, variabili di tabella
uso della frase (OPZIONE) con istruzioni e suggerimenti per le tabelle CON (...)
utilizzare le viste indicizzate come uno dei mezzi per eliminare le letture dei dati ridondanti durante le selezioni

I principali colli di bottiglia prestazionali del risultato query SQL durante la compilazione Query LINQ sono:

consolidamento dell'intero meccanismo di selezione dei dati in un'unica richiesta
duplicando blocchi di codice identici, che alla fine portano a molteplici letture di dati non necessarie
gruppi di condizioni multicomponente (logici “e” e “o”) - E и OR, combinandosi in condizioni complesse, porta al fatto che l'ottimizzatore, avendo indici non cluster adeguati per i campi necessari, alla fine inizia a eseguire la scansione rispetto all'indice cluster (SCANSIONE INDICE) per gruppi di condizioni
l'annidamento profondo delle sottoquery rende l'analisi molto problematica Istruzioni SQL e analisi del piano di query da parte degli sviluppatori e DBA

Metodi di ottimizzazione

Passiamo ora direttamente ai metodi di ottimizzazione.

1) Indicizzazione aggiuntiva

È meglio considerare i filtri sulle tabelle di selezione principali, poiché molto spesso l'intera query è costruita attorno a una o due tabelle principali (applicazioni-persone-operazioni) e con un insieme di condizioni standard (È chiuso, Annullato, Abilitato, Stato). È importante creare indici appropriati per i campioni identificati.

Questa soluzione ha senso quando la selezione di questi campi limita in modo significativo il set restituito alla query.

Ad esempio, abbiamo 500000 domande. Tuttavia, ci sono solo 2000 applicazioni attive. Quindi un indice correttamente selezionato ci salverà SCANSIONE INDICE su una tabella di grandi dimensioni e ti consentirà di selezionare rapidamente i dati tramite un indice non cluster.

Inoltre, la mancanza di indici può essere identificata tramite richieste per l'analisi dei piani di query o la raccolta di statistiche sulla visualizzazione del sistema MS SQL Server:

Tutti i dati della vista contengono informazioni sugli indici mancanti, ad eccezione degli indici spaziali.

Tuttavia, gli indici e la memorizzazione nella cache sono spesso metodi per combattere le conseguenze di una scrittura scadente Query LINQ и query SQL.

Come dimostra la dura pratica della vita, spesso è importante per un'azienda implementare le funzionalità aziendali entro determinate scadenze. Pertanto, le richieste pesanti vengono spesso trasferite in background con la memorizzazione nella cache.

Ciò è in parte giustificato, dal momento che l'utente non ha sempre bisogno dei dati più recenti e esiste un livello accettabile di reattività dell'interfaccia utente.

Questo approccio consente di risolvere le esigenze aziendali, ma alla fine riduce le prestazioni del sistema informativo semplicemente ritardando la soluzione dei problemi.

Vale anche la pena ricordare che nel processo di ricerca degli indici necessari da aggiungere, vengono forniti suggerimenti MS SQL l'ottimizzazione potrebbe non essere corretta, anche nelle seguenti condizioni:

se sono già presenti indici con un insieme di campi simile
se i campi nella tabella non possono essere indicizzati a causa di restrizioni di indicizzazione (descritte più in dettaglio qui).

2) Unire gli attributi in un nuovo attributo

A volte alcuni campi di una tabella, che servono come base per un gruppo di condizioni, possono essere sostituiti introducendo un nuovo campo.

Ciò è particolarmente vero per i campi di stato, che solitamente sono di tipo bit o intero.

Esempio:

È chiuso = 0 E annullato = 0 E abilitato = 0 sostituito da Stato = 1.

È qui che viene introdotto l'attributo intero Status per garantire che questi stati siano popolati nella tabella. Successivamente, questo nuovo attributo viene indicizzato.

Questa è una soluzione fondamentale al problema delle prestazioni, perché Accediamo ai dati senza calcoli inutili.

3) Materializzazione della vista

Sfortunatamente, a Query LINQ Le tabelle temporanee, le CTE e le variabili di tabella non possono essere utilizzate direttamente.

Tuttavia, esiste un altro modo per ottimizzare in questo caso: le visualizzazioni indicizzate.

Gruppo di condizioni (dall'esempio sopra) È chiuso = 0 E annullato = 0 E abilitato = 0 (o un insieme di altre condizioni simili) diventa una buona opzione per utilizzarli in una vista indicizzata, memorizzando nella cache una piccola porzione di dati da un insieme di grandi dimensioni.

Ma ci sono una serie di restrizioni quando si materializza una vista:

uso di subquery, clausole ESISTE dovrebbe essere sostituito utilizzando ISCRIVITI
non puoi usare frasi UNION, UNIONE TUTTI, un'ECCEZIONE, INTERSEZIONE
Non è possibile utilizzare suggerimenti e clausole di tabella OPZIONE
nessuna possibilità di lavorare con i cicli
È impossibile visualizzare i dati in un'unica vista da tabelle diverse

È importante ricordare che il vero vantaggio derivante dall'utilizzo di una vista indicizzata può essere ottenuto solo indicizzandola effettivamente.

Ma quando si chiama una vista, questi indici non possono essere utilizzati e per utilizzarli in modo esplicito è necessario specificarli CON(NO ESPANDI).

Dal in Query LINQ È impossibile definire i suggerimenti della tabella, quindi è necessario creare un'altra rappresentazione: un "wrapper" nel seguente formato:

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

4) Utilizzo delle funzioni di tabella

Spesso in 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 complessa e non ottimale.

Principali vantaggi dell'utilizzo delle funzioni di tabella in Query LINQ:

La possibilità, come nel caso delle visualizzazioni, di essere utilizzate e specificate come oggetto, ma è possibile passare una serie di parametri di input:
DALLA FUNZIONE(@param1, @param2 ...)
Di conseguenza, è possibile ottenere un campionamento flessibile dei dati
Quando si utilizza una funzione di tabella, non ci sono restrizioni così forti come nel caso delle viste indicizzate sopra descritte:
1. Suggerimenti per la tabella:
  attraverso LINQ Non è possibile specificare quali indici utilizzare e determinare il livello di isolamento dei dati durante l'esecuzione di una query.
  Ma la funzione ha queste capacità.
  Con la funzione è possibile ottenere un piano di query di esecuzione abbastanza costante, in cui vengono definite le regole per lavorare con gli indici e i livelli di isolamento dei dati
2. L'utilizzo della funzione permette, rispetto alle viste indicizzate, di ottenere:
  - logica complessa di campionamento dei dati (anche utilizzando loop)
  - recuperare dati da molte tabelle diverse
  - utilizzare UNION и ESISTE
Proposta OPZIONE molto utile quando dobbiamo fornire il controllo della concorrenza OPZIONE(MAXDOP N), l'ordine del piano di esecuzione della query. Per esempio:
- è possibile specificare una ricreazione forzata del piano di query OPZIONE (RICIMPILA)
- è possibile specificare se forzare il piano di query a utilizzare l'ordine di join specificato nella query OPZIONE (ORDINE FORZATO)
Maggiori dettagli su OPZIONE descritto qui.
Utilizzando la porzione di dati più stretta e richiesta:
Non è necessario archiviare grandi set di dati nelle cache (come nel caso delle viste indicizzate), da cui è comunque necessario filtrare i dati per parametro.
Ad esempio, esiste una tabella il cui filtro DOVE vengono utilizzati tre campi (a, b, c).

Convenzionalmente, tutte le richieste hanno una condizione costante a = 0 e b = 0.

Tuttavia, la richiesta del campo c più variabile.

Lasciamo la condizione a = 0 e b = 0 Ci aiuta davvero a limitare il set risultante richiesto a migliaia di record, ma la condizione è attiva с restringe la selezione a un centinaio di record.

In questo caso la funzione tabella potrebbe essere un'opzione migliore.

Inoltre, una funzione di tabella è più prevedibile e coerente nel tempo di esecuzione.

Примеры

Diamo un'occhiata a un'implementazione di esempio utilizzando il database Questions come esempio.

C'è una richiesta SELEZIONA, che combina più tabelle e utilizza una vista (OperativeQuestions), in cui l'affiliazione viene verificata via email (via ESISTE) a “Domande operative”:

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: ha subquery join e utilizza l'ordinamento DISTINCT, che in generale è un'operazione piuttosto dispendiosa in termini di risorse.

Un campione di OperativeQuestions è composto da circa diecimila record.

Il problema principale con questa query è che per i record della query esterna, viene eseguita una sottoquery interna nella vista [OperativeQuestions], che dovrebbe per [Email] = @p__linq__0 permetterci di limitare la selezione dell'output (tramite ESISTE) fino a centinaia di record.

E può sembrare che la sottoquery debba calcolare i record una volta per [Email] = @p__linq__0, quindi queste due centinaia di record dovrebbero essere collegati tramite ID con Domande e la query sarà veloce.

Esiste infatti un collegamento sequenziale di tutte le tabelle: verificando la corrispondenza delle Id Questions con gli Id di OperativeQuestions, e filtraggio per Email.

La richiesta, infatti, funziona con tutte le decine di migliaia di record OperativeQuestions, ma servono solo i dati di interesse via Email.

DomandeOperative visualizza il testo:

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

Mapping della visualizzazione 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");
    }
}

Query 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 questo caso particolare, stiamo valutando una soluzione a questo problema senza modifiche infrastrutturali, senza introdurre una tabella separata con risultati già pronti (“Query attive”), che richiederebbe un meccanismo per riempirla di dati e mantenerla aggiornata .

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

Lo scopo principale è memorizzare nella cache le voci di [Email] = @p__linq__0 dalla vista OperativeQuestions.

Introdurre la funzione di tabella [dbo].[OperativeQuestionsUserMail] nel database.

Inviando Email come parametro di input, 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

Ciò restituisce una tabella di valori con una struttura dati predefinita.

Affinché le query a OperativeQuestionsUserMail siano ottimali e abbiano piani di query ottimali, è necessaria una struttura rigorosa e non TABELLA RESI COME RESO...

In questo caso, la Query 1 richiesta viene convertita nella Query 4:

Richiesta 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 visualizzazioni e 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 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();

L'ordine del tempo di esecuzione è sceso da 200-800 ms a 2-20 ms, ecc., ovvero decine di volte più veloce.

Se lo prendiamo più in media, invece di 350 ms otteniamo 8 ms.

Dagli evidenti vantaggi otteniamo anche:

riduzione generale del carico di lettura,
significativa riduzione della probabilità di blocco
riducendo il tempo medio di blocco a valori accettabili

conclusione

Ottimizzazione e messa a punto delle chiamate al database MS SQL attraverso LINQ è un problema che può essere risolto.

Attenzione e coerenza sono molto importanti in questo lavoro.

All'inizio del processo:

è necessario verificare i dati con cui funziona la richiesta (valori, tipi di dati selezionati)
effettuare una corretta indicizzazione di questi dati
verificare la correttezza delle condizioni di unione tra le tabelle

La successiva iterazione di ottimizzazione rivela:

base della richiesta e definisce il filtro principale della richiesta
ripetere blocchi di query simili e analizzare l'intersezione delle condizioni
in SSMS o altra GUI per SQL Server ottimizza se stesso Interrogazione SQL (assegnando un archivio dati intermedio, costruendo la query risultante utilizzando questo archivio (potrebbero essercene diversi))
nell'ultima fase, prendendo come base il risultato Interrogazione SQL, la struttura è in fase di ricostruzione Interrogazione LINQ

Il risultato Domanda LINQ dovrebbe diventare identico nella struttura all'ottimo identificato Interrogazione SQL dal punto 3.

Ringraziamenti

Molte grazie ai colleghi jobgemws и alex_ozr dalla società Fortis per assistenza nella preparazione di questo materiale.

Fonte: habr.com

Metodi per ottimizzare le query LINQ in C#.NET