Esecuzione efficiente di query

  • Articolo

L'esecuzione di query in modo efficiente è un argomento vasto, che copre gli argomenti come indici, strategie di caricamento di entità correlate e molte altre. Questa sezione descrive in dettaglio alcuni temi comuni per velocizzare le query e insidia gli utenti in genere riscontrano.

Usare correttamente gli indici

Il fattore principale che determina se una query viene eseguita rapidamente o meno è se utilizzerà correttamente gli indici dove appropriato: i database vengono in genere usati per contenere grandi quantità di dati e le query che attraversano intere tabelle sono in genere origini di gravi problemi di prestazioni. I problemi di indicizzazione non sono facili da individuare, perché non è immediatamente ovvio se una determinata query userà o meno un indice. Ad esempio:

// Matches on start, so uses an index (on SQL Server)
var posts1 = context.Posts.Where(p => p.Title.StartsWith("A")).ToList();
// Matches on end, so does not use the index
var posts2 = context.Posts.Where(p => p.Title.EndsWith("A")).ToList();

Un buon modo per individuare i problemi di indicizzazione consiste nell'individuare prima una query lenta e quindi esaminare il piano di query tramite lo strumento preferito del database; per altre informazioni su come eseguire questa operazione, vedere la pagina relativa alla diagnosi delle prestazioni . Il piano di query visualizza se la query attraversa l'intera tabella o usa un indice.

Come regola generale, non esiste alcuna conoscenza speciale di Entity Framework per l'uso di indici o la diagnosi dei problemi di prestazioni correlati; Le conoscenze generali del database correlate agli indici sono altrettanto rilevanti per le applicazioni EF che per le applicazioni che non usano Entity Framework. Di seguito sono elencate alcune linee guida generali da tenere presenti quando si usano gli indici:

  • Anche se gli indici velocizzano le query, rallentano anche gli aggiornamenti perché devono essere mantenuti aggiornati. Evitare di definire indici che non sono necessari e prendere in considerazione l'uso di filtri di indice per limitare l'indice a un subset delle righe, riducendo così questo sovraccarico.
  • Gli indici compositi possono velocizzare le query che filtrano su più colonne, ma possono anche velocizzare le query che non filtrano su tutte le colonne dell'indice, a seconda dell'ordinamento. Ad esempio, un indice sulle colonne A e B accelera i filtri delle query in base ad A e B, nonché le query che filtrano solo per A, ma non velocizza solo i filtri delle query rispetto a B.
  • Se una query filtra in base a un'espressione su una colonna ,ad esempio price / 2, non è possibile usare un indice semplice. Tuttavia, è possibile definire una colonna memorizzata in modo permanente per l'espressione e creare un indice su tale colonna. Alcuni database supportano anche gli indici delle espressioni, che possono essere usati direttamente per velocizzare i filtri delle query in base a qualsiasi espressione.
  • I diversi database consentono la configurazione degli indici in diversi modi e in molti casi i provider EF Core espongono questi tramite l'API Fluent. Ad esempio, il provider SQL Server consente di configurare se un indice è cluster o impostarne il fattore di riempimento. Per altre informazioni, vedere la documentazione del provider.

Project only properties you need

EF Core semplifica l'esecuzione di query su istanze di entità e quindi usa tali istanze nel codice. Tuttavia, l'esecuzione di query su istanze di entità può spesso eseguire il pull di più dati del necessario dal database. Tenere presente quanto segue:

foreach (var blog in context.Blogs)
{
    Console.WriteLine("Blog: " + blog.Url);
}

Anche se questo codice richiede solo la proprietà di Url ogni blog, l'intera entità blog viene recuperata e le colonne non necessarie vengono trasferite dal database:

SELECT [b].[BlogId], [b].[CreationDate], [b].[Name], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]

Questa operazione può essere ottimizzata usando Select per indicare a EF quali colonne proiettare:

foreach (var blogName in context.Blogs.Select(b => b.Url))
{
    Console.WriteLine("Blog: " + blogName);
}

Il codice SQL risultante esegue il pull indietro solo delle colonne necessarie:

SELECT [b].[Url]
FROM [Blogs] AS [b]

Se è necessario proiettare più colonne, proiettare in un tipo anonimo C# con le proprietà desiderate.

Si noti che questa tecnica è molto utile per le query di sola lettura, ma le cose diventano più complesse se è necessario aggiornare i blog recuperati, poiché il rilevamento delle modifiche di Entity Framework funziona solo con le istanze di entità. È possibile eseguire aggiornamenti senza caricare intere entità collegando un'istanza di blog modificata e comunicando a EF quali proprietà sono state modificate, ma è una tecnica più avanzata che potrebbe non valerne la pena.

Limitare le dimensioni del set di risultati

Per impostazione predefinita, una query restituisce tutte le righe corrispondenti ai relativi filtri:

var blogsAll = context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .ToList();

Poiché il numero di righe restituite dipende dai dati effettivi nel database, è impossibile sapere quanti dati verranno caricati dal database, la quantità di memoria che verrà caricata dai risultati e il carico aggiuntivo generato durante l'elaborazione di questi risultati, ad esempio inviandoli a un browser utente in rete. In modo cruciale, i database di test contengono spesso poco dati, in modo che tutto funzioni correttamente durante i test, ma i problemi di prestazioni vengono improvvisamente visualizzati quando la query viene avviata su dati reali e vengono restituite molte righe.

Di conseguenza, in genere vale la pena pensare di limitare il numero di risultati:

var blogs25 = context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .Take(25)
    .ToList();

Come minimo, l'interfaccia utente potrebbe mostrare un messaggio che indica che nel database potrebbero esistere più righe e consentire il recupero in un altro modo. Una soluzione completa implementa la paginazione, in cui l'interfaccia utente visualizza solo un certo numero di righe alla volta e consente agli utenti di passare alla pagina successiva in base alle esigenze. Per altre informazioni su come implementare questo in modo efficiente, vedere la sezione successiva.

Impaginazione efficiente

L'impaginazione si riferisce al recupero dei risultati nelle pagine, anziché contemporaneamente; questa operazione viene in genere eseguita per set di risultati di grandi dimensioni, in cui viene visualizzata un'interfaccia utente che consente all'utente di passare alla pagina successiva o precedente dei risultati. Un modo comune per implementare la impaginazione con i database consiste nell'usare gli Skip operatori e (OFFSET e TakeLIMIT in SQL). Anche se si tratta di un'implementazione intuitiva, è anche piuttosto inefficiente. Per la paginazione che consente lo spostamento di una pagina alla volta (anziché passare a pagine arbitrarie), è consigliabile usare invece l'impaginazione keyset.

Per altre informazioni, vedere la pagina della documentazione sulla paginazione.

Nei database relazionali tutte le entità correlate vengono caricate introducendo JOIN in una singola query.

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]

Se un blog tipico include più post correlati, le righe per questi post dupliceranno le informazioni del blog. Questa duplicazione porta alla cosiddetta "esplosione cartesiana". Man mano che vengono caricate più relazioni uno-a-molti, la quantità di dati duplicati può aumentare e influire negativamente sulle prestazioni dell'applicazione.

Ef consente di evitare questo effetto tramite l'uso di "query suddivise", che caricano le entità correlate tramite query separate. Per altre informazioni, vedere la documentazione sulle query singole e suddivise.

Nota

L'implementazione corrente delle query suddivise esegue un round trip per ogni query. Si prevede di migliorarlo in futuro ed eseguire tutte le query in un unico round trip.

Prima di continuare con questa sezione, è consigliabile leggere la pagina dedicata sulle entità correlate.

Quando si gestiscono entità correlate, in genere sappiamo in anticipo cosa è necessario caricare: un esempio tipico sta caricando un determinato set di blog, insieme a tutti i loro post. In questi scenari, è sempre meglio usare il caricamento eager, in modo che EF possa recuperare tutti i dati necessari in un round trip. La funzionalità di inclusione filtrata consente anche di limitare le entità correlate da caricare, mantenendo il processo di caricamento eager e quindi eseguibile in un singolo round trip:

using (var context = new BloggingContext())
{
    var filteredBlogs = context.Blogs
        .Include(
            blog => blog.Posts
                .Where(post => post.BlogId == 1)
                .OrderByDescending(post => post.Title)
                .Take(5))
        .ToList();
}

In altri scenari, è possibile che non si conosca quale entità correlata è necessaria prima di ottenere l'entità principale. Ad esempio, quando si carica un blog, potrebbe essere necessario consultare un'altra origine dati, possibilmente un servizio Web, per sapere se si è interessati ai post del blog. In questi casi, il caricamento esplicito o differita può essere usato per recuperare le entità correlate separatamente e popolare lo spostamento Post del blog. Si noti che poiché questi metodi non sono eager, richiedono ulteriori round trip al database, che è fonte di rallentamento; a seconda dello scenario specifico, può essere più efficiente caricare sempre tutti i post, piuttosto che eseguire i round trip aggiuntivi e ottenere selettivamente solo i post necessari.

Attenzione al caricamento differita

Il caricamento differita spesso sembra un modo molto utile per scrivere la logica del database, poiché EF Core carica automaticamente le entità correlate dal database quando sono accessibili dal codice. In questo modo si evita il caricamento di entità correlate non necessarie (ad esempio il caricamento esplicito) e apparentemente libera il programmatore dalla necessità di gestire completamente le entità correlate. Tuttavia, il caricamento differita è particolarmente soggetto alla produzione di round trip extra non necessario che possono rallentare l'applicazione.

Tenere presente quanto segue:

foreach (var blog in context.Blogs.ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Questo pezzo di codice apparentemente innocente scorre tutti i blog e i loro post, stampandoli. L'attivazione della registrazione delle istruzioni di EF Core rivela quanto segue:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [b].[BlogId], [b].[Rating], [b].[Url]
      FROM [Blogs] AS [b]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[@__p_0='1'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='2'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='3'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0

... and so on

Cosa avviene qui? Perché tutte queste query vengono inviate per i cicli semplici precedenti? Con il caricamento differita, i post di un blog vengono caricati solo (pigri) quando si accede alla relativa proprietà Post; di conseguenza, ogni iterazione nel foreach interno attiva una query di database aggiuntiva, nel proprio round trip. Di conseguenza, dopo il caricamento iniziale della query di tutti i blog, abbiamo un'altra query per blog, caricando tutti i post. Questo è talvolta detto problema N+1 e può causare problemi di prestazioni molto significativi.

Supponendo che tutti i post dei blog siano necessari, è opportuno usare il caricamento eager qui. È possibile usare l'operatore Include per eseguire il caricamento, ma poiché sono necessari solo gli URL dei blog (ed è necessario caricare solo gli ELEMENTI necessari). Verrà quindi usata una proiezione:

foreach (var blog in context.Blogs.Select(b => new { b.Url, b.Posts }).ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

In questo modo EF Core recupererà tutti i blog, insieme ai relativi post, in una singola query. In alcuni casi, può anche essere utile evitare effetti di esplosione cartesiani usando query suddivise.

Avviso

Poiché il caricamento differita rende estremamente facile attivare inavvertitamente il problema N+1, è consigliabile evitarlo. Il caricamento eager o esplicito lo rende molto chiaro nel codice sorgente quando si verifica un round trip del database.

Buffering e streaming

Il buffering si riferisce al caricamento di tutti i risultati della query in memoria, mentre lo streaming indica che EF passa ogni volta un singolo risultato all'applicazione, senza mai contenere l'intero set di risultati in memoria. In linea di principio, i requisiti di memoria di una query di streaming sono fissi: sono gli stessi se la query restituisce 1 riga o 1000; una query di buffering, d'altra parte, richiede più memoria, vengono restituite più righe. Per le query che generano set di risultati di grandi dimensioni, questo può essere un fattore importante per le prestazioni.

L'eventuale presenza di buffer o flussi di query dipende dalla modalità di valutazione:

// ToList and ToArray cause the entire resultset to be buffered:
var blogsList = context.Posts.Where(p => p.Title.StartsWith("A")).ToList();
var blogsArray = context.Posts.Where(p => p.Title.StartsWith("A")).ToArray();

// Foreach streams, processing one row at a time:
foreach (var blog in context.Posts.Where(p => p.Title.StartsWith("A")))
{
    // ...
}

// AsEnumerable also streams, allowing you to execute LINQ operators on the client-side:
var doubleFilteredBlogs = context.Posts
    .Where(p => p.Title.StartsWith("A")) // Translated to SQL and executed in the database
    .AsEnumerable()
    .Where(p => SomeDotNetMethod(p)); // Executed at the client on all database results

Se le query restituiscono solo alcuni risultati, probabilmente non è necessario preoccuparsi di questo. Tuttavia, se la query potrebbe restituire un numero elevato di righe, è consigliabile pensare allo streaming anziché al buffering.

Nota

Evitare di usare ToList o ToArray se si intende usare un altro operatore LINQ sul risultato. In questo modo tutti i risultati verranno memorizzati in memoria senza bisogno. Utilizzare invece AsEnumerable.

Buffering interno da ef

In determinate situazioni, Entity Framework memorizza internamente il set di risultati nel buffer, indipendentemente dalla modalità di valutazione della query. I due casi in cui si verificano questi casi sono:

  • Quando è in atto una strategia di ripetizione dell'esecuzione. Questa operazione viene eseguita per assicurarsi che vengano restituiti gli stessi risultati se la query viene ritentata in un secondo momento.
  • Quando viene usata la query divisa, i set di risultati di tutti gli elementi ma l'ultima query vengono memorizzati nel buffer, a meno che MARS (Multiple Active Result Sets) non sia abilitato in SQL Server. Ciò è dovuto al fatto che in genere è impossibile avere più set di risultati di query attivi contemporaneamente.

Si noti che questo buffer interno si verifica oltre a qualsiasi buffering causato tramite operatori LINQ. Ad esempio, se si usa ToList in una query e viene eseguita una strategia di ripetizione dell'esecuzione, il set di risultati viene caricato in memoria due volte: una volta internamente da Entity Framework e una volta da ToList.

Rilevamento, nessun rilevamento e risoluzione delle identità

Prima di continuare con questa sezione, è consigliabile leggere la pagina dedicata sul rilevamento e sulla mancata verifica .

Ef tiene traccia delle istanze di entità per impostazione predefinita, in modo che le modifiche apportate vengano rilevate e rese persistenti quando SaveChanges viene chiamato. Un altro effetto del rilevamento delle query è che EF rileva se un'istanza è già stata caricata per i dati e restituirà automaticamente tale istanza rilevata anziché restituirne una nuova. questa operazione è denominata risoluzione delle identità. Dal punto di vista delle prestazioni, il rilevamento delle modifiche indica quanto segue:

  • Ef gestisce internamente un dizionario di istanze rilevate. Quando vengono caricati nuovi dati, Entity Framework controlla il dizionario per verificare se un'istanza è già tracciata per la chiave dell'entità (risoluzione delle identità). La manutenzione e le ricerche del dizionario richiedono tempo durante il caricamento dei risultati della query.
  • Prima di distribuire un'istanza caricata all'applicazione, EF crea snapshot dell'istanza e mantiene lo snapshot internamente. Quando SaveChanges viene chiamato, l'istanza dell'applicazione viene confrontata con lo snapshot per individuare le modifiche da rendere persistenti. Lo snapshot occupa più memoria e il processo di snapshot richiede tempo; A volte è possibile specificare un comportamento di snapshot diverso, possibilmente più efficiente tramite gli strumenti di confronto dei valori o usare proxy di rilevamento delle modifiche per ignorare completamente il processo di snapshot (anche se questo include un proprio set di svantaggi).

Negli scenari di sola lettura in cui le modifiche non vengono salvate di nuovo nel database, è possibile evitare i sovraccarichi precedenti usando query senza rilevamento. Tuttavia, poiché le query senza rilevamento non eseguono la risoluzione delle identità, una riga di database a cui fa riferimento più righe caricate verrà materializzata come istanze diverse.

Per illustrare, si supponga di caricare un numero elevato di post dal database, nonché il blog a cui fa riferimento ogni post. Se 100 post fanno riferimento allo stesso blog, una query di rilevamento rileva questa situazione tramite la risoluzione delle identità e tutte le istanze di Post fanno riferimento alla stessa istanza di blog deduplicata. Una query senza rilevamento, al contrario, duplica lo stesso blog 100 volte e il codice dell'applicazione deve essere scritto di conseguenza.

Ecco i risultati di un benchmark che confronta il rilevamento e il comportamento senza rilevamento per una query che carica 10 blog con 20 post ciascuno. Il codice sorgente è disponibile qui, è possibile usarlo come base per le misurazioni personalizzate.

Method NumBlogs NumPostsPerBlog Valore medio Errore StdDev Mediana Ratio RatioSD Generazione 0 Gen1 Gen2 Allocato
AsTracking 10 20 1.414.7 27.20 45.44 noi 1.405.5 1.00 0,00 60.5469 13.6719 - 380,11 KB
AsNoTracking 10 20 993.3 24.04 65.40 966.2 0.71 0.05 37.1094 6.8359 - 232,89 KB

Infine, è possibile eseguire aggiornamenti senza sovraccarico del rilevamento delle modifiche, utilizzando una query senza rilevamento e quindi collegando l'istanza restituita al contesto, specificando quali modifiche devono essere apportate. Questo trasferisce il carico di lavoro del rilevamento delle modifiche da Entity Framework all'utente e deve essere tentato solo se il sovraccarico di rilevamento delle modifiche risulta inaccettabile tramite la profilatura o il benchmarking.

Uso di query SQL

In alcuni casi, per la query esiste un'istanza di SQL più ottimizzata, che EF non genera. Questo problema può verificarsi quando il costrutto SQL è un'estensione specifica del database non supportata o semplicemente perché EF non lo converte ancora. In questi casi, la scrittura manuale di SQL può offrire un notevole miglioramento delle prestazioni e EF supporta diversi modi per eseguire questa operazione.

  • Usare le query SQL direttamente nella query, ad esempio tramite FromSqlRaw. EF consente anche di comporre il codice SQL con query LINQ regolari, consentendo di esprimere solo una parte della query in SQL. Questa è una buona tecnica quando SQL deve essere usato solo in una singola query nella codebase.
  • Definire una funzione definita dall'utente e quindi chiamarla dalle query. Si noti che EF consente alle funzioni definite dall'utente di restituire set di risultati completi, noti come funzioni con valori di tabella (TVFS) e consente anche di eseguire il mapping di a DbSet una funzione, rendendolo simile a un'altra tabella.
  • Definire una vista di database ed eseguire query da essa nelle query. Si noti che, a differenza delle funzioni, le viste non possono accettare parametri.

Nota

SQL non elaborato deve essere in genere usato come ultima risorsa, dopo aver verificato che EF non possa generare il codice SQL desiderato e quando le prestazioni sono sufficientemente importanti per la query specificata per giustificarla. L'uso di SQL non elaborato comporta notevoli svantaggi di manutenzione.

Programmazione asincrona

Come regola generale, affinché l'applicazione sia scalabile, è importante usare sempre API asincrone anziché sincrone , ad esempio SaveChangesAsync anziché SaveChanges. Le API sincrone bloccano il thread per la durata dell'I/O del database, aumentando la necessità di thread e il numero di commutatori di contesto del thread che devono verificarsi.

Per altre informazioni, vedere la pagina relativa alla programmazione asincrona.

Avviso

Evitare di combinare codice sincrono e asincrono nella stessa applicazione: è molto facile attivare inavvertitamente problemi di fame del pool di thread.

Avviso

L'implementazione asincrona di Microsoft.Data.SqlClient presenta purtroppo alcuni problemi noti ,ad esempio #593, #601 e altri. Se si verificano problemi di prestazioni imprevisti, provare a usare l'esecuzione del comando di sincronizzazione, soprattutto quando si usano valori binari o di testo di grandi dimensioni.

Risorse aggiuntive

  • Per altri argomenti relativi all'esecuzione di query efficienti, vedere la pagina degli argomenti sulle prestazioni avanzate.
  • Per alcune procedure consigliate per confrontare i valori null, vedere la sezione relativa alle prestazioni della pagina della documentazione sul confronto dei valori Null.