SQL scatenato: 17 modi per velocizzare le tue query SQL

Gli sviluppatori SQL su ogni piattaforma stanno lottando, apparentemente bloccati in un DO WHILEciclo che li fa ripetere gli stessi errori ancora e ancora. Questo perché il campo del database è ancora relativamente immaturo. Certo, i fornitori stanno facendo dei passi avanti, ma continuano ad affrontare i problemi più grandi. La concorrenza, la gestione delle risorse, la gestione dello spazio e la velocità continuano a tormentare gli sviluppatori SQL, indipendentemente dal fatto che codifichino su SQL Server, Oracle, DB2, Sybase, MySQL o qualsiasi altra piattaforma relazionale.

Parte del problema è che non esiste una bacchetta magica e per quasi tutte le migliori pratiche posso mostrarti almeno un'eccezione. In genere, uno sviluppatore trova i propri metodi preferiti, sebbene di solito non includono alcun costrutto per prestazioni o concorrenza, e non si preoccupa di esplorare altre opzioni. Forse è un sintomo di mancanza di istruzione o gli sviluppatori sono troppo vicini al processo per riconoscere quando stanno facendo qualcosa di sbagliato. Forse la query viene eseguita bene su un set locale di dati di test ma fallisce miseramente sul sistema di produzione.

Non mi aspetto che gli sviluppatori SQL diventino amministratori, ma devono tenere conto dei problemi di produzione quando scrivono il loro codice. Se non lo fanno durante lo sviluppo iniziale, gli amministratori di database li faranno tornare indietro e lo faranno in un secondo momento e nel frattempo gli utenti soffriranno.

C'è un motivo per cui diciamo che la messa a punto di un database è sia un'arte che una scienza. È perché esistono pochissime regole rigide che si applicano su tutta la linea. I problemi che hai risolto su un sistema non sono problemi su un altro e viceversa. Non c'è una risposta giusta quando si tratta di ottimizzare le query, ma ciò non significa che dovresti arrenderti.

Ci sono alcuni buoni principi che puoi seguire che dovrebbero produrre risultati in una combinazione o nell'altra. Li ho incapsulati in un elenco di cose da fare e da non fare SQL che spesso vengono trascurate o sono difficili da individuare. Queste tecniche dovrebbero darti una visione un po 'più approfondita delle menti dei tuoi amministratori di database, nonché la capacità di iniziare a pensare ai processi in modo orientato alla produzione.

1. Non utilizzare al UPDATEposto diCASE

Questo problema è molto comune e, sebbene non sia difficile da individuare, molti sviluppatori spesso lo trascurano perché l'utilizzo UPDATEha un flusso naturale che sembra logico.

Prendi questo scenario, ad esempio: stai inserendo dati in una tabella temporanea e ne hai bisogno per visualizzare un determinato valore se esiste un altro valore. Forse stai estraendo dalla tabella Clienti e vuoi che chiunque abbia più di $ 100.000 in ordini venga etichettato come "Preferito". Pertanto, inserisci i dati nella tabella ed esegui UPDATEun'istruzione per impostare la colonna CustomerRank su "Preferred" per chiunque abbia più di $ 100.000 in ordini. Il problema è che l' UPDATEistruzione viene registrata, il che significa che deve scrivere due volte per ogni singola scrittura sulla tabella. Il modo per aggirare questo, ovviamente, è usare un'istruzione inline CASEnella stessa query SQL. Questo verifica ogni riga per la condizione dell'importo dell'ordine e imposta l'etichetta "Preferred" prima che venga scritta nella tabella. L'aumento delle prestazioni può essere sbalorditivo.

2. Non riutilizzare ciecamente il codice

Anche questo problema è molto comune. È molto facile copiare il codice di qualcun altro perché sai che estrae i dati di cui hai bisogno. Il problema è che molto spesso estrae molti più dati del necessario e gli sviluppatori raramente si preoccupano di tagliarli, quindi finiscono con un enorme superset di dati. Questo di solito si presenta sotto forma di un'unione esterna aggiuntiva o una condizione aggiuntiva nella WHEREclausola. Puoi ottenere enormi guadagni in termini di prestazioni se riduci il codice riutilizzato alle tue esatte esigenze.

3. Esegui il pull solo del numero di colonne di cui hai bisogno

Questo problema è simile al numero 2, ma è specifico per le colonne. È fin troppo facile codificare tutte le tue query con SELECT *invece di elencare le colonne singolarmente. Il problema di nuovo è che estrae più dati del necessario. Ho visto questo errore dozzine e dozzine di volte. Uno sviluppatore esegue una SELECT *query su una tabella con 120 colonne e milioni di righe, ma finisce per usarne solo da tre a cinque. A quel punto, stai elaborando così tanti più dati di quanti ne hai bisogno, è un vero miracolo che la query restituisca. Non stai solo elaborando più dati del necessario, ma stai anche sottraendo risorse ad altri processi.

4. Non immergere due volte

Eccone un altro che ho visto più volte di quanto avrei dovuto: una stored procedure viene scritta per estrarre i dati da una tabella con centinaia di milioni di righe. Lo sviluppatore ha bisogno di clienti che vivono in California e hanno un reddito superiore a $ 40.000. Quindi interroga i clienti che vivono in California e inserisce i risultati in una tabella temporanea; quindi interroga i clienti con un reddito superiore a $ 40.000 e inserisce i risultati in un'altra tabella temporanea. Infine, si unisce a entrambe le tabelle per ottenere il prodotto finale.

Ma stai scherzando? Questo dovrebbe essere fatto in una singola query; invece, stai immergendo due volte un tavolo superlarge. Non essere un deficiente: interroga tabelle di grandi dimensioni solo una volta ogni volta che è possibile: scoprirai quanto sono migliori le tue procedure.

Uno scenario leggermente diverso è quando un sottoinsieme di una tabella di grandi dimensioni è necessario per diversi passaggi in un processo, il che fa sì che la tabella di grandi dimensioni venga interrogata ogni volta. Evita ciò eseguendo una query per il sottoinsieme e conservandolo altrove, quindi indirizzando i passaggi successivi al tuo set di dati più piccolo.

6. Eseguire i dati preliminari

Questo è uno dei miei argomenti preferiti perché è una vecchia tecnica che spesso viene trascurata. Se hai un report o una procedura (o meglio ancora, un insieme di essi) che eseguirà unioni simili a tabelle di grandi dimensioni, può essere un vantaggio per te pre-organizzare i dati unendo le tabelle in anticipo e conservandole in un tavolo. Ora i report possono essere eseguiti su quella tabella preinstallata ed evitare il grande join.

Non sei sempre in grado di utilizzare questa tecnica, ma quando puoi, scoprirai che è un modo eccellente per risparmiare risorse del server.

Si noti che molti sviluppatori aggirano questo problema di join concentrandosi sulla query stessa e creando una sola visualizzazione attorno al join in modo da non dover digitare le condizioni di join ancora e ancora. Ma il problema con questo approccio è che la query viene ancora eseguita per ogni report che ne ha bisogno. Pre-staging dei dati, si esegue il join solo una volta (diciamo, 10 minuti prima dei rapporti) e tutti gli altri evitano il big join. Non posso dirti quanto amo questa tecnica; nella maggior parte degli ambienti, ci sono tabelle popolari che vengono sempre unite, quindi non c'è motivo per cui non possano essere pre-organizzate.

7. Elimina e aggiorna in batch

Ecco un'altra tecnica facile che viene molto trascurata. Eliminare o aggiornare grandi quantità di dati da tabelle enormi può essere un incubo se non lo fai correttamente. Il problema è che entrambe queste istruzioni vengono eseguite come una singola transazione e, se è necessario ucciderle o se accade qualcosa al sistema mentre stanno funzionando, il sistema deve ripristinare l'intera transazione. Questo può richiedere molto tempo. Queste operazioni possono anche bloccare altre transazioni per la loro durata, essenzialmente bloccando il sistema.

La soluzione è eliminare o aggiornare in batch più piccoli. Questo risolve il tuo problema in un paio di modi. Innanzitutto, se la transazione viene interrotta per qualsiasi motivo, ha solo un numero limitato di righe da ripristinare, quindi il database torna online molto più rapidamente. In secondo luogo, mentre i batch più piccoli sono impegnati su disco, altri possono intrufolarsi e svolgere un po 'di lavoro, quindi la concorrenza è notevolmente migliorata.

In questo senso, molti sviluppatori hanno in testa che queste operazioni di eliminazione e aggiornamento devono essere completate lo stesso giorno. Non è sempre vero, soprattutto se stai archiviando. Puoi allungare l'operazione per tutto il tempo necessario e i lotti più piccoli ti aiutano a farlo. Se puoi impiegare più tempo per eseguire queste operazioni intensive, dedica del tempo extra e non arrestare il sistema.

8. Utilizzare le tabelle temporanee per migliorare le prestazioni del cursore

Spero che ormai sappiamo tutti che è meglio stare alla larga dai cursori, se possibile. I cursori non solo soffrono di problemi di velocità, che di per sé possono essere un problema con molte operazioni, ma possono anche causare il blocco di altre operazioni per molto più tempo del necessario. Ciò riduce notevolmente la concorrenza nel sistema.

Tuttavia, non è sempre possibile evitare di utilizzare i cursori e, quando si verificano quei momenti, potresti essere in grado di allontanarti dai problemi di prestazioni indotti dal cursore eseguendo invece le operazioni del cursore su una tabella temporanea. Prendi, ad esempio, un cursore che passa attraverso una tabella e aggiorna un paio di colonne in base ad alcuni risultati di confronto. Invece di fare il confronto con la tabella live, potresti essere in grado di mettere quei dati in una tabella temporanea e fare invece il confronto con quella. Quindi hai una singola UPDATEdichiarazione contro il tavolo dal vivo che è molto più piccola e mantiene i blocchi solo per un breve periodo.

Lo sniping delle modifiche ai dati in questo modo può aumentare notevolmente la concorrenza. Concludo dicendo che non è quasi mai necessario utilizzare un cursore. C'è quasi sempre una soluzione basata su set; devi imparare a vederlo.

9. Non annidare le visualizzazioni

Le visualizzazioni possono essere convenienti, ma devi fare attenzione quando le usi. Sebbene le visualizzazioni possano aiutare a nascondere query di grandi dimensioni da parte degli utenti e a standardizzare l'accesso ai dati, puoi facilmente trovarti in una situazione in cui hai visualizzazioni che chiamano visualizzazioni che chiamano visualizzazioni che chiamano visualizzazioni. Questa operazione è denominata visualizzazioni di nidificazione e può causare gravi problemi di prestazioni, in particolare in due modi:

  • In primo luogo, molto probabilmente torneranno molti più dati del necessario.
  • In secondo luogo, Query Optimizer si arrenderà e restituirà un piano di query non valido.

Una volta ho avuto un cliente che amava le viste annidate. Il cliente aveva una vista che utilizzava per quasi tutto perché aveva due importanti join. Il problema era che la vista restituiva una colonna con 2 MB di documenti al suo interno. Alcuni dei documenti erano ancora più grandi. Il client spingeva almeno 2 MB in più sulla rete per ogni singola riga in quasi ogni singola query eseguita. Naturalmente, le prestazioni delle query erano pessime.

E nessuna delle query ha effettivamente utilizzato quella colonna! Naturalmente, la colonna era sepolta a sette punti di vista, quindi anche trovarla era difficile. Quando ho rimosso la colonna del documento dalla visualizzazione, il tempo per la query più grande è passato da 2,5 ore a 10 minuti. Quando finalmente ho svelato le viste annidate, che avevano diversi join e colonne non necessari, e ho scritto una semplice query, il tempo per quella stessa query è sceso a secondi.