Drukowana wersja tematu

Napisany przez: Morfina 27.02.2014, 09:44:21

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

Napisany przez: thek 3.03.2014, 10:40:03

Nie pamiętam czy pod MSSQL to działa, ale czy aby najprostsze:

[SQL] pobierz, plaintext 
SELECT dane FROM pracownicy GROUP BY etat HAVING MIN(placa)
[SQL] pobierz, plaintext 

Napisany przez: fizzlebubble 3.03.2014, 10:50:06

Lekko przyofftopuję... Coś te dane wyglądają znajomo... Politechnika Opolska? ; )

Napisany przez: widmo_91 3.03.2014, 10:53:24

Cytat(thek @ 3.03.2014, 10:40:03 )

[SQL] pobierz, plaintext 
SELECT dane FROM pracownicy GROUP BY etat HAVING MIN(placa)
[SQL] pobierz, plaintext 

Napisany przez: Morfina 4.03.2014, 13:16:04

Cytat(fizzlebubble @ 3.03.2014, 10:50:06 )

Napisany przez: 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ć.

Napisany przez: juris 12.03.2014, 09:16:34

Nie ma co gdybać co jest bardzie a co mniej wydajne - najlepiej to po porstu sprawdzić.

Zarówno zaproponowane przez kolegę podzapytanie skorelowane :

[SQL] pobierz, plaintext 
SELECT Nazwisko
FROM dbo.Pracownicy AS T1
WHERE Placa_Pod =
(
	SELECT MIN(Placa_Pod) AS Low
	FROM dbo.Pracownicy
	WHERE (Etat = T1.Etat)
)
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
SELECT T1.Nazwisko 
FROM dbo.Pracownicy T1 CROSS APPLY 
	( 
		SELECT MIN(Placa_Pod) AS Low
		FROM dbo.Pracownicy
		WHERE (Etat = T1.Etat) 
	) a
WHERE Placa_pod = Low
 
SELECT T1.Nazwisko 
FROM dbo.Pracownicy T1 INNER JOIN 
	(
		SELECT Etat,MIN(Placa_Pod) AS Low
		FROM dbo.Pracownicy
		GROUP BY Etat
	) m ON T1.Etat = m.Etat AND T1.Placa_Pod = m.Low
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE nonclustered INDEX Placa_pod_inc_Nazwisko ON dbo.Pracownicy(Etat,Placa_Pod) include ( Nazwisko )
[SQL] pobierz, plaintext 

Napisany przez: Morfina 13.03.2014, 16:41:32

Cytat(pmir13 @ 4.03.2014, 15:14:03 )

Napisany przez: 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.

Napisany przez: Morfina 15.03.2014, 08:34:13

Cytat(juris @ 14.03.2014, 09:12:05 )

Napisany przez: mmmmmmm 15.03.2014, 14:09:16

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...

Napisany przez: juris 17.03.2014, 09:13:13

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

[SQL] pobierz, plaintext 
CREATE nonclustered INDEX Placa_pod_inc_Nazwisko ON dbo.Pracownicy(Etat,Placa_Pod) include ( Imie, Nazwisko )
[SQL] pobierz, plaintext 

Napisany przez: pmir13 28.03.2014, 14:22:54

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:

[SQL] pobierz, plaintext 
WITH etaty AS
  (  
    SELECT MIN(etat) AS etat
    FROM pracownicy
    UNION ALL
    SELECT eo.etat
    FROM
    ( 
      SELECT p.etat, ROW_NUMBER() OVER (ORDER BY p.etat) AS nr_etatu
      FROM etaty e 
      JOIN pracownicy p 
      ON p.etat > e.etat
      WHERE e.etat IS NOT NULL
    ) eo
    WHERE eo.nr_etatu = 1
  )
SELECT pd.*
FROM etaty e
CROSS APPLY
  (
    SELECT TOP 1 pr.*
    FROM pracownicy pr
    WHERE pr.etat = e.etat
    ORDER BY placa_pod, id
  ) pd
OPTION (MAXRECURSION 0)
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE pracownicy (
  id INT NOT NULL PRIMARY KEY,
  nazwisko VARCHAR(64),
  imie VARCHAR(64),
  etat VARCHAR(16),
  placa_pod DECIMAL(20,2)
)
GO
 
CREATE INDEX etat_placa_id ON pracownicy( etat, placa_pod, id );
GO
 
BEGIN TRANSACTION
DECLARE @i INT
SET @i = 1+FLOOR(RAND(1))
WHILE @i < 10000000
BEGIN
  INSERT INTO pracownicy( id, nazwisko, imie, etat, placa_pod )
  VALUES(
     @i,
     'Nazwisko_'+CAST(@i AS VARCHAR(9)),
     'Imie_'+CAST(@i AS VARCHAR(9)),
     'Etat_'+CAST(FLOOR(RAND()*100)+1 AS VARCHAR(3)),
     (FLOOR(RAND()*300000+100000))/100
     )
  SET @i = @i+1;
END
COMMIT
[SQL] pobierz, plaintext 

Napisany przez: juris 31.03.2014, 09:07:51

@pmir13 Bardzo mi się podoba Twoje podejście i to rozwiązanie dla b. dużych tabel = działa znakomicie !

Napisany przez: Morfina 3.04.2014, 09:06:36

Dziękuję wszystkim uczestniką dyskusji, muszę przyznać, że jestem pod wrażeniem odpowiedzi i zyskałam świadomość mojej niewiedzy.

Pozdrawiam Morfina