[MSSQL] Optymalizacja i kursory

Odpowiedz Nowy wątek
2009-03-23 18:33
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?

Pozostało 580 znaków

2009-03-23 19:10
0

Jakie to operacje? Nie mozesz wykorzystac funkcji?


You need to learn how to walk
before you can run

Pozostało 580 znaków

2009-03-23 22:54
0

Daj kod.

Pozostało 580 znaków

2009-03-24 11:17
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ę.

Pozostało 580 znaków

2009-03-24 16:24
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.

Pozostało 580 znaków

2009-03-25 13:29
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ł.

Pozostało 580 znaków

2009-03-25 14:43
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.

Pozostało 580 znaków

2009-03-25 17:14
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?

Pozostało 580 znaków

2009-03-25 21:41
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ę.

Pozostało 580 znaków

2009-03-25 22:54
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 :)

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

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