[linux] Re: sql optimalisatie?

[Rob Hagman]

> Folkert van Heusden schreef op 23 januari 2007 21:26
> 
> Iemand nog 'n ideetje hoe ik deze query kan versnellen?
> 
> SELECT heatloss_ranges.descr, (SELECT (COUNT(*) / 45408) * 
> 100.0 FROM data WHERE (1.1622*(10.45 + 10*sqrt(windspeed) - 
> windspeed)*(33 - temp_out)) >= heatloss_ranges.min  AND (1.1622*(10.45 +
> 10*sqrt(windspeed) - windspeed)*(33 - temp_out)) < 
> heatloss_ranges.max) AS percentage FROM heatloss_ranges GROUP 
> BY heatloss_ranges.min ORDER BY percentage DESC;
> 

Hoe tijdkritisch zijn je Inserts/Updates ?
Je zou kunnen overwegen de berekening 
(1.1622*(10.45 + 10*sqrt(windspeed) -  windspeed)*(33 - temp_out)) 
uit de WHERE clause tijdens de INSERT /UPDATE uit te voeren
(evt dmv een trigger) en het resultaat in een extra kolom in de tabel 
data te zetten, zodat de SELECT deze berekeningen niet hoeft uit te voeren 
want in deze berekening gaat waarschijnlijk de meeste tijd in zitten 
bij het uitvoeren van deze query.

Je query wordt dan zoiets als:
SELECT heatloss_ranges.descr, (SELECT (COUNT(*) / 45408) * 
100.0 FROM data WHERE berekende_kolom >= heatloss_ranges.min  AND
(berekende_kolom < 
heatloss_ranges.max) AS percentage FROM heatloss_ranges GROUP 
BY heatloss_ranges.min ORDER BY percentage DESC;

Nog wat algemeenheden voor het versnellen van SELECT query's

- Vermijd berekeningen in SELECT query's
de meeste query engines zijn niet geoptimaliseerd voor het
uitvoeren van berekeningen, vaak is de UPDATE/INSERT minder tijdkritisch
en kunnen de berekeningen beter dan plaats vinden

- Vermijd JOINS (de-normaliseer het database schema)
Ik weet, dit gaat tegen de theorie in, maar normaliseren doe je om ruimte
in de database te besparen ten koste van de performance bij het uitvoeren 
van query's, ook hierbij geldt dat de SELECT sneller gemaakt wordt door de
INSERT/UPDATE te verlengen en de database groter te maken.

- Vermijd GROUP BY en ORDER BY (in dit geval zal dat wel niet mogelijk zijn)
vaak is een eigen sorteer algoritme in de favoriete programeer taal
efficiënter dat wat er in de query engine zit

Eigenlijk is het basis principe: je kunt de SELECT sneller maken door
de dingen die tijd kosten te verschuiven naar de INSERT

Rob Hagman.