Maar om diverse redenen moet de query nu op een kopie van een live database worden uitgevoerd (een zogenaamde snapshot database) waarin data van veel meer bedrijven zit. Met als gevolg dat het effect van een slecht geschreven query gelijk merkbaar werd!
De query
De query had ruwweg de volgende structuur:
selecteer een heleboel kolommen van ( selecteer een aantal kolommen van tabelY , ( selecteer de som van veld1 van TabelX waar het rekeningnummer = rekeningnummer van tabel Y en de datum in een bepaalde range ligt) , ( selecteer de som van veld2 van TabelX waar het rekeningnummer = rekeningnummer van tabel Y en de datum in een bepaalde range ligt) , ( selecteer de som van veld1 van TabelZ waar het rekeningnummer = rekeningnummer van tabel Y en de datum in een bepaalde range ligt) , ( selecteer de som van veld2 van TabelZ waar het rekeningnummer = rekeningnummer van tabel Y en de datum in een bepaalde range ligt) van tabel Y join wat tabellen wat restricties op de data ) groepeer sorteer
Het probleem zit in de 4 subqueries. Om deze te elimineren heb ik:
- de 4 subqueries vervangen door 2 common table expressions (er werd alleen gekeken naar TabelX en TabelZ)
- de subqueries vervangen door outer joins
CTE1 = (selecteer de som van veld1 van tabelX waarbij de datum in een range ligt) ,CTE2 = (selecteer de som van veld1 van tabelZ waarbij de datum in een range ligt) ,CTE3 = ( selecteer een aantal kolommen van tabelY , selecteer de waarde uit CTE1 (alias1) , selecteer de waarde uit CTE1 (alias2) , selecteer de waarde uit CTE2 (alias3) , selecteer de waarde uit CTE2 (alias4) van tabel Y join wat tabellen left join CTE1 als alias1 left join CTE1 als alias2 left join CTE2 als alias3 left join CTE2 als alias4 wat restricties op de data ) selecteer een aantal kolommen van CTE3 groepeer sorteerDit gaf een ongelooflijke performance winst. De oude query gaf na 745 seconden 3506 regels terug, de nieuwe query deed er 6 seconden over. Toen ik drie maal had gecontroleerd dat de data gelijk was, werd het tijd om wat sales managers blij te maken. Weer een feel-good ervaring rijker.