Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

 
Reply to this topicStart new topic
> (Nie)Eleganckie zapytanie tablicowe., Prośba o pomoc w sformułowaniu zapytania…
Morfina
post 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




Go to the top of the page
+Quote Post
thek
post 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:
  1. SELECT dane FROM pracownicy GROUP BY etat HAVING MIN(placa)

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
Go to the top of the page
+Quote Post
fizzlebubble
post 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ł" :).
Go to the top of the page
+Quote Post
widmo_91
post 3.03.2014, 10:53:24
Post #4





Grupa: Zarejestrowani
Postów: 109
Pomógł: 25
Dołączył: 10.08.2009

Ostrzeżenie: (0%)
-----


Cytat(thek @ 3.03.2014, 10:40:03 ) *
Nie pamiętam czy pod MSSQL to działa, ale czy aby najprostsze:
  1. SELECT dane FROM pracownicy GROUP BY etat HAVING MIN(placa)

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.
Go to the top of the page
+Quote Post
Morfina
post 4.03.2014, 13:16:04
Post #5





Grupa: Zarejestrowani
Postów: 14
Pomógł: 0
Dołączył: 4.09.2013

Ostrzeżenie: (0%)
-----


Cytat(fizzlebubble @ 3.03.2014, 10:50:06 ) *
Lekko przyofftopuję... Coś te dane wyglądają znajomo... Politechnika Opolska? ; )


Dane znalezione przypadkiem na jakimś serwerze w Poznaniu, możliwe, że zapożyczone smile.gif
Go to the top of the page
+Quote Post
pmir13
post 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ć.
Go to the top of the page
+Quote Post
juris
post 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 :
  1. SELECT Nazwisko
  2. FROM dbo.Pracownicy AS T1
  3. WHERE Placa_Pod =
  4. (
  5. SELECT MIN(Placa_Pod) AS Low
  6. FROM dbo.Pracownicy
  7. WHERE (Etat = T1.Etat)
  8. )

Jak i dwa inne rozwiązania (moim zdaniem całkiem eleganckie smile.gif) dadzą takie same EFEKTY jeśli chodzi o wydajność. W końcu CROSS APPLY to nic innego jak zapytanie skorelowane.... a CTE questionmark.gif? 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.
  1. SELECT T1.Nazwisko
  2. FROM dbo.Pracownicy T1 CROSS APPLY
  3. (
  4. SELECT MIN(Placa_Pod) AS Low
  5. FROM dbo.Pracownicy
  6. WHERE (Etat = T1.Etat)
  7. ) a
  8. WHERE Placa_pod = Low
  9.  
  10. SELECT T1.Nazwisko
  11. FROM dbo.Pracownicy T1 INNER JOIN
  12. (
  13. SELECT Etat,MIN(Placa_Pod) AS Low
  14. FROM dbo.Pracownicy
  15. GROUP BY Etat
  16. ) m ON T1.Etat = m.Etat AND T1.Placa_Pod = m.Low

Każda z tych kwerend da identyczny plan wykonania. BTW - warto dodać jeszcze indeks np. taki :
  1. CREATE nonclustered INDEX Placa_pod_inc_Nazwisko ON dbo.Pracownicy(Etat,Placa_Pod) include ( Nazwisko )


Który dramatycznie poprawi wydajność smile.gif

pozrdrowienia !

Ten post edytował juris 12.03.2014, 09:17:16
Go to the top of the page
+Quote Post
Morfina
post 13.03.2014, 16:41:32
Post #8





Grupa: Zarejestrowani
Postów: 14
Pomógł: 0
Dołączył: 4.09.2013

Ostrzeżenie: (0%)
-----


Cytat(pmir13 @ 4.03.2014, 15:14:03 ) *
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

Go to the top of the page
+Quote Post
juris
post 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.
Go to the top of the page
+Quote Post
Morfina
post 15.03.2014, 08:34:13
Post #10





Grupa: Zarejestrowani
Postów: 14
Pomógł: 0
Dołączył: 4.09.2013

Ostrzeżenie: (0%)
-----


Cytat(juris @ 14.03.2014, 09:12:05 ) *
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
Go to the top of the page
+Quote Post
mmmmmmm
post 15.03.2014, 14:09:16
Post #11





Grupa: Zarejestrowani
Postów: 1 411
Pomógł: 307
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...
Go to the top of the page
+Quote Post
juris
post 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 exclamation.gif!!), czasy obliczaj na podstawie zmiennych typu datetime2 (start i end).
Będzie to samo smile.gif 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ń smile.gif

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
  1. CREATE nonclustered INDEX Placa_pod_inc_Nazwisko ON dbo.Pracownicy(Etat,Placa_Pod) include ( Imie, Nazwisko )


Ten post edytował juris 17.03.2014, 09:16:56
Go to the top of the page
+Quote Post
pmir13
post 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:

  1. WITH etaty AS
  2. (
  3. SELECT MIN(etat) AS etat
  4. FROM pracownicy
  5. UNION ALL
  6. SELECT eo.etat
  7. FROM
  8. (
  9. SELECT p.etat, ROW_NUMBER() OVER (ORDER BY p.etat) AS nr_etatu
  10. FROM etaty e
  11. JOIN pracownicy p
  12. ON p.etat > e.etat
  13. WHERE e.etat IS NOT NULL
  14. ) eo
  15. WHERE eo.nr_etatu = 1
  16. )
  17. SELECT pd.*
  18. FROM etaty e
  19. CROSS APPLY
  20. (
  21. SELECT TOP 1 pr.*
  22. FROM pracownicy pr
  23. WHERE pr.etat = e.etat
  24. ORDER BY placa_pod, id
  25. ) pd
  26. OPTION (MAXRECURSION 0)


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:
  1. CREATE TABLE pracownicy (
  2. id INT NOT NULL PRIMARY KEY,
  3. nazwisko VARCHAR(64),
  4. imie VARCHAR(64),
  5. etat VARCHAR(16),
  6. placa_pod DECIMAL(20,2)
  7. )
  8. GO
  9.  
  10. CREATE INDEX etat_placa_id ON pracownicy( etat, placa_pod, id );
  11. GO
  12.  
  13. BEGIN TRANSACTION
  14. DECLARE @i INT
  15. SET @i = 1+FLOOR(RAND(1))
  16. WHILE @i < 10000000
  17. BEGIN
  18. INSERT INTO pracownicy( id, nazwisko, imie, etat, placa_pod )
  19. VALUES(
  20. @i,
  21. 'Nazwisko_'+CAST(@i AS VARCHAR(9)),
  22. 'Imie_'+CAST(@i AS VARCHAR(9)),
  23. 'Etat_'+CAST(FLOOR(RAND()*100)+1 AS VARCHAR(3)),
  24. (FLOOR(RAND()*300000+100000))/100
  25. )
  26. SET @i = @i+1;
  27. END
  28. COMMIT

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.
Go to the top of the page
+Quote Post
juris
post 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
Go to the top of the page
+Quote Post
Morfina
post 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. snitch.gif

Pozdrawiam Morfina
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 



RSS Wersja Lo-Fi Aktualny czas: 19.07.2019 - 22:35