Optymalizacja zapytań skorelowanych

Na początek, krótkie wyjaśnienie czym jest zapytanie skorelowane, a prawidłowo ujmując - podzapytanie skorelowane. Z Wikipedii możemy odczytać: "zapytanie skorelowane, to takie zapytanie, które w podzapytaniu korzysta w klauzurze WHERE z danych z zapytania zewnętrznego. Zapytanie skorelowane jest wykonywane dla każdego wiersza przetwarzanego przez zapytanie zewnętrzne."

Druga część definicji daje nam obraz tego, iż stosowanie podzapytań skorelowanych dla dużych zbiorów danych jest nieefektowne i prowadzi do obniżenia wydajności całego systemu.
Co więc należy zrobić? Unikać zapytań skorelowanych a w przypadku już istniejących - przerabiać je w miarę możliwości na zapytania nieskorelowane.

Przykład na żywym organizmie:

Przykładowa baza danych - AdventureWorks2012 - jest ogólnie dostępna do pobrania ze stron Codeplex.
Treść zapytania które będziemy analizowali:
Z tabeli produktów wybrać te, których cena jest większa od średniej ceny produktów tego samego rozmiaru:

SELECT p.ProductID, p.Name, p.StandardCost
FROM Production.Product p
WHERE p.StandardCost > (SELECT AVG(StandardCost) FROM Production.Product WHERE Size = p.Size)


Takie zapytanie dla każdego wiersza zapytania zewnętrznego, policzy najpierw średnią cenę produktu o danym rozmiarze a następnie ją porówna z zewnętrznym wierszem.

Powyższe zapytanie można przepisać do następującej postaci:

SELECT p.ProductID, p.Name, p.StandardCost
FROM Production.Product p JOIN 
 (SELECT size, AVG(StandardCost) avgStandardCostPerSize
 FROM Production.Product
 GROUP BY Size) p2 
ON p2.Size = p.Size 
WHERE p.StandardCost > p2.avgStandardCostPerSize

Zapytanie wydłużyło się, jednak jest rozbite na dwa etapy. Najpierw zostaną zgrupowane produkty o tym samym rozmiarze i policzona ich średnia cena, a następnie z tym wynikiem zostanie połączona tabela produktów.

Nie każde zapytanie da się optymalizować. Niektóre będą wymagały większej ilości czasu poświęconego na ich analizę. Zysk jednak zostanie odczuty wraz z kolejnymi tysiącami wierszy w tabelach.