Ottimizzazione delle prestazioni del database SQL per gli sviluppatori

L'ottimizzazione delle prestazioni SQL può essere un'attività incredibilmente difficile, in particolare quando si lavora con dati su larga scala in cui anche la più piccola modifica può avere un impatto drammatico (positivo o negativo) sulle prestazioni.

Nelle aziende di medie e grandi dimensioni, la maggior parte dell'ottimizzazione delle prestazioni SQL verrà gestita da un amministratore di database (DBA). Ma credetemi, ci sono molti sviluppatori là fuori che devono eseguire attività simili a DBA. Inoltre, in molte delle aziende che ho visto che hanno DBA, spesso hanno difficoltà a lavorare bene con gli sviluppatori: le posizioni richiedono semplicemente diverse modalità di risoluzione dei problemi, che possono portare a disaccordo tra i colleghi.

Inoltre, anche la struttura aziendale può svolgere un ruolo. Supponiamo che il team DBA si trovi al 10° piano con tutti i suoi database, mentre gli sviluppatori si trovano al 15° piano, o anche in un edificio diverso con una struttura di reporting completamente separata, è certamente difficile lavorare insieme senza intoppi in queste condizioni.

In questo articolo, vorrei realizzare due cose:

1. Fornire agli sviluppatori alcune tecniche di ottimizzazione delle prestazioni SQL lato sviluppatore.
2. Spiega come sviluppatori e DBA possono collaborare in modo efficace.

Ottimizzazione delle prestazioni SQL (nella codebase): indici

Se sei un principiante assoluto dei database e ti chiedi anche "Cos'è l'ottimizzazione delle prestazioni SQL?", dovresti sapere che l'indicizzazione è un modo efficace per ottimizzare il tuo database SQL che viene spesso trascurato durante lo sviluppo. In termini di base, un indice è una struttura di dati che migliora la velocità delle operazioni di recupero dei dati su una tabella di database fornendo rapide ricerche casuali e un accesso efficiente ai record ordinati. Ciò significa che una volta creato un indice, puoi selezionare o ordinare le righe più velocemente di prima.

Gli indici vengono utilizzati anche per definire una chiave primaria o un indice univoco che garantirà che nessun'altra colonna abbia gli stessi valori. Naturalmente, l'indicizzazione del database è un argomento vasto e interessante a cui non posso rendere giustizia con questa breve descrizione (ma ecco un articolo più dettagliato).

Se non conosci gli indici, ti consiglio di utilizzare questo diagramma per strutturare le tue query:

Fondamentalmente, l'obiettivo è indicizzare le principali colonne di ricerca e ordinamento.

Tieni presente che se le tue tabelle sono costantemente martellate da INSERT , UPDATE e DELETE , dovresti prestare attenzione durante l'indicizzazione: potresti finire per ridurre le prestazioni poiché tutti gli indici devono essere modificati dopo queste operazioni.

Inoltre, i DBA spesso eliminano i propri indici SQL prima di eseguire inserimenti batch di oltre un milione di righe per accelerare il processo di inserimento. Dopo aver inserito il batch, ricreano gli indici. Ricorda, tuttavia, che l'eliminazione degli indici influirà su ogni query in esecuzione in quella tabella; quindi questo approccio è consigliato solo quando si lavora con un singolo inserimento di grandi dimensioni.

Ottimizzazione SQL: piani di esecuzione in SQL Server

A proposito: lo strumento Piano di esecuzione in SQL Server può essere utile per creare indici.

La sua funzione principale è quella di visualizzare graficamente i metodi di recupero dati scelti da Query Optimizer di SQL Server. Se non li hai mai visti prima, c'è una procedura dettagliata.

Per recuperare il piano di esecuzione (in SQL Server Management Studio), fare clic su "Includi piano di esecuzione effettivo" (CTRL + M) prima di eseguire la query.

Successivamente, apparirà una terza scheda denominata "Piano di esecuzione". Potresti vedere un indice mancante rilevato. Per crearlo, fai clic con il pulsante destro del mouse nel piano di esecuzione e scegli "Dettagli indice mancante...". E 'così semplice!

( Clicca per ingrandire )

Ottimizzazione SQL: evita i cicli di codifica

Immagina uno scenario in cui 1000 query martellano il tuo database in sequenza. Qualcosa di simile a:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Dovresti evitare tali loop nel tuo codice. Ad esempio, potremmo trasformare lo snippet sopra utilizzando un'istruzione INSERT o UPDATE univoca con più righe e valori:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Assicurati che la tua clausola WHERE eviti di aggiornare il valore memorizzato se corrisponde al valore esistente. Un'ottimizzazione così banale può aumentare notevolmente le prestazioni delle query SQL aggiornando solo centinaia di righe anziché migliaia. Per esempio:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

Ottimizzazione SQL: evitare sottoquery SQL correlate

Una sottoquery correlata è quella che utilizza i valori della query padre. Questo tipo di query SQL tende a essere eseguita riga per riga, una volta per ogni riga restituita dalla query esterna, e quindi riduce le prestazioni della query SQL. I nuovi sviluppatori SQL vengono spesso sorpresi a strutturare le loro query in questo modo, perché di solito è la strada più facile.

Ecco un esempio di una sottoquery correlata:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

In particolare, il problema è che la query interna ( SELECT CompanyName… ) viene eseguita per ogni riga restituita dalla query esterna ( SELECT c.Name… ). Ma perché esaminare la Company ancora e ancora per ogni riga elaborata dalla query esterna?

Una tecnica di ottimizzazione delle prestazioni SQL più efficiente sarebbe il refactoring della sottoquery correlata come join:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

In questo caso, all'inizio, andiamo sopra la tabella Company una sola volta e ABBIAMOCI con la tabella Customer . Da quel momento in poi, possiamo selezionare i valori di cui abbiamo bisogno ( co.CompanyName ) in modo più efficiente.

Ottimizzazione SQL: selezionare Con moderazione

Uno dei miei suggerimenti per l'ottimizzazione SQL preferiti è evitare SELECT * ! Invece, dovresti includere individualmente le colonne specifiche di cui hai bisogno. Ancora una volta, sembra semplice, ma vedo questo errore dappertutto. Considera una tabella con centinaia di colonne e milioni di righe: se la tua applicazione ha davvero bisogno solo di poche colonne, non ha senso eseguire query per tutti i dati. È un enorme spreco di risorse. ( Per ulteriori problemi, vedere qui. )

Per esempio:

 SELECT * FROM Employees

contro

 SELECT FirstName, City, Country FROM Employees

Se hai davvero bisogno di ogni colonna, elenca esplicitamente ogni colonna. Questa non è tanto una regola, quanto piuttosto un mezzo per prevenire futuri errori di sistema e un'ulteriore ottimizzazione delle prestazioni SQL. Ad esempio, se stai utilizzando INSERT... SELECT... e la tabella di origine è cambiata tramite l'aggiunta di una nuova colonna, potresti riscontrare problemi, anche se quella colonna non è necessaria per la tabella di destinazione, per esempio:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Per evitare questo tipo di errore da SQL Server, è necessario dichiarare ciascuna colonna singolarmente:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Si noti, tuttavia, che ci sono alcune situazioni in cui l'uso di SELECT * potrebbe essere appropriato. Ad esempio, con le tabelle temporanee, che ci porta al nostro prossimo argomento.

Ottimizzazione SQL: l'uso saggio delle tabelle temporanee (#Temp)

Le tabelle temporanee in genere aumentano la complessità di una query. Se il tuo codice può essere scritto in modo semplice e diretto, ti suggerisco di evitare le tabelle temporanee.

Ma se hai una procedura memorizzata con alcune manipolazioni dei dati che non possono essere gestite con una singola query, puoi utilizzare le tabelle temporanee come intermediari per aiutarti a generare un risultato finale.

Quando devi unirti a una tabella di grandi dimensioni e ci sono condizioni su detta tabella, puoi aumentare le prestazioni del database trasferendo i tuoi dati in una tabella temporanea e quindi creando un join su quella . La tua tabella temporanea avrà meno righe rispetto alla tabella originale (grande), quindi il join finirà più velocemente!

La decisione non è sempre semplice, ma questo esempio ti darà un'idea delle situazioni in cui potresti voler utilizzare le tabelle temporanee:

Immagina una tabella cliente con milioni di record. Devi creare un join su una regione specifica. Puoi ottenere ciò utilizzando un'istruzione SELECT INTO e quindi unendoti alla tabella temporanea:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Nota: alcuni sviluppatori SQL evitano anche di utilizzare SELECT INTO per creare tabelle temporanee, dicendo che questo comando blocca il database tempdb, impedendo ad altri utenti di creare tabelle temporanee. Fortunatamente, questo è stato risolto in 7.0 e versioni successive .)

In alternativa alle tabelle temporanee, potresti prendere in considerazione l'utilizzo di una sottoquery come tabella:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Ma aspetta! C'è un problema con questa seconda query. Come descritto sopra, dovremmo includere solo le colonne di cui abbiamo bisogno nella nostra sottoquery (cioè, non usando SELECT * ). Tenendo conto di ciò:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Tutti questi frammenti SQL restituiranno gli stessi dati. Ma con le tabelle temporanee, potremmo, ad esempio, creare un indice nella tabella temporanea per migliorare le prestazioni. C'è una buona discussione qui sulle differenze tra tabelle temporanee e sottoquery.

Infine, quando hai finito con la tua tabella temporanea, eliminala per cancellare le risorse tempdb, piuttosto che attendere che venga eliminata automaticamente (come accadrà quando la connessione al database verrà interrotta):

 DROP TABLE #temp

Ottimizzazione SQL: "Il mio record esiste?"

Questa tecnica di ottimizzazione SQL riguarda l'uso di EXISTS() . Se vuoi verificare se esiste un record, usa EXISTS() invece di COUNT() . Mentre COUNT() esegue la scansione dell'intera tabella, contando tutte le voci che corrispondono alla tua condizione, EXISTS() uscirà non appena vedrà il risultato di cui ha bisogno. Questo ti darà prestazioni migliori e un codice più chiaro.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

contro

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

Ottimizzazione delle prestazioni SQL con SQL Server 2016

Come probabilmente sanno i DBA che lavorano con SQL Server 2016, la versione ha segnato un cambiamento importante nelle impostazioni predefinite e nella gestione della compatibilità. Come versione principale, ovviamente, include nuove ottimizzazioni delle query, ma il controllo sull'eventuale utilizzo è ora semplificato tramite sys.databases.compatibility_level .

Ottimizzazione delle prestazioni SQL (in Office)

Gli amministratori di database SQL (DBA) e gli sviluppatori spesso si scontrano su problemi relativi ai dati e non relativi ai dati. Tratti dalla mia esperienza, ecco alcuni suggerimenti (per entrambe le parti) su come andare d'accordo e lavorare insieme in modo efficace.

Twitta

Ottimizzazione del database per gli sviluppatori:

1. Se la tua applicazione smette di funzionare improvvisamente, potrebbe non essere un problema di database. Ad esempio, forse hai un problema di rete. Indaga un po' prima di accusare un DBA!

2. Anche se sei un modellatore di dati SQL ninja, chiedi a un DBA di aiutarti con il tuo diagramma relazionale. Hanno molto da condividere e da offrire.

3. Ai DBA non piacciono i cambiamenti rapidi. Questo è naturale: devono analizzare il database nel suo insieme ed esaminare l'impatto di eventuali modifiche da tutte le angolazioni. Un semplice cambiamento in una colonna può richiedere una settimana per essere implementato, ma questo perché un errore potrebbe concretizzarsi come enormi perdite per l'azienda. Essere pazientare!

4. Non chiedere ai DBA SQL di apportare modifiche ai dati in un ambiente di produzione. Se vuoi accedere al database di produzione, devi essere responsabile di tutte le tue modifiche.

Ottimizzazione del database per i DBA di SQL Server:

1. Se non ti piacciono le persone che ti chiedono informazioni sul database, fornisci loro un pannello di stato in tempo reale. Gli sviluppatori sono sempre sospettosi dello stato di un database e un tale pannello potrebbe far risparmiare tempo ed energia a tutti.

2. Aiuta gli sviluppatori in un ambiente di test/garanzia di qualità. Semplifica la simulazione di un server di produzione con semplici test su dati reali. Questo sarà un notevole risparmio di tempo per gli altri e per te stesso.

3. Gli sviluppatori trascorrono tutto il giorno su sistemi con logiche di business modificate di frequente. Cerca di capire che questo mondo è più flessibile e di essere in grado di infrangere alcune regole in un momento critico.

4. I database SQL si evolvono. Verrà il giorno in cui dovrai migrare i tuoi dati a una nuova versione. Gli sviluppatori contano su nuove funzionalità significative con ogni nuova versione. Invece di rifiutarti di accettare le loro modifiche, pianifica in anticipo e preparati per la migrazione.