Trochę się pobawiłem, czasy na wolumenie 160k rekordów, mamy minimalne lepsze na korzyść selecta, zobacz jak wyjdzie u ciebie, ja przemyślałem sprawę i błąd selectów polegał na tym, że skupiliśmy się (ja i inni odpowiadający) na wyciągnięciu okresów i później zsumowaniu różnicy, kiedy to nie jest potrzebne do policzenia wyniku, w sensie czy policzę np od 1 do 31 stycznia, czy od 1-11, 12-26, 27-31...
Moja procedura:
ALTER PROCEDURE [dbo].[CZASY_SELECT]
AS
--tabela do obliczen
create table #agg (
id int identity(1,1) primary key
,d_start date
,d_koniec date
)
--dodaj dane pogrupowane po dacie startu
insert into #agg (d_start,d_koniec)
select d_start, max(d_koniec) from czasy group by d_start
--okresy zawierające się w innych
SELECT a.ID INTO #DID from #agg a
INNER join #agg s on a.d_start between s.d_start and s.d_koniec
and a.d_koniec between s.d_start and s.d_koniec
and a.id<>s.id
GROUP BY A.ID
--widok wykluczający okresy zawierające się w innych
;WITH W AS (
SELECT
A.*
FROM
#AGG A
LEFT JOIN #DID D ON D.ID=A.ID
WHERE
D.ID IS NULL
)
--liczenie
select
sum(datediff(minute,case when d_start > pd and d_start < nd then nd else d_start end,d_koniec))
from (
select
*
,lag(d_start,1) over (order by d_start) pd
,lag(d_koniec,1) over (order by d_start) nd
from W
) A
drop table #agg
DROP TABLE #DID
Trochę zmieniłem skrypt testujący:
SET NOCOUNT ON
declare @t0 datetime, @I INT, @SUM INT, @W INT
SET @I=1
SET @SUM=0
WHILE @I<=10
BEGIN
set @t0 = GETDATE()
exec CZASY_SELECT
SET @W = datediff(millisecond, @t0, GETDATE())
PRINT 'WYKONANIE #' + CONVERT(VARCHAR(2),@I) + ': ' + CONVERT(VARCHAR(10),@W)
SET @SUM = @SUM + @W
SET @I = @I+1
END
PRINT 'RAZEM: ' + CONVERT(VARCHAR(20),@SUM)
PRINT 'ŚREDNIA: ' + CONVERT(VARCHAR(20),@SUM/10)
Wyniki dla procedury JAR (wprowadziłem zmianę w kursorze i grupuje po dacie od)
10588320
WYKONANIE #1: 490
10588320
WYKONANIE #2: 356
10588320
WYKONANIE #3: 303
10588320
WYKONANIE #4: 276
10588320
WYKONANIE #5: 246
10588320
WYKONANIE #6: 226
10588320
WYKONANIE #7: 216
10588320
WYKONANIE #8: 203
10588320
WYKONANIE #9: 193
10588320
WYKONANIE #10: 180
RAZEM: 2689
ŚREDNIA: 268
dla CZASY_SELECT
10588320
WYKONANIE #1: 490
10588320
WYKONANIE #2: 363
10588320
WYKONANIE #3: 286
10588320
WYKONANIE #4: 260
10588320
WYKONANIE #5: 236
10588320
WYKONANIE #6: 210
10588320
WYKONANIE #7: 206
10588320
WYKONANIE #8: 190
10588320
WYKONANIE #9: 176
10588320
WYKONANIE #10: 173
RAZEM: 2590
ŚREDNIA: 259
Potraktuje to jako remis bo czasy są z niewielką przewagą dla selektów, mam wrażenie, że proces generowania danych ma słabości, bo mi przy stworzeniu tych 160000 okresów wyszło, ze nie mam żadnych luk...
Postanowiłem zwiększyć pulę do 500k rekordów i zobaczyć wyniki (w myśl zasady, że na większym zestawie będzie większa różnica), załączam skrypt insertu w załączniku (skrypt usuwa i zakłada tabelę czasy), cobyśmy testowali na tych samych danych, wyniki:
CZASY_SELECT
WYKONANIE #1: 620
WYKONANIE #2: 470
WYKONANIE #3: 380
WYKONANIE #4: 343
WYKONANIE #5: 300
WYKONANIE #6: 286
WYKONANIE #7: 260
WYKONANIE #8: 250
WYKONANIE #9: 230
WYKONANIE #10: 210
RAZEM: 3349
ŚREDNIA: 334
JAR
WYKONANIE #1: 990
WYKONANIE #2: 663
WYKONANIE #3: 560
WYKONANIE #4: 473
WYKONANIE #5: 423
WYKONANIE #6: 390
WYKONANIE #7: 373
WYKONANIE #8: 370
WYKONANIE #9: 366
WYKONANIE #10: 380
RAZEM: 4988
ŚREDNIA: 498
I Select jest średnio 32% szybszy, co i tak jest ciekawe bo przerabianych rekordów jest tyle samo...