Grupowanie danych wg. rozdzielonych dat

0

Witam! Od niedawna borykam się z problemem, że zbyt długo czekam na odpowiedź od bazy danych, gdyż prawdopodobnie moja funkcja nie jest zoptymalizowana. Mój problem wygląda tak, że:

Teraz robię to tak i nie wiem czy istnieje może jakiś bardziej wyrafinowany sposób na to rozwiązanie:

Mam tabelę kontraktów z pracownikami, tj. Id pracownik, data startu kontraktu, data końca kontraktu.
Następna tabela to wakacje dla pracowników i ona również zawiera id pracownika, data startu wakacji i końca wakacji pracownika ( urlopu ).

Teraz dla każdego dnia w roku muszę znaleźć ile pracowników będzie danego dnia w pracy.

No i zrobiłem to tak, że utworzyłem sobie kursor z danymi z kontraktami i w pętli dla każdego ID pracownika biorę jego datę rozpoczęcia kontraktu i do nowej tabeli wstawiam wiersze z ID, Dzien, i tak aż do daty końca kontraktu.

Po utworzeniu już takowej tabelki, robię to samo tylko, że teraz dla tabeli z wakacjami, też rozdzielam na dni dla każdego pracownika.
Następnie od tabelki z rozdzielonymi dniami dla kontraktów odejmuje te z wakacjami. I na końcu grupuje wg. dnia i count'em zliczam ilość pracowników.

Może ktoś miał podobny problem lub można to z czymś porównać, jakoś przyśpieszyć? Każda odpowiedź będzie cenna

0

to moje zapytanie, które odejmuje dane...może ono jest zbyt wolne

SELECT Date, SUM(Hours) AS EmployeeHours, SUM(CONVERT(int, HouseNeeded)) AS HouseNeeded
FROM dbo.fnContractSplitDate(@StartDate, @EndDate) AS contracts
WHERE (Date NOT IN (SELECT Date
                               FROM dbo.fnHolidaysSplitDate(@StartDate, @EndDate) AS holidays
                               WHERE(Id = contracts.Id)))
GROUP BY Date 
0

sam Spliting robi dla kontraktów i dla urlopów w 0.8s.

0
 SELECT t.Date, SUM(t.Hours) AS EmployeeHours, SUM(CONVERT(int, t.HouseNeeded)) AS HouseNeeded FROM 
( select c.date, c.hours, c.houseneeded, h.date warunek from dbo.fnContractSplitDate(@StartDate, @EndDate) c left join dbo.fnHolidaysSplitDate(@StartDate, @EndDate) h on (c.id=h.id and c.date=h.date)) t where t.warunek is null group by t.date
0

Zajmuje 40 s jak dla mojego starego algorymtu...

1

pierwsze co to się ogląda plan zapytania, żeby stwierdzić czy gdzieś nie brakuje indeksów

0

Ils jest obecnie pracowników? Ile będzie docelowo?
Ile wykonuje się zapytanie dla mniejszej ilości pracowników 0.5, 0.25?

0

Dane są zwracane przez funkcje tabelaryczne, więc indeksów tam nie ma. Pojedynczy select wykonuje się szybko, natomiast join z filtrowaniem i grupowaniem z powodu braku indeksów wykonuje się długo

0

Po rozdzieleniu kontraktów jest około 45000 wierszy, natomiast urlopów około 3500 wierszy. Funkcja jak zwraca tabelę to traci indeksy?

0

W mssql w funkcji tabelarycznej nie założysz indeksów (możesz założyć primary key, ale akurat pola wykorzystywane do łączenia joinów nie łapią się na primary key, Twoje pierwsze zapytanie wskazuje, że nie są unikalne).
Funkcja tabelaryczna zwraca wynik w nowej tabeli (tabela jest jako zmiennal, więc nie bardzo ma jak gubić indeksy, ona po prostu ich nie posiada.

0

Czy dobrze zrozumiałem, że twoja funkcja zawiera kursor? Jeśli tak, to "se poczekasz"...

0

Rozdzielanie na daty posiada kursor.... To można to jakoś lepiej zrobić?

0

A pokaż jak zrobiłeś to rozdzielanie na daty (dni). Może się coś wymyśli.

0
declare @lclStartDate datetime
declare @lclEndDate datetime
declare @lclId int
declare @lclHours float
declare @lclHouseNeeded bit


declare contract_cursor cursor for

select - zapytanie z where i order by
--@StartDate i @EndDate parametry funkcji

open contract_cursor
fetch next from  contract_cursor into @lclId, @lclStartDate, @lclEndDate, @lclHours,@lclHouseNeeded
set @lclEndDate = dateadd(dd,-1,@lclEndDate)
while @@FETCH_STATUS = 0
begin
  if @lclStartDate < @StartDate 
  begin
	  set @lclStartDate = @StartDate
  end
  if @lclEndDate > @EndDate
  begin
	  set @lclEndDate = @EndDate
  end
  while @lclStartDate <= @lclEndDate
  begin
	  if(datename(dw,@lclStartDate) = 'Sunday')
	  begin
	    set @lclStartDate += 1
	    continue;
	  end
	  insert into @outputTable (Id, Date, Hours, HouseNeeded) values (@lclId, @lclStartDate, @lclHours/6,@lclHouseNeeded)
	  set @lclStartDate += 1
  end  
  fetch next from  contract_cursor into @lclId, @lclStartDate, @lclEndDate, @lclHours,@lclHouseNeeded
  set @lclEndDate = dateadd(dd,-1,@lclEndDate)
end

close contract_cursor
deallocate contract_cursor

return
0

Widzę, że tylko warunek jest na dzień tygodnia... to łatwo można obejść.
Możesz bez kursora zrobić na dwa sposoby (przynajmniej)

  1. SELECT 10*d+j lp FROM (SELECT 1 d UNION SELECT 2 UNION ... SELECT 9 UNION SELECT 0) d, (SELECT 1 j UNION SELECT 2 UNION ... SELECT 9 UNION SELECT 0) j
    to zapytanie cudownie rozmnoży ci rekordy od 0 do 99. Połączysz sobie to ze swoją tabelą jakoś tak: DATEADD(d, lp, DataStart)>=DataEnd
  2. Podobnie, tylko jako źródło rekordów dajesz CTE. Niestety, dla MS SQL bez zaglądania do dokumentacji nie umiem napisać (a zaglądać mi się nie chce)
0

@Marcin.Miga Korzystam z Toad dla Sql... na dole mogłem dodać Primary Key - dodałem dla Id, Date - czas skrócił się do 1 sekundy...czyli primary key ma tutaj wpływ..

Korzystam z tego przy zwracaniu wierszy:

SELECT Date, SUM(Hours) AS EmployeeHours, SUM(CONVERT(int, HouseNeeded)) AS HouseNeeded
FROM dbo.fnContractSplitDate(@StartDate, @EndDate) AS contracts
WHERE (Date NOT IN
                             (SELECT Date
                               FROM dbo.fnHolidaysSplitDate(@StartDate, @EndDate) AS holidays
                               WHERE (Id = contracts.Id)))
GROUP BY Date

Bo tamto co podał @Paweł Dmitruk zwraca raczej zły wynik.

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