1 Warszawska Wyższa Szkoła Informatyki Andrzej Kuska Promotor : dr inż. Paweł Figat Konsultant : mgr inż. Andrzej Ptasznik

2 Problematyka optymalizacji zapytań Znalezienie optymalnego rozwiązania wykonania zapytania poprzez analizę i dekompozycję zapytania. Wprowadzenie narzędzi, funkcji wspomagających optymalizację zapytań w MS SQL Udoskonalenie funkcji przetwarzania w oknie w MS SQL 2012 Czynniki mające wpływ na czas i koszt wykonania zapytań.

3 Cel i zakres pracy Stworzenie środowiska badawczego Stworzenie bazy danych Stworzenie zapytań wykorzystujących funkcję okna Stworzenie zapytań klasycznych Wykonanie badania Pomiar czasów realizacji dwóch typów zapytań Analiza oraz wnioski z przeprowadzonego badania

4 Hipoteza badawcza Zapytania wykorzystujące funkcje przetwarzania w oknie są bardziej wydajne niż zapytania napisane metodą tradycyjną Zapytania wykorzystujące OVER są potencjalnym rozszerzeniem klasycznej metody tworzenia zapytań.

5 Środowisko badawcze CzynnikWartość CPU 8 rdzeni, 3GHz RAM16 GB Silnik bazy danychMicrosoft SQL Server 2012 Standard Szybkość odczytu danych z dysku Producent dysku podaje średni czas dostępu do danych w wielkości 8,5ms. Całkowita ilość danych do pobrania Dane generowane są przy każdym etapie badania. Ostatecznie w bazie szacowanych jest ok 10.000.000 danych. Ilość ludzi aktualnie pobierających dane Jedna osoba Kryteria pobierania danychOpisane zostały w rozdziale 3.2 oraz 3.3 Rozmiar zbioru wynikowego Rozmiar zbiorów wynikowych jest zależny od stworzonych zapytań. Będzie się zwiększał w miarę przyrostu danych do bazy. Liczba tabelW bazie istnieje 6 tabel biorących udział w badaniu

6 Baza Danych Badanie

7 Zapytania z OVER oraz zapytania klasyczne Badanie SELECT ROW_NUMBER() OVER (PARTITION BY tw.towar_nazwa ORDER BY zl.kwota_dostawy) AS rown, tw.towar_nazwa, zl.kwota_dostawy, AVG(zl.kwota_dostawy) OVER(PARTITION BY tw.towar_nazwa ORDER BY zl.kwota_dostawy) FROM dbo.tbl_zlecenie zl INNER JOIN dbo.dct_towary tw ON tw.id_towar = zl.id_towar SELECT tw.towar_nazwa,r1.rownumber, r1.kwota_dostawy, AVG(r1.kwota_dostawy) AS srednia FROM ( SELECT *, ( SELECT COUNT(*) FROM dbo.tbl_zlecenie AS counter WHERE counter.id_towar = tbl_zlecenie.id_towar AND counter.kwota_dostawy

8 Stworzenie bazy danych Stworzenie pięciu grup zapytań Zapytania klasyczne Zapytania z OVER Badanie

9 Zapytania z OVER oraz zapytania klasyczne Badanie SELECT zl.id_towar,tw.towar_nazwa,tr.nr_pojazdu,tr.data_zaladunku,zl.id_transport, RANK() OVER ( ORDER BY zl.id_transport desc) AS rank FROM tbl_zlecenie zl INNER JOIN dbo.tbl_transport tr ON tr.id_transport = zl.id_transport INNER JOIN dbo.dct_towary tw ON tw.id_towar = zl.id_towar SELECT zl.id_towar,tw.towar_nazwa,tr.nr_pojazdu,tr.data_zaladunku, zl.id_transport,(SELECT COUNT(zl2.id_transport)+1 FROM dbo.tbl_zlecenie zl2 WHERE zl.id_transport

10 Metoda badań Pięć grup zapytań Każda grupa to zapytanie używające funkcji okna oraz realizujące ten sam problem zapytanie klasyczne Przeprowadzenie pięciu próbek zapytań celem uśrednienia czasów w jednej grupie Przyrost danych po każdym etapie badania Etap I – początkowy zestaw danych – 1000 rekordów w we wszystkich tabelach Etap II: tabele słownikowe - 10,000 rekordów ; tabela główna 100,000 rekordów Etap III: tabele słownikowe - 10,000 rekordów ; tabela główna 1,000,000 rekordów Etap IV: tabele słownikowe - 10,000 rekordów ; tabela główna 5,000,000 rekordów Etap V: tabele słownikowe - 10,000 rekordów ; tabela główna 10,000,000 rekordów

11 Metoda badań Stworzenie zapytania mierzącego czas wykonania DECLARE @coldRunStart Datetime2, @coldRunFinish Datetime2 CHECKPOINT DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS DBCC FREEPROCCACHE WITH NO_INFOMSGS SET @coldRunStart = SYSDATETIME() SET STATISTICS IO ON SET STATISTICS TIME ON ------------------------------------------------- --- zapytanie do sprawdzenia ------------------------------------------------- SET STATISTICS TIME OFF SET STATISTICS IO OFF SET @coldRunFinish = SYSDATETIME() SELECT DATEDIFF(MILLISECOND, @coldRunStart, @coldRunFinish) AS czasWykonania

12 Pomiary oraz analiza Grupa I Lp.Zapytanie klasyczneZapytanie OVER 1125 ms109 ms 2140 ms124 ms 3140 ms104 ms 4125 ms109 ms 5172 ms124 ms AVG : 140,4 msAVG : 114 ms Fragment planu wykonania zapytania klasycznego – opracowanie własne

13 Pomiary oraz analiza Grupa II Lp.Zapytanie klasyczneZapytanie OVER 1755 ms1263 ms 21425 ms515 ms 31326 ms312 ms 41280 ms1108 ms 51280 ms515 ms AVG : 1213 msAVG : 742 ms

14 Pomiary oraz analiza Grupa II - Koszt wykonania Fragment planu wykonania zapytania OVER – opracowanie własne

15 Pomiary oraz analiza Grupa III Lp.Zapytanie klasyczneZapytanie OVER 125 853 ms17 146 ms 223 669 ms16 619 ms 323 536 ms16 980 ms 423 116 ms16 569 ms 523 753 ms16 178 ms AVG : 23 985 msAVG : 16 698 ms Fragment planu wykonania zapytania klasycznego – opracowanie własne

16 Grupa IV Lp.Zapytanie klasyczneZapytanie OVER 1963 215 ms166 327 ms 2973 251 ms166 854 ms 3968 125 ms156 121 ms 4982 151 ms178 521 ms 5978 945 ms156 353 ms AVG : 973 137 msAVG : 165 207 ms Fragment planu wykonania zapytania klasycznego – opracowanie własne Pomiary oraz analiza

17 Grupa V Lp.Zapytanie klasyczneZapytanie OVER 1 1 321 512 ms512 652 ms 2 1 243 565 ms501 213 ms 3 1 313 251 ms492 132 ms 4 1 253 554 ms503 255 ms 5 1 483 556 ms489 513 ms AVG : 1 323 087 msAVG : 499 753 ms Pomiary oraz analiza

18 Analiza całościowa wyników Pomiary oraz analiza

19 Wnioski Im wyższy przyrost danych tym dłuższy czas wykonania zapytania. Zapytania wykorzystujące OVER są bardziej wydajne niż zapytania pisane metodą klasyczną Im mniejszy czas i koszt wykonania zapytania tym większa wydajność bazy danych Różnica kosztowa zapytań z OVER w stosunku do zapytań klasycznych Przeprowadzone badanie ma charakter perspektywiczny

20 Warszawska Wyższa Szkoła Informatyki Andrzej Kuska