[MSSQL] Optymalizacja i kursory

0

Cześć, czytam tak sobie, że kursory mają negatywny wpływ na czas wykonania operacji.
Wobec tego, czym je zamienić, jeśli muszę wykonywać operacje na danych wiersz po wierszu?

Czytałem trochę w necie i próbowałem kombinować z ROW_NUMBER(), jednak SQL mi krzyczy, że nie mogę w takim zapytaniu pobierać danych do zmiennych, tzn. rzuca się o:

SELECT ROW_NUMBER() OVER ...blabla.... @zmienna1 = pole, @zmienna2 = pole2
WHERE blabla

Więc co zamiast kursorów?

0

Jakie to operacje? Nie mozesz wykorzystac funkcji?

0

Daj kod.

0
AdamPL napisał(a)

Daj kod.

OK kod wygląda mniej więcej tak:

Na początku procedura FillRCP, której zadaniem jest włożyć rekordy do tabeli RCP.
Pola w tabeli RCP: (ID; employeeID; startDate; firstIn; lastOut; workTime; late; overtime; earlyIn; earlyOut; matchedShift)

I teraz tak, startDate to data rozpoczęcia pracy. firstIn, lastOut - wiadomo. Dodatkowo lastOut może być dzień później niż firstIn ;>
workTime - efektywny czas pracy, czyli nie lastOut - firstIn, ale wyklucza wszystkie wyjścia niesłużbowe pracownika w dniu. MatchedShift - ID dopasowanej zmiany, jeśli pracownik ma mieć automatycznie dopasowaną zmianę do swojego wejścia.

Wiemy już, co ma zawierać tabela RCP, a teraz jej wypełnianie. Procedura FillRCP:

ALTER PROCEDURE [dbo].[FillRcp] 
	@startDate datetime = null,	
	@stopDate datetime = null,
	@employeeID bigint = null
AS
BEGIN
	SET NOCOUNT ON

	TRUNCATE TABLE RCP

	DECLARE @firstIn DATETIME
	DECLARE @lastOut DATETIME
	DECLARE @late int --spóźnienie
	DECLARE @overtime int --nadgodziny
	DECLARE @workTime int --czas pracy
	DECLARE @endWorkDate datetime
	DECLARE @matchedShift bigint
	DECLARE @earlyIn datetime --wcześniejsze wejście
	DECLARE @earlyOut datetime --wcześniejsze wyjście	

	DECLARE @eid bigint
	DECLARE @date datetime

	
-- ten select jest taki, a nie inny(distinct) ze względu na optymalizację
	DECLARE ev CURSOR FOR	
		select a.employeeID, a.date
		from 
		(
			select employeeID, [dbo].get_date(date) as date
			FROM [dbo].V_Events		
			where (@employeeID is null or @employeeID = employeeID) AND
			(@startDate is null or @startDate<=[dbo].get_date(date)) AND
			(@stopDate is null or @stopDate>=[dbo].get_date(date))
		) as a
		group by a.employeeID, a.date
		order by a.employeeID, a.date

--powyższy select pobiera mi IDPracownika i daty jego odbić

	OPEN ev

	FETCH NEXT FROM ev INTO @eid, @date
	WHILE @@FETCH_STATUS = 0
	begin
		if @eid is null
		begin
			FETCH NEXT FROM ev INTO @eid, @date
			continue
		end

		SET @firstIn = null
		SET @endWorkDate = null
		SET @lastOut = null
        SET @late = null
		SET @overtime = null
		SET @earlyIn = null
		SET @earlyOut = null
		
--teraz wywołuję kilka swoich funkcji

		--pobieram wejście
		SET @firstIn = [dbo].getFirstIn(@eid, @date)

		--pobieram datę końca pracy
		SET @EndWorkDate = [dbo].getEndDate(@eid, @date)

		--pobieram wyjście
		SET @lastOut = [dbo].GetLastOut(@eid, @EndWorkDate)

		--liczę spóźnienie itp
		execute CalculateLateOver @eid, @firstIn, @lastOut, @late output, @overtime output, @matchedShift output
		
--jeśli spóźnienie <0 to jest to wcześniejsze wejście
		if @late<0 
		begin
			SET @earlyIn = [dbo].secToTime(abs(@late))
			SET @late = 0
		end
	
		if @overtime<0 
		begin
			SET @earlyOut = [dbo].secToTime(abs(@overtime))
			SET @overtime = 0
		end

		--liczę czas pracy
		execute CalculateWorkTime @eid, @firstIn, @lastOut, @workTime output

		--robię insert
		INSERT INTO rcp(employeeID, startDate, firstIn, lastOut, workTime, 
					late, overtime, earlyIn, earlyOut, matchedShift)
		VALUES (@eid, @date, @firstIn, @lastOut, [dbo].secToTime(@workTime), 
				[dbo].secToTime(@late), [dbo].secToTime(@overtime), 
				@earlyIn, @earlyOut, @matchedShift)

	    FETCH NEXT FROM ev INTO @eid, @date	
	end

	CLOSE ev
	DEALLOCATE ev	
END

Generalnie ta procedura wg planu zajmuje 30%. Nie znam się za bardzo na czytaniu planów, ale podejrzewam, że te 30% to czas funkcji i procedur wywoływanych przez tą :)

Kursor mam też w procedurze CalculateWorkTime. Pobieram w niej wszystkie odbicia dla danego pracownika pomiędzy firstIn i lastOut w danym dniu pracy. Następnie sprawdzam, czy odbicie jest wejściem, wyjściem, czy wyjściem służbowym. A konkretnie, czy wliczać je do czasu pracy, czy nie. No i później sumuję.

0

Myślę, że główny problem to zła architektura tego rozwiązania. Jak sam zauważyłeś kursor ma negatywny wpływ na szybkość. Jednak 100 razy gorsze jest uruchamianie procedur w kursorze dla każdego rekordu z osobna.

Zamiast obliczać spóźnienia i nadgodziny w kursorze można to zrobić jednym trywialnym updatem:

update dbo.rcp
set late = DATEDIFF(MINUTE, lrcp.Wejscie, dateadd(hour, 8, dateadd(dd, datediff(dd, 0, getdate()), 0))) --zakładając, że dzień zaczyna się o 8 rano
set overtime = DATEDIFF(MINUTE,lrcp.Wejscie, lrcp.Wyjscie) - 480 --zakładając, że dzień pracy trwa 8*60minut = 480 minut
from dbo.rcp rcp
inner join dbo.linijki_rcp lrcp on rcp.EmployeeID = lrcp.EmployeeID
inner join
(
  select EmployeeId, Date
  (
                          select employeeID, [dbo].get_date(date) as date
                          FROM [dbo].V_Events               
                          where (@employeeID is null or @employeeID = employeeID) AND
                          (@startDate is null or @startDate<=[dbo].get_date(date)) AND
                          (@stopDate is null or @stopDate>=[dbo].get_date(date))
  ) as a
  group by a.employeeID, a.date
  order by a.employeeID, a.date
) as t on t.EmployeeId = rcp.EmployeeId and t.Date = lrpc.Date

Nie wiem jaką masz strukturę danych dlatego to tylko szkic jak możesz to zrobić. Założyłem, że masz tabelkę "lrcp" w której są przechowywane Wejscia i Wyjscia z pracy pracownika.

Inne wyliczenia w procedurach, które są teraz w kursorze robisz analogicznie. Ten kursor naprawdę jest zbędny, a już naprawdę zbędne i czasożerne jest wielokrotne wywoływanie procedur, które można zamienić na update.

Poza tym tego selecta

select a.employeeID, a.date
                from
                (
                        select employeeID, [dbo].get_date(date) as date
                        FROM [dbo].V_Events               
                        where (@employeeID is null or @employeeID = employeeID) AND
                        (@startDate is null or @startDate<=[dbo].get_date(date)) AND
                        (@stopDate is null or @stopDate>=[dbo].get_date(date))
                ) as a
                group by a.employeeID, a.date
                order by a.employeeID, a.date

można na pewno uprościć. Po pierwsze wywal tego viewsa i wstaw zamiast niego kod tego viewsa. Select z viewsa jest bardziej czytelny ale jednocześnie bardziej czasożerny.

0

Hmm, no raczej nie mogę się oprzeć na Twoim rozwiązaniu, bo problem polega na tym, że dzień pracy nie zawsze trwa 8 godzin. Mało tego, dla każdego pracownika może być inaczej.
Ma na to wpływ szereg czynników. M.in. jak pracownik ma ustawione kalendarze(albo ich brak), jak pracownik ma ustawione zmiany(albo ich brak :)), generalnie muszę najpierw się dowiedzieć jak pracownik powinien pracować. Czyli godzinę(i dzień) wejścia i wyjścia.

Nie widzę, jak to wpleść w to zapytanie, bo jest to dość pokomplikowane(i zapytanie, i sposób sprawdzania, jak powinien pracować).

Ale mam pewien pomysł. Żeby ograniczyć jak najbardziej wywoływanie funkcji w kursorze, próbuję tworzyć zapytania, które wypełnią mi 3 dodatkowe tabele.
Generalnie jeden select(nawet z funkcją) jest dużo szybszy niż kilka selectów w kursorze.
Już udało mi się insertować do tabeli pierwsze wejścia pracownika(pozbycie się jednej funkcji) i już to działa dużo szybciej.
Teraz pracuję nad tym, żeby w drugiej tabeli umieścić datę zakończenia pracy, a w trzeciej ostatnie wyjście pracownika. Niestety ostatnie wyjście mogę dopiero policzyć wtedy, kiedy mam datę zakończenia pracy. A datę zakończenia pracy mogę obliczyć wtedy, kiedy mam pierwsze wejście w dniu rozpoczęcia pracy. Dlatego rozbijam to na 3 tabele.
Potem je planuję zjoinować i wynik wrzucić do kursora. I generalnie w kursorze zostanie mi do wykonania jedna, czy dwie procedury. Chociaż i może to da się wywalić, ale jak na razie o tym nie myślałem.

A co do widoków, to jestem zdania, że po to są, żeby z nich korzystać ;) Zwłaszcza, że ten konkretnie widok jest naprawdę nieprosty ;> Bo to zagnieżdżony case(a w casie select też niezły) i 5 joinów :)

To jakbym wrzucił to zapytanie zamiast widoku, to już na bank w niczym bym się nie pokapił.

0

Jeżeli każdy pracownik ma inną ilość godzin do przepracowania to zrobiłbym tabelkę z idpracownika i ilością godzin, które musi przepracować. Dzięki temu mógłbym w tym updatecie, który dałem wcześniej zjoinować tą tabelę z ilością które musi przepracować i wpleść to do tego datediff i zamiast 8 wstawić prawidłową ilość godzin, to samo z wyliczaniem nadgodzin.

Nadal jestem przekonany, że można to zrobić jednym zapytaniem zamiast kursora z procedurami. Pamiętaj, że to właśnie ten kursor i wywoływane w nim procedury spowalniają całość najbardziej.

Kolejna sprawa to zrób dobrze indeksy na tabelach tj. na polach po których najczęściej joinujesz, wyszukujesz, sortujesz.

/edit: można również zjoinować w tym update tabelki z regułami na podstawie można wyliczyć ilość godzin i je wyliczyć po prostu.

0

Na razie robię to tak, jak planowałem i okazuje się, że dla jednego pracownika z 7 miesięcy liczy sekundę. Dla porównania na samym początku było ok 3,5 minuty ;)

Dla 23 pracowników w takim samym okresie liczy 17 sekund.

W każdym razie nie pozbędę się kursora(chyba, że mogę jakoś ;>) w procedurze, która liczy czas pracy.

Polega to na tym, że w kursorze są odbicia dla konkretnego pracownika i konkretnego okresu(początek pracy - koniec pracy).
Następnie, zakładając że wszystkie odbicia są poprawne(dla lepszego wytłumaczenia), czyli:
WE WY WE WY WE WY

od godziny wyjścia odejmuję godzinę wejścia. Czyli jeśli aktualne odbicie jest wyjściem, to odejmuję godzinę poprzedniego odbicia. Oczywiście biorę tutaj pod uwagę jeszcze wyjścia służbowe itd.

Tutaj się chyba nie da uniknąć kursora, nie?

0

Nie znam dokładnej struktury danych, więc z całą pewnością nie powiem czy da się uniknąć kursora. Osobiście unikam kursorów jak ognia ale przyznaje, że czasami nie da się ich uniknąć. W każdym razie jeżeli się da wywalić kursor to powinno się to zrobić bo to na pewno wpłynie na wydajność.

Jeżeli chodzi o liczenie tych godzin to jestem przekonany, że można to zrobić w jednym zapytaniu. Za pomocą DATEDIFF można uzyskać ilość minut, którą przepracował pracownik. Nawet jeżeli wychodził służbowo kilka razy w ciągu dnia to wystarczy te przepracowane minuty zsumować.

Tam gdzie się da spróbuj wyrzucić te procedury z kursora na rzecz zapytań i nawet jeżeli ten kursor ostatecznie zostanie do liczenia czegoś czego nie będziesz mógł uprościć to na pewno zobaczysz różnicę.

0
AdamPL napisał(a)

Jeżeli chodzi o liczenie tych godzin to jestem przekonany, że można to zrobić w jednym zapytaniu. Za pomocą DATEDIFF można uzyskać ilość minut, którą przepracował pracownik. Nawet jeżeli wychodził służbowo kilka razy w ciągu dnia to wystarczy te przepracowane minuty zsumować.

Jest problem. Bo są typy "wyjść". Tzn: "Wejście", "Wyjście", "Wyjście służbowe" - to są rzeczy słownikowe. Generalnie każde "wyjście" ma flage: "includeWT", która mówi o tym, czy ma być wliczane do czasu pracy, czy nie.

Tam gdzie się da spróbuj wyrzucić te procedury z kursora na rzecz zapytań i nawet jeżeli ten kursor ostatecznie zostanie do liczenia czegoś czego nie będziesz mógł uprościć to na pewno zobaczysz różnicę.

No, zostały mi tylko dwie procedury, resztę udało się na zapytania. No tylko lekko musiałem zmienić strukturę bazy, ale zszdłem z ok 3,5 minuty do jednej sekundy :)

1 użytkowników online, w tym zalogowanych: 0, gości: 1