Supercharge Your SQL Queries for Production Databases (Italiano)

Artem Yevtushenko | Builder Education |7 min read |April 24, 2019

SQL è uno degli strumenti di gestione dei dati più potenti in circolazione. In SQL Superstar, ti diamo consigli praticabili per aiutarti a ottenere il massimo da questo linguaggio versatile e creare query belle ed efficaci.

Se non avete un data warehouse o un database analitico separato per il reporting, il database di produzione è probabilmente l’unica fonte di dati aggiornati. Quando si interroga un database di produzione, l’ottimizzazione è la chiave. Una query inefficiente prosciuga le risorse del database di produzione e causa prestazioni lente o perdita di servizio per gli altri utenti se la query contiene errori. È vitale ottimizzare le query per un impatto minimo sulle prestazioni del database.

Definire prima i requisiti di business

Abbiamo trattato altrove le migliori pratiche per definire i requisiti di business per la BI. Assicuratevi sicuramente di applicare queste pratiche quando ottimizzate le query SQL, tra cui:

• Identificare le parti interessate. Assicuratevi che tutte le parti necessarie siano nella discussione per sviluppare la vostra query. Quando si interrogano i database di produzione, assicurarsi che il team DBA sia incluso.
• Concentrarsi sui risultati di business. Date alla query uno scopo definito e unico. Tassare il database di produzione per report esplorativi o duplicativi è un rischio inutile.
• Inquadra la discussione per requisiti ottimali. Definite la funzione e lo scopo del report identificando il pubblico a cui è destinato. Questo focalizzerà l’interrogazione sulle tabelle con il giusto livello di dettaglio.
• Fare ottime domande. Seguite le 5 W: Chi? Cosa? Dove? Quando? Perché?
• Scrivere requisiti molto specifici e confermarli con le parti interessate. La performance del database di produzione è troppo critica per avere requisiti poco chiari o ambigui. Assicuratevi che i requisiti siano il più specifici possibile e confermateli con tutte le parti interessate prima di eseguire la query.

Selezionate i campi invece di usare SELECT *

Quando si eseguono query esplorative, molti sviluppatori SQL usano SELECT * (letto come “select all”) come abbreviazione per interrogare tutti i dati disponibili da una tabella. Tuttavia, se una tabella ha molti campi e molte righe, questo tassa le risorse del database interrogando molti dati non necessari.

Utilizzando l’istruzione SELECT si indirizza il database ad interrogare solo i dati necessari per soddisfare i requisiti di business. Ecco un esempio in cui i requisiti di business richiedono indirizzi postali per i clienti.

Non efficiente:

SELECT *
FROM Customers

Questa query può estrarre anche altri dati memorizzati nella tabella clienti, come numeri di telefono, date di attività e note dalle vendite e dal servizio clienti.

Efficiente:

SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers

Questa query è molto più pulita e tira solo le informazioni necessarie per gli indirizzi postali.

Per mantenere un indice di tutte le tabelle e i nomi dei campi, esegui una query da una tabella di sistema come INFORMATION_SCHEMA o ALL_TAB_COLUMNS (per MS SQL Server, leggi questo).

Inizia oggi con SQL:

Evitare SELECT DISTINCT

SELECT DISTINCT è un modo pratico per rimuovere i duplicati da una query. SELECT DISTINCT funziona raggruppando tutti i campi della query per creare risultati distinti. Per realizzare questo obiettivo, tuttavia, è necessaria una grande quantità di potenza di elaborazione. Inoltre, i dati possono essere raggruppati al punto di essere imprecisi. Per evitare di usare SELECT DISTINCT, seleziona più campi per creare risultati unici.

Non efficiente e impreciso:

SELECT DISTINCT FirstName, LastName, State
FROM Customers

Questa query non tiene conto di più persone nello stesso stato che hanno lo stesso nome e cognome. Nomi popolari come David Smith o Diane Johnson saranno raggruppati insieme, causando un numero impreciso di record. Nei database più grandi, un gran numero di David Smith e Diane Johnsons farà sì che questa query venga eseguita lentamente.

Efficiente e precisa:

SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers

Aggiungendo più campi, sono stati restituiti record non duplicati senza usare SELECT DISTINCT. Il database non deve raggruppare alcun campo e il numero di record è accurato.

Vedi Sisense in azione:

Esplora Dashboard

Crea le join con INNER JOIN (non WHERE)

Alcuni sviluppatori SQL preferiscono fare join con clausole WHERE, come la seguente:

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID

Questo tipo di join crea un join cartesiano, chiamato anche prodotto cartesiano o CROSS JOIN.

In un join cartesiano, vengono create tutte le possibili combinazioni delle variabili. In questo esempio, se avessimo 1.000 clienti con 1.000 vendite totali, la query genererebbe prima 1.000.000 di risultati, poi filtrerebbe per i 1.000 record dove CustomerID è correttamente unito. Questo è un uso inefficiente delle risorse del database, poiché il database ha fatto 100 volte più lavoro del necessario. Le Cartesian Join sono particolarmente problematiche nei database su larga scala, perché una Cartesian Join di due grandi tabelle potrebbe creare miliardi o trilioni di risultati.

Per evitare di creare una Cartesian Join, usate invece una INNER JOIN:

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID

Il database genererebbe solo i 1.000 record desiderati dove CustomerID è uguale.

Alcuni sistemi DBMS sono in grado di riconoscere le WHERE join e di eseguirle automaticamente come INNER JOIN. In quei sistemi DBMS, non ci sarà differenza di prestazioni tra una WHERE join e una INNER JOIN. Tuttavia, la INNER JOIN è riconosciuta da tutti i sistemi DBMS. Il tuo DBA ti consiglierà quale sia il migliore nel tuo ambiente.

Usa WHERE invece di HAVING per definire i filtri

L’obiettivo di una query efficiente è di estrarre solo i record richiesti dal database. Secondo l’ordine delle operazioni SQL, le dichiarazioni HAVING sono calcolate dopo le dichiarazioni WHERE. Se l’intento è quello di filtrare una query basata su condizioni, una dichiarazione WHERE è più efficiente.

Per esempio, assumiamo che siano state fatte 200 vendite nell’anno 2016, e vogliamo interrogare il numero di vendite per cliente nel 2016.

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.CustomerID, Customers.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#

Questa query estrarrebbe 1.000 record di vendite dalla tabella Sales, poi filtrerebbe per i 200 record generati nell’anno 2016, e infine conterebbe i record nel dataset.

In confronto, le clausole WHERE limitano il numero di record estratti:

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name

Questa query estrarrebbe i 200 record dall’anno 2016, e poi conterebbe i record nel dataset. Il primo passo della clausola HAVING è stato completamente eliminato.

HAVING dovrebbe essere usato solo quando si filtra su un campo aggregato. Nella query di cui sopra, potremmo anche filtrare i clienti con più di 5 vendite usando una dichiarazione HAVING.

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
HAVING Count(Sales.SalesID) > 5

>>Free Starter Kit: Inizia oggi con il nostro starter kit SQL gratuito

Usa i caratteri jolly solo alla fine di una frase

Quando si cercano dati in chiaro, come città o nomi, i caratteri jolly creano la ricerca più ampia possibile. Tuttavia, la ricerca più ampia è anche la ricerca più inefficiente.

Quando viene usato un carattere jolly iniziale, specialmente in combinazione con un carattere jolly finale, il database ha il compito di cercare tutti i record per una corrispondenza in qualsiasi punto del campo selezionato.

Considera questa query per estrarre le città che iniziano con ‘Char’:

SELECT City FROM Customers
WHERE City LIKE ‘%Char%’

Questa query estrarrà i risultati attesi di Charleston, Charlotte e Charlton. Tuttavia, otterrà anche risultati inaspettati, come Cape Charles, Crab Orchard e Richardson.

Una query più efficiente sarebbe:

SELECT City FROM Customers
WHERE City LIKE ‘Char%’

Questa query otterrà solo i risultati previsti di Charleston, Charlotte e Charlton.

Utilizzare LIMIT per campionare i risultati della query

Prima di eseguire una query per la prima volta, assicurati che i risultati siano desiderabili e significativi usando una dichiarazione LIMIT. (In alcuni sistemi DBMS, la parola TOP è usata in modo intercambiabile con LIMIT.) L’istruzione LIMIT restituisce solo il numero di record specificato. L’uso di un’istruzione LIMIT evita di tassare il database di produzione con una grande query, solo per scoprire che la query ha bisogno di modifiche o perfezionamenti.

Nella query sulle vendite del 2016 di cui sopra, esamineremo un limite di 10 record:

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
LIMIT 10

Possiamo vedere dal campione se abbiamo un set di dati utilizzabile o meno.

Esegui la tua query durante le ore non di punta

Per minimizzare l’impatto delle tue query analitiche sul database di produzione, parla con un DBA per programmare l’esecuzione della query in un orario non di punta. La query dovrebbe essere eseguita quando gli utenti concorrenti sono al loro numero più basso, che è tipicamente il centro della notte (3 – 5 del mattino).

Più dei seguenti criteri ha la tua query, più è probabile che venga eseguita di notte:

• Selezione da grandi tabelle (>1.000,000 record)
• Cartesian Joins o CROSS JOINs
• Esami di looping
• Esami SELECT DISTINCT
• Sottoquery annidate
• Ricerche con caratteri jolly in testi lunghi o campi memo campi
• Ricerche su schemi multipli

Cerca con fiducia

Con questi consigli in mente (più alcuni altri suggerimenti e trucchi SQL in tasca) si dovrebbe essere in grado di costruire efficiente, che funzioneranno senza problemi e restituiranno le intuizioni che cambieranno il gioco di cui il tuo team ha bisogno.

Ottieni lo starter kit SQL gratuito qui: