Ci stavo pensando qualche giorno fa dopo l'ottimizzazione SQL. Penso che possiamo essere d'accordo sul fatto che SQL sia un "linguaggio dichiarativo" nella definizione di Wikipedia:
Programming paradigm that expresses the logic of computation without
describing its control flow
Se pensi a quante cose si fanno dietro le quinte (guardando le statistiche, decidendo se un indice è utile, andando per un join annidato, unito o hash, ecc .. etc ..) dobbiamo ammettere che diamo solo una logica di alto livello e il database si è occupato di tutta la logica del flusso di controllo di basso livello.
Anche in questo scenario, a volte l'ottimizzatore del database necessita di alcuni "suggerimenti" da parte dell'utente per ottenere i migliori risultati.
Un'altra definizione comune di linguaggio "dichiarativo" è (non riesco a trovare una fonte autorevole):
Programming paradigm that expresses the desired result of computation
without describing the steps to achieve it (also abbreviated with
"describe what, not how")
Se accettiamo questa definizione, riscontriamo i problemi descritti dall'OP.
Il primo problema è che SQL ci dà modi equivalenti per definire "lo stesso risultato". Probabilmente è un male necessario: più potere espressivo diamo a un linguaggio, più è probabile che abbia modi diversi di esprimere la stessa cosa.
Ad esempio, mi è stato chiesto una volta di ottimizzare questa query:
SELECT Distinct CT.cust_type, ct.cust_type_description
from customer c
INNER JOIN
Customer_type CT on c.cust_type=ct.cust_type;
Poiché i tipi erano molto inferiori al cliente e c'era un indice sulla cust_type sulla tabella clienti, ho ottenuto un notevole miglioramento riscrivendolo come:
SELECT CT.cust_type, ct.cust_type_description
from Customer_type CT
Where exists ( select 1 from customer c
Where c.cust_type=ct.cust_type);
In questo caso specifico, quando ho chiesto allo sviluppatore cosa volesse ottenere mi ha detto "Volevo tutti i tipi di clienti per i quali avevo almeno un cliente", che incidentalmente è esattamente il modo in cui può essere descritta la query di ottimizzazione.
Quindi, se trovassi una query equivalente e più efficiente, perché l'ottimizzatore non può fare lo stesso?
La mia ipotesi migliore è che si tratta di due motivi principali:
SQL esprime la logica:
dal momento che SQL esprime la logica di alto livello, vorremmo davvero che l'ottimizzatore "superasse" noi e la nostra logica? Vorrei dire con entusiasmo "sì" se non fosse stato per tutte le volte in cui dovevo forzare l'ottimizzatore a scegliere il percorso di esecuzione più efficiente.
Penso che l'idea potrebbe essere quella di permettere all'ottimizzatore di fare del suo meglio (anche rivedendo la nostra logica) ma darci un "meccanismo di suggerimento" per venire in soccorso quando qualcosa impazzisce (sarebbe come avere la ruota + i freni in un'auto autonoma).
Più scelte = più tempo
Anche il miglior ottimizzatore RDBMS non verifica TUTTI i possibili percorsi di esecuzione, poiché devono essere molto veloci: quanto sarebbe buono ottimizzare una query da 100ms a 10ms se ho bisogno di spendere ogni volta 100ms scegliendo il percorso migliore? E questo è con l'ottimizzatore che rispetta la nostra "logica di alto livello". Se dovrebbe anche testare tutte le query SQL equivalenti, il tempo di ottimizzazione potrebbe aumentare più volte.
Un altro buon esempio di riscrittura delle query che nessun RDBMS è in grado di fare è (da questo interessante post sul blog )
SELECT t1.id, t1.value, SUM(t2.value)
FROM mytable t1
JOIN mytable t2
ON t2.id <= t1.id
GROUP BY t1.id, t1.value;
di quello che può essere scritto come questo (sono richieste le funzioni analitiche)
SELECT id, value, SUM(t1.value) OVER (ORDER BY id)
FROM mytable