(Nie)Eleganckie zapytanie tablicowe., Prośba o pomoc w sformułowaniu zapytania… |
(Nie)Eleganckie zapytanie tablicowe., Prośba o pomoc w sformułowaniu zapytania… |
27.02.2014, 09:44:21
Post
#1
|
|
Grupa: Zarejestrowani Postów: 14 Pomógł: 0 Dołączył: 4.09.2013 Ostrzeżenie: (0%) |
Witam,
Mam problem w odpytaniu Bazy danych w sposób „elegancki”, już tłumaczę o co chodzi. Dane: Zadanie: Wyszukaj pracowników zarabiających najmniej na danym etacie. Rozwiązanie, którego bym oczekiwał i uważam za eleganckie powinno mieć postać: SELECT nazwisko FROM pracownicy WHERE ( Etat , Placa_pod ) IN (SELECT Etat, MIN(Placa_pod) FROM pracownicy GROUP BY etat); Niestety nie działa ono na MS SQL. Rozwiązanie, które działa ale jest wg. mnie gorsze (nieeleganckie) ma postać: SELECT dbo.Pracownicy.Nazwisko, dbo.Pracownicy.Imie FROM dbo.Pracownicy INNER JOIN (SELECT MIN(Placa_Pod) AS Min, Etat FROM dbo.Pracownicy AS Pracownicy_1 GROUP BY Etat) AS P_1 ON dbo.Pracownicy.Placa_Pod = P_1.Min AND dbo.Pracownicy.Etat = P_1.Etat Czy któryś z szanownych forumowiczów ma pomysł na rozwiązanie bez użycia JOIN ? Z góry dziękuję Morf. Witam ponownie, po nocnych przemyśleniach: SELECT Nazwisko, Imie FROM dbo.Pracownicy AS T1 WHERE (Placa_Pod = (SELECT MIN(Placa_Pod) AS Low FROM dbo.Pracownicy WHERE (Etat = T1.Etat))) Poziom elegancji 7 / 10 i dodatkowy punkcik za bark JOIN czyli 8 /10 :-) w zasadzie jest OK chyba, że... Pozdrawiam Morf |
|
|
3.03.2014, 10:40:03
Post
#2
|
|
Grupa: Moderatorzy Postów: 4 362 Pomógł: 714 Dołączył: 12.02.2009 Skąd: Jak się położę tak leżę :D |
Nie pamiętam czy pod MSSQL to działa, ale czy aby najprostsze:
nie załatwia sprawy? -------------------- Najpierw był manual... Jeśli tam nie zawarto słów mądrości to zapytaj wszechwiedzącego Google zadając mu własciwe pytania. A jeśli i on milczy to Twój problem nie istnieje :D
|
|
|
3.03.2014, 10:50:06
Post
#3
|
|
Grupa: Zarejestrowani Postów: 272 Pomógł: 28 Dołączył: 13.02.2011 Ostrzeżenie: (0%) |
Lekko przyofftopuję... Coś te dane wyglądają znajomo... Politechnika Opolska? ; )
-------------------- Nie obrażę się, jeśli za udzieloną pomoc odwdzięczysz się klikając w "Pomógł" :).
|
|
|
3.03.2014, 10:53:24
Post
#4
|
|
Grupa: Zarejestrowani Postów: 109 Pomógł: 25 Dołączył: 10.08.2009 Ostrzeżenie: (0%) |
Nie pamiętam czy pod MSSQL to działa, ale czy aby najprostsze:
nie załatwia sprawy? Ze względu na brak warunku w HAVING mam wątpliwości czy to zapytanie będzie działać gdziekolwiek, niemniej jednak nie ma szans żeby to poszło na MSSQL, ponieważ wszystkie wybierane dane muszą być albo w GROUP BY albo w funkcji agregującej. |
|
|
4.03.2014, 13:16:04
Post
#5
|
|
Grupa: Zarejestrowani Postów: 14 Pomógł: 0 Dołączył: 4.09.2013 Ostrzeżenie: (0%) |
|
|
|
4.03.2014, 15:14:03
Post
#6
|
|
Grupa: Zarejestrowani Postów: 282 Pomógł: 89 Dołączył: 12.04.2011 Ostrzeżenie: (0%) |
Po raz pierwszy słyszę by ktoś uznawał rozwiązanie z JOIN za mało eleganckie, a rozwiązanie z IN() za bardziej eleganckie. Bazy danych niezależnie od silnika mają metody optymalizacji JOINów tak wyśrubowane, że przy prawidłowym wykorzystaniu indeksów złączenia są najbardziej wydajnymi rozwiązaniami. W zasadzie podane "nieeleganckie rozwiązanie" jest klasyką jeśli chodzi o problemy typu szukanie minimum/maximum w grupach i działa poprawnie praktycznie we wszystkich dialektach SQL. Wprawdzie istnieją szybsze metody, ale są one dostosowane do konkretnego silnika, na przykład dla MSSQL byłoby to rozwiązanie oparte na CTE i CROSS APPLY. Ciekawi mnie czym kieruje się autor wątku oceniając elegancję, bo z pewnością nie jest to wydajność, a i czytelność też raczej nie, bo widząc GROUP BY w podzapytaniu i JOIN do całej tabeli by pobrać resztę kolumn większość ludzi z jakimś doświadczeniem w SQL jest w stanie natychmiast stwierdzić co dokładnie takie zapytanie ma robić.
|
|
|
12.03.2014, 09:16:34
Post
#7
|
|
Grupa: Zarejestrowani Postów: 8 Pomógł: 1 Dołączył: 12.03.2014 Ostrzeżenie: (0%) |
Nie ma co gdybać co jest bardzie a co mniej wydajne - najlepiej to po porstu sprawdzić.
Zarówno zaproponowane przez kolegę podzapytanie skorelowane :
Jak i dwa inne rozwiązania (moim zdaniem całkiem eleganckie ) dadzą takie same EFEKTY jeśli chodzi o wydajność. W końcu CROSS APPLY to nic innego jak zapytanie skorelowane.... a CTE ? hmmm w tym przypadku chyba tylko żeby poprawić czytelność. Warto poczytać porównania wydajnościowe, dla podzapytań skorelowanych na www.sqlpedia.pl - również dot. CTE, grupowania czy joinów.
Każda z tych kwerend da identyczny plan wykonania. BTW - warto dodać jeszcze indeks np. taki :
Który dramatycznie poprawi wydajność pozrdrowienia ! Ten post edytował juris 12.03.2014, 09:17:16 |
|
|
13.03.2014, 16:41:32
Post
#8
|
|
Grupa: Zarejestrowani Postów: 14 Pomógł: 0 Dołączył: 4.09.2013 Ostrzeżenie: (0%) |
Po raz pierwszy słyszę by ktoś uznawał rozwiązanie z JOIN za mało eleganckie, a rozwiązanie z IN() za bardziej eleganckie. Bazy danych niezależnie od silnika mają metody optymalizacji JOINów tak wyśrubowane, że przy prawidłowym wykorzystaniu indeksów złączenia są najbardziej wydajnymi rozwiązaniami. W zasadzie podane "nieeleganckie rozwiązanie" jest klasyką jeśli chodzi o problemy typu szukanie minimum/maximum w grupach i działa poprawnie praktycznie we wszystkich dialektach SQL. Wprawdzie istnieją szybsze metody, ale są one dostosowane do konkretnego silnika, na przykład dla MSSQL byłoby to rozwiązanie oparte na CTE i CROSS APPLY. Ciekawi mnie czym kieruje się autor wątku oceniając elegancję, bo z pewnością nie jest to wydajność, a i czytelność też raczej nie, bo widząc GROUP BY w podzapytaniu i JOIN do całej tabeli by pobrać resztę kolumn większość ludzi z jakimś doświadczeniem w SQL jest w stanie natychmiast stwierdzić co dokładnie takie zapytanie ma robić. Witam ponownie, Udało mi się przetestować oba rozwiązania: Zapytanie: SELECT Imię, Nazwisko, Zespół, Płaca FROM Testowa_1 AS T1 WHERE Płaca = (SELECT MAX(Płaca) FROM Testowa_1 AS T2 WHERE Zespół = T1.Zespół) Jest o około 10 % szybsze od zapytania: SELECT T1.Imię, T1.Nazwisko, T1.Zespół, T1.Płaca FROM dbo.Testowa_1 AS T1 INNER JOIN (SELECT Zespół, MAX(Płaca) AS Exp1 FROM dbo.Testowa_1 GROUP BY Zespół) AS T2 ON T1.Zespół = T2.Zespół AND T1.Płaca = T2.Exp1; Testowałem na MS SQL 2008 R2 Tak więc kolega pmir13 chyba nie ma jednak do końca racji... Jedno zwraca wynik po około 78s a drugie 86s (średnia 10 odpytań). Pozdrawiam Morf |
|
|
14.03.2014, 09:12:05
Post
#9
|
|
Grupa: Zarejestrowani Postów: 8 Pomógł: 1 Dołączył: 12.03.2014 Ostrzeżenie: (0%) |
Masz jakieś indeksy na tej tabeli? Możesz podrzucić plany zapytań ?
Moim zdaniem te dwa rozwiązania (bez indeksów) powinny być identyczne pod względem wydajności. |
|
|
15.03.2014, 08:34:13
Post
#10
|
|
Grupa: Zarejestrowani Postów: 14 Pomógł: 0 Dołączył: 4.09.2013 Ostrzeżenie: (0%) |
Masz jakieś indeksy na tej tabeli? Możesz podrzucić plany zapytań ? Moim zdaniem te dwa rozwiązania (bez indeksów) powinny być identyczne pod względem wydajności. Witam, Kolega ma rację Plany są identyczne, z czego wynika różnica nie mam bladego pojęcia? Ja w SQL raczkuję... istotne dla Mnie są informacje, które dane niosą a nie sposób w jaki jest to realizowane. Załączam kolejny test wydajnościowy popierający moje spostrzeżenie. Tym razem szybsza maszyna i tylko 10000 krotek. Plany Prozdrawiam Morf Ten post edytował Morfina 15.03.2014, 08:35:55 |
|
|
15.03.2014, 14:09:16
Post
#11
|
|
Grupa: Zarejestrowani Postów: 1 421 Pomógł: 310 Dołączył: 18.04.2012 Ostrzeżenie: (0%) |
Różnica w czasach może wynikać ze statystyk i cache. Skoro optymliztor założył, że dla bazy są to jednakowe zapytania (a w Execution widać, że tak jest), to po co ma odpytywać na nowo bazę, skoro może zpytać statystyki, czy już czegoś takiego całkiem niedawno nie robił, czy się dane od tego czasu nie zmieniły, a skoro nie, to pobrać z cache... Logiczne. Dla kogoś, kto wie jak działają bazy i JAK NIE TESTOWAĆ zapytań. Musiłbyś przed każdym wykonniem zapytania albo wyłączyć statstyki, alebo zrestartować serwer...
|
|
|
17.03.2014, 09:13:13
Post
#12
|
|
Grupa: Zarejestrowani Postów: 8 Pomógł: 1 Dołączył: 12.03.2014 Ostrzeżenie: (0%) |
Zrób tak - na początku każdego zapytania DBCC DROPCLEANBUFFERS (serwer testowy !!), czasy obliczaj na podstawie zmiennych typu datetime2 (start i end).
Będzie to samo lub na granicy błędy statystycznego. Poza tym jak uruchoamiasz dwie kwerendy i porównujesz % udział w barchu to po każdej daj GO. Ewentualnie w swoich testach odwrtóć kolejność zapytań Poza tym obejrzyj statystyki odczytów - SET STATISTICS IO ON - identyczne. Jeśli plany są identyczne (wraz z predykatami), statystyki odczytów też to nie ma co drążyć dalej tematu - zapytania wykonywane są przez silnik w taki sam sposób - tak też właśnie jest w tym przypadku. Na koniec jeszcze raz polecam dodanie indeksu
Ten post edytował juris 17.03.2014, 09:16:56 |
|
|
28.03.2014, 14:22:54
Post
#13
|
|
Grupa: Zarejestrowani Postów: 282 Pomógł: 89 Dołączył: 12.04.2011 Ostrzeżenie: (0%) |
Z odpowiednimi indeksami rozwiązanie klasyczne jest wystarczająco szybkie dla stosunkowo niedużej bazy danych, a zapewne taka jest baza pracowników, bo nawet biorąc pod uwagę wszystkich zatrudnionych na naszych uczelniach pracowników mielibyśmy ilość rekordów rzędu 100k, a to jest liczba, którą może obsłużyć testowy serwer postawiony na leciwym laptopie. Oczywiście zakładając prawidłowe zaindeksowanie, bo bez indeksów nawet przy tej niewielkiej ilości danych serwer może się zmęczyć.
Dopiero gdy dane idą w miliony trzeba myśleć o rozwiązaniach dedykowanych dla silnika i wykorzystujących specyfikę rozkładu danych, mówiąc wcześniej o takim rozwiązaniu dla sql servera z wykorzystaniem CTE i CROSS APPLY chodziło mi o coś w tym stylu:
Czyli wykorzystanie rekurencji CTE do szybkiego znalezienia z indeksu wszystkich różnych etatów a następnie CROSS APPLY by w zależnym podzapytaniu móc umieścić własne TOP i ORDER BY, dzięki czemu dostajemy dla każdego etatu wykorzystując ten sam indeks najniższą płacę, co przy dość zaawansowanym optymalizatorze sql servera pozwala uniknąć pełnego skanu indeksu (a skan całej tabeli to w ogóle byłby horror), a zamiast tego szukamy tylko tyle ile mamy różnych etatów ( czyli w miarę ograniczona, można nawet założyć stała ilość - nawet O(log(N)) jest przecież dużo gorsze niż O(1)). Polecam do testowania na nieco większej bazie, dla ułatwienia:
Mamy tutaj 10M losowych rekordów dla 100 różnych etatów i prawidłowy indeks, z trzecim członem dla ewentualnego rozstrzygania remisów w płacach wg najniższego id. Samo tworzenie tabeli zajmuje kilka-kilkanaście minut na starym kompie, ale zapytanie wciąż pozostaje w obszarze milisekund. Dla porównania klasyczne rozwiązanie powinno trwać kilka sekund. |
|
|
31.03.2014, 09:07:51
Post
#14
|
|
Grupa: Zarejestrowani Postów: 8 Pomógł: 1 Dołączył: 12.03.2014 Ostrzeżenie: (0%) |
@pmir13 Bardzo mi się podoba Twoje podejście i to rozwiązanie dla b. dużych tabel = działa znakomicie !
Ten post edytował juris 31.03.2014, 09:08:34 |
|
|
3.04.2014, 09:06:36
Post
#15
|
|
Grupa: Zarejestrowani Postów: 14 Pomógł: 0 Dołączył: 4.09.2013 Ostrzeżenie: (0%) |
Dziękuję wszystkim uczestniką dyskusji, muszę przyznać, że jestem pod wrażeniem odpowiedzi i zyskałam świadomość mojej niewiedzy.
Pozdrawiam Morfina |
|
|
Wersja Lo-Fi | Aktualny czas: 25.04.2024 - 18:25 |