Strojenie baz danych - optymalizacja składni zapytań SQL c.d.
kotu - 14 maj, 2009
Kategorie:
Unikanie „zasobożernych” zapytań
Pisząc procedur, funkcje czy też zwykłe zapytania powinniśmy brać pod uwagę zapotrzebowanie na zasoby systemowe stosowanych konstrukcji. W kilku kolejnych podpunktach omówię przykładowe techniki ograniczające zasobożerność konstrukcji języka T-SQL.
3.1 Unikanie konwersji danych
Problemy z wydajnością podczas konwersji danych przedstawię na dodatkowo stworzonej tabeli People:
CREATE TABLE [dbo].[people](
[Id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE Latin1_General_CS_AS NULL,
[lastname] [varchar](50) COLLATE Latin1_General_CS_AS NULL,
[uniqueIdString] [varchar](50) COLLATE Latin1_General_CS_AS NULL
) ON [PRIMARY]
Zakładamy na niej 2 indeksy:
CREATE UNIQUE CLUSTERED INDEX PK_ID ON dbo.people ([Id] ASC) ;
CREATE UNIQUE NONCLUSTERED INDEX INDEX_UNQ ON dbo.people (uniqueIdString) ;
Tabele wypełniamy danymi użytkowników z tabeli Person.Contact naszej testowej bazy AdventureWorks
SELECT TOP 10000 FirstName, LastName
INTO #temp
FROM Person.Contact
INSERT INTO dbo.people (name, lastname, uniqueIdString)
SELECT CAST(FirstName AS VARCHAR), CAST(LastName AS VARCHAR), LEFT(NEWID(),8)
FROM #temp ;
DROP TABLE #temp;
Teraz możemy sprawdzić wydajność poniższych zapytań, w drugim z nich wymuszamy użycie kodowania UNICODE. Oznacza to, że wartość będzie typu NCHAR, NVARCHAR lub NTEXT (kolumna „uniquesIdString” w naszej tabeli jest typu VARCHAR stąd też dodatkowo wykonana musi zostać konwersja)
SELECT lastname
FROM dbo.people
WHERE uniqueIdString = '594C7855';
Table 'people'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 4 ms.
SELECT lastname
FROM dbo.people
WHERE uniqueIdString = N'594C7855';
Table 'people'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 7 ms.
W testowanym przypadku co prawda nie widać ogromnego spadku wydajności jednakże w zależności od rodzaju konwersji, typu zapytania i ilości danych spadek wydajności danej konstrukcji języka T-SQL może być bardziej dotkliwy. Pamiętajmy więc, że dana wartość konwertowana jest automatycznie do typu kolumny której dotyczy, co może powodować dodatkowe obciążenia dla serwera bazy danych.
3.2 Stosowanie EXISTS zamiast COUNT(*)
Często spotykamy się z potrzebą sprawdzenia czy danych zbiór rezultatów istnieje. Wydawałby się, że zastosowanie instrukcji COUNT(*) będzie dobrym rozwiązaniem, np. (tabela z poprzedniego podpunktu):
DECLARE @ile INT
SELECT @ile = COUNT(*)
FROM dbo.people AS p
WHERE p.Id >= 11238
IF @ile > 0 PRINT 'Istnieją osoby o id >= 11238'
GO
Table 'people'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 15 ms.
Okazuje się jednak, że w tym wypadku wyraźnie lepszym pomysłem będzie zastosowanie instrukcji EXISTS:
IF EXISTS (
SELECT p.*
FROM dbo.people AS p
WHERE p.Id >= 11238
)PRINT 'Istnieją osoby o id >= 11238'
Table 'people'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.
Użycie instrukcji EXISTS wymaga zaledwie 4 logicznych odczytów w porównaniu do 30 w instrukcji COUNT(*), czas wykonania jest ok. 3-krotnie mniejszy.
3.3 Stosowanie UNION ALL zamiast UNION
Instrukcji UNION możemy użyć w celu połączenia wyników działania kilku instrukcji SELECT. Instrukcja ta przetwarza ostateczny zbiór usuwając z niego wszystkie duplikaty (jak DISTINCT dla pojedynczego zapytania). Prosty wniosek jaki nasuwa się na myśl jest taki, że jeżeli jesteśmy pewni co do rozłączności zbiorów lub jeżeli dopuszczamy możliwość wystąpienia duplikatów nie powinniśmy jej używać J. Wydajniejsza w takim przypadku będzie instrukcja UNION ALL ponieważ nie musi ona wykrywać i usuwać duplikatów.
SELECT p.*
FROM dbo.people AS p
WHERE p.Id LIKE '21%'
UNION
SELECT p.*
FROM dbo.people AS p
WHERE p.Id LIKE '2%'
Table 'people'. Scan count 2, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 47 ms, elapsed time = 1105 ms.
SELECT p.*
FROM dbo.people AS p
WHERE p.Id LIKE '21%'
UNION ALL
SELECT p.*
FROM dbo.people AS p
WHERE p.Id LIKE '2%'
Table 'people'. Scan count 2, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 31 ms, elapsed time = 776 ms.
3.4 Używanie indeksów dla funkcji agregujących i sortowania
Funkcje agregujące jak (np. MIN, MAX, COUNT, GROUP BY) oraz sortowanie (ORDER BY) działając wyraźnie wydajniej gdy na danej kolumnie założony jest indeks, bez niego wykonany musi zostać pełny odczyt tabeli (lub użyty zostanie inny, mniej wydajny indeks). Przykładowo:
SELECT MAX(s.Rate) AS rate
FROM HumanResources.EmployeePayHistory AS s
Table 'EmployeePayHistory'. Scan count 1, logical reads 232, physical reads 1, read-ahead reads 230, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 99 ms.
Po założeniu indeksu na kolumnie „Rate” wartość maksymalna będzie sprawdzone szybciej (wartości są posortowane w indeksie):
CREATE NONCLUSTERED INDEX INDEX_RATE ON HumanResources.EmployeePayHistory (Rate) ;
Table 'EmployeePayHistory'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 95, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 21 ms.
4. Optymalizacja złożonego zapytania
W tym podpunkcie szczegółowo opiszę optymalizację istrukcji UPDATE z zagnieżdżonymi podzapytaniami. Omawiany przykład bazuje na jednym z artykułów opublikowanym w serwisie sqlservercentrel.com.
Zapytanie powoduje ukatulanienie danych w kolumnie SalesYTD poprzez dodanie do aktualnej wartości sumy wartości SubTotal z tabeli SalesOrderHeader dla danego sprzedawcy i konkretnego („najpóźniejszego” - MAX(OrderDate)) dnia.
UPDATE Sales.MySalesPerson
SET SalesYTD = SalesYTD +
(SELECT SUM(soh.SubTotal)
FROM Sales.SalesOrderHeader AS soh
WHERE soh.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader AS sohh
WHERE sohh.SalesPersonID =
soh.SalesPersonID)
AND Sales.MySalesPerson.SalesPersonID = soh.SalesPersonID
GROUP BY soh.SalesPersonID);
Oto struktura mojej tabeli MySalesPerson:
CREATE TABLE [Sales].[MySalesPerson](
[SalesPersonID] [int] NOT NULL,
[TerritoryID] [int] NULL,
[SalesQuota] [money] NULL,
[Bonus] [money] NOT NULL,
[CommissionPct] [smallmoney] NOT NULL,
[SalesYTD] [money] NULL,
[SalesLastYear] [money] NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MySalesPerson] PRIMARY KEY CLUSTERED
(
[SalesPersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX AK_rowuid_unique ON [Sales].[MySalesPerson] (rowguid);
Oto plan wykonania zapytania:
c.d.
Table 'MySalesPerson'. Scan count 1, logical reads 316, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 64548, logical reads 399368, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 2, logical reads 4898, physical reads 3, read-ahead reads 2445, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1359 ms, elapsed time = 1908 ms.
Po pierwsze należałoby opisać „podobszary” i zastanowić się w jakie sposób możnaby zoptymalizować kod:
UPDATE Sales.MySalesPerson
SET SalesYTD = SalesYTD +
-- dla „najpóźniejszej” daty MAX(OrderDate) sumuje wartosci SubTotal konkretnego sprzedawcy
(SELECT SUM(soh.SubTotal)
FROM Sales.SalesOrderHeader AS soh
WHERE soh.OrderDate = (
-- znajduje najpóźniejszą datę dla kazdego sprzedawcy
SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader AS sohh
WHERE sohh.SalesPersonID =
soh.SalesPersonID)
AND Sales.MySalesPerson.SalesPersonID = soh.SalesPersonID
GROUP BY soh.SalesPersonID);
1 krok
Zaczynając od środka możemy zauważyć, że podczas wyliczania najpóźniejszej daty dla każdego sprzedawcy w warunku WHERE porównujemy wartość z inną tabelą co na pewno jest operacją kosztowną. Skupiając się na naszym założeniu czyli „znajduje najpóźniejszą datę dla każdego sprzedawcy” moglibyśmy zapisać ten fragment jako tabelę pochodną z potrzebnymi kolumnami na liście SELECT.
SELECT MAX(OrderDate) AS MaxSalePersonDate, SalesPersonID
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
) MaxDate;
2 krok
W kolejnym kroku pozbywamy się kolejnego WHERE’a i stosując instrukcję INNER JOIN łączymy po kolumnie SalesPersonID z tabeli pochodnej. Ponownie tworzymy z tego tabelę pochodną i w tym momecie mamy już ID sprzedawcy oraz sumą sprzedaży w odpowiednim dniu.
(SELECT soh.SalesPersonID, SUM(soh.SubTotal) AS SubTotalsSum
FROM Sales.SalesOrderHeader soh
INNER JOIN
(SELECT MAX(OrderDate) AS MaxDate, SalesPersonID
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
) MaxDate_PersonID
ON soh.SalesPersonID = MaxDate_PersonID.SalesPersonID
AND soh.OrderDate = MaxDate_PersonID.MaxDate
GROUP BY soh.SalesPersonID
)SalesPersonStotal
3 krok
Teraz mamy już wszystko co jest potrzebne do aktualizacji kolumny SalesYTD w tabeli Sales.MySalesPerson. Również w tym przypadku możemy zastosować instrukcję INNER JOIN:
UPDATE Sales.MySalesPerson
SET SalesYTD = msp.SalesYTD + SalesPersonStotal.SubTotalsSum
FROM Sales.MySalesPerson msp
INNER JOIN
(SELECT soh.SalesPersonID, SUM(soh.SubTotal) AS SubTotalsSum
FROM Sales.SalesOrderHeader soh
INNER JOIN
(SELECT MAX(OrderDate) AS MaxDate, SalesPersonID
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
) MaxDate_PersonID
ON soh.SalesPersonID = MaxDate_PersonID.SalesPersonID
AND soh.OrderDate = MaxDate_PersonID.MaxDate
GROUP BY soh.SalesPersonID
)SalesPersonStotal
ON msp.SalesPersonID = SalesPersonStotal.SalesPersonID
Po dokonanych modyfikacjach wyraźnie widać, że ten sam efekt możemy uzyskamy znacznie niższym kosztem (szczególnie jeśli chodzi o czas procesora):
Table 'MySalesPerson'. Scan count 1, logical reads 316, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 2449, physical reads 3, read-ahead reads 2445, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 250 ms, elapsed time = 1087 ms.
4 krok
Na pewno warto zająć się indeksami na kolumnach we wszystkich funkcjach agregujących (MIN, MAX i GROUP BY). Po sprawdzeni istniejących indeksów oraz kilku próbach ostatecznie zdecydowałem się na dodanie jeszcze jedego:
CREATE NONCLUSTERED INDEX INDEX_SUM_MAX ON
Sales.SalesOrderHeader (SalesPersonID ASC, OrderDate DESC)
INCLUDE (SubTotal);
Dzięki przedstawionym powyżej zabiegom udało mi się uzyskać wynik wyraźnie lepszy od wyjściowego:
Table 'MySalesPerson'. Scan count 1, logical reads 316, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 2, logical reads 498, physical reads 1, read-ahead reads 247, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 218 ms, elapsed time = 338 ms.
Kategorie
Wyszukiwarka
Ankieta
Czy podoba Ci się ten serwis !?
Tak, jest super !!!
22% (16 głosów)
Fajny jest :)
39% (28 głosów)
Może być...
8% (6 głosów)
Ujdzie w tłumie...
10% (7 głosów)
Nie, jest beznadziejny :P
21% (15 głosów)
Razem głosów: 72
Komentarze
Podajesz ciekawe informacje. Gdyby udało ci się opisać optymalizację zapytań i wydajności w Drupalu to byłoby to Coś przez duże C :) Życzę Ci przede wszystkim wytrwałości w prowadzeniu bloga bo z tym chyba jest najtrudniej. Wiedzy Ci nie brakuje.
Dodaj nowy komentarz