Simpler SQL Select -> extreme Performance/Speicherprobleme

SonnyHH

Benutzer
Beiträge
22
Hallo zusammen!

Vielleicht hat jemand eine Idee für mein Problem. Es geht um eine simple SQL Selektion, in der ich im WITH Block Subselektionen vornehme und anschließend zusammen auswerten möchte. Wenn es weiterhilft, die Haupttabelle beinhaltet mal gerade 867.526 Datensätze und für den derzeit eingeschränkten Zeitraum sind es nur 9.791 Datensätze.

Die Abfrage funktioniert solange recht performant, bis ich die 5. Tabelle (t5) per Join in den Hauptselekt aufnehme. Die Zeiten sind ungefähr:

t1 + t2 = 1 Sek.
+ t3 = 2 Sek.
+t4 = 22 Sek
bei t5 habe ich nach ca. 10 Minuten abgebrochen. In einem gestrigen Versuch kam irgendwann die Fehlermeldung, dass die tempdb voll gelaufen ist.

Bei meinen Tests kommentiere ich lediglich im JOIN Teil aus, die WITH Blöcke lasse ich immer aktiv. Wird ein WITH Teil vom SQL Server nur ausgeführt, sofern er auch in der Hauptselektion benötigt wird oder wird der WITH Block komplett abgearbeitet und das Problem liegt im JOIN der Hauptselektion?

Anbei das Skript... hat jemand eine Idee? Die Tabelle hat übrigens auf alle relevanten Spalten Indizees.
---------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
-- Agenten Zeiten
----------------------------------------------------------------------------------------------------
DECLARE @DateFrom as date,
@DateTo as date

SET @DateFrom = '20151030';
SET @DateTo = '20151030';
----------------------------------------------------------------------------------------------------
WITH
basis_Selection AS
(
SELECT NurDatum as Datum,
MONTH(NurDatum) as Monat,
DAY(NurDatum) as Tag,
DATEPART(WEEK, NurDatum) as Kalenderwoche,
DATEPART(weekday, NurDatum) as WochentagNr,
CASE
WHEN DATEPART(weekday, NurDatum) = 1 THEN 'Montag'
WHEN DATEPART(weekday, NurDatum) = 2 THEN 'Dienstag'
WHEN DATEPART(weekday, NurDatum) = 3 THEN 'Mittwoch'
WHEN DATEPART(weekday, NurDatum) = 4 THEN 'Donnerstag'
WHEN DATEPART(weekday, NurDatum) = 5 THEN 'Freitag'
WHEN DATEPART(weekday, NurDatum) = 6 THEN 'Samstag'
WHEN DATEPART(weekday, NurDatum) = 7 THEN 'Sonntag'
END AS Wochentag

FROM [dbo].[BSAG_Report_Agenten]

WHERE NurDatum between @DateFrom and @DateTo

GROUP BY
NurDatum
),
----------------------------------------------------------------------------------------------------
time_Active AS
(
SELECT NurDatum as time_Active_Datum,
Start as Start,
[Stop] as 'Stop',
sum(Duration) as TimeActive,
IntervallStart =
case
when datepart(N,[Stop]) < 30 then Convert(varchar,Datepart(HH,[Stop])) + ':00'
when datepart(N,[Stop]) >= 30 then Convert(varchar,Datepart(HH,[Stop])) + ':30'
end,
IntervallEnde =
case
when datepart(N,[Stop]) < 30 then Convert(varchar,Datepart(HH,[Stop])) + ':30'
when datepart(N,[Stop]) >= 30 then Convert(varchar,Datepart(HH,Dateadd(HH,+1,[Stop]))) + ':00'
end

FROM [dbo].[BSAG_Report_Agenten]

WHERE NurDatum between @DateFrom and @DateTo
AND Action = 'Active'

GROUP BY
NurDatum,
Start,
[Stop]
),
----------------------------------------------------------------------------------------------------
time_Alerting AS
(
SELECT NurDatum as time_Alerting_Datum,
Start as Start,
[Stop] as 'Stop',
sum(Duration) as TimeAlerting,
IntervallStart =
case
when datepart(N,[Stop]) < 30 then Convert(varchar,Datepart(HH,[Stop])) + ':00'
when datepart(N,[Stop]) >= 30 then Convert(varchar,Datepart(HH,[Stop])) + ':30'
end,
IntervallEnde =
case
when datepart(N,[Stop]) < 30 then Convert(varchar,Datepart(HH,[Stop])) + ':30'
when datepart(N,[Stop]) >= 30 then Convert(varchar,Datepart(HH,Dateadd(HH,+1,[Stop]))) + ':00'
end

FROM [dbo].[BSAG_Report_Agenten]

WHERE NurDatum between @DateFrom and @DateTo
AND Action = 'Alerting'

GROUP BY
NurDatum,
Start,
[Stop]
),
----------------------------------------------------------------------------------------------------
time_Busy AS
(
SELECT NurDatum as time_Busy_Datum,
Start as Start,
[Stop] as 'Stop',
sum(Duration) as TimeBusy,
IntervallStart =
case
when datepart(N,[Stop]) < 30 then Convert(varchar,Datepart(HH,[Stop])) + ':00'
when datepart(N,[Stop]) >= 30 then Convert(varchar,Datepart(HH,[Stop])) + ':30'
end,
IntervallEnde =
case
when datepart(N,[Stop]) < 30 then Convert(varchar,Datepart(HH,[Stop])) + ':30'
when datepart(N,[Stop]) >= 30 then Convert(varchar,Datepart(HH,Dateadd(HH,+1,[Stop]))) + ':00'
end

FROM [dbo].[BSAG_Report_Agenten]

WHERE NurDatum between @DateFrom and @DateTo
AND Action = 'Busy'

GROUP BY
NurDatum,
Start,
[Stop]
),
----------------------------------------------------------------------------------------------------
... Skript gekürzt, wegen Forenbeschränkung
----------------------------------------------------------------------------------------------------
time_Wrapup AS
(
SELECT NurDatum as time_WrapUp_Datum,
Start as Start,
[Stop] as 'Stop',
sum(Duration) as TimeWrapUp,
IntervallStart =
case
when datepart(N,[Stop]) < 30 then Convert(varchar,Datepart(HH,[Stop])) + ':00'
when datepart(N,[Stop]) >= 30 then Convert(varchar,Datepart(HH,[Stop])) + ':30'
end,
IntervallEnde =
case
when datepart(N,[Stop]) < 30 then Convert(varchar,Datepart(HH,[Stop])) + ':30'
when datepart(N,[Stop]) >= 30 then Convert(varchar,Datepart(HH,Dateadd(HH,+1,[Stop]))) + ':00'
end

FROM [dbo].[BSAG_Report_Agenten]

WHERE NurDatum between @DateFrom and @DateTo
AND Action = 'WrapUp'

GROUP BY
NurDatum,
Start,
[Stop]
)
----------------------------------------------------------------------------------------------------
SELECT t1.Datum as Datum,
t1.Wochentag as Wochentag,
t1.Kalenderwoche,
t2.IntervallStart,
t2.IntervallEnde,
Sum(t2.TimeActive) as TimeActive,
Sum(t3.TimeAlerting) as TimeAlerting,
Sum(t4.TimeBusy) as TimeBusy,
Sum(t5.TimeDialing) as TimeDialing,
Sum(t6.TimeHookOffExternal) as TimeHookOffExternal,
Sum(t7.TimeHookOffInternal) as TimeHookOffInternal,
Sum(t8.TimeKnocking) as TimeKnocking,
Sum(t9.TimeOnHold) as TimeOnHold,
Sum(t10.TimeRinging) as TimeRinging,
Sum(t11.TimeTransferring) as TimeTransferring,
Sum(t12.TimeWrapUp) as TimeWrapUp

FROM basis_Selection t1
left join time_Active t2 on t1.Datum = t2.time_Active_Datum
left join time_Alerting t3 on t1.Datum = t3.time_Alerting_Datum
left join time_Busy t4 on t1.Datum = t4.time_Busy_Datum
left join time_Dialing t5 on t1.Datum = t5.time_Dialing_Datum
left join time_HookOffExternal t6 on t1.Datum = t6.time_HookOffExternal_Datum
left join time_HookOffInternal t7 on t1.Datum = t7.time_HookOffInternal_Datum
left join time_Knocking t8 on t1.Datum = t8.time_Knocking_Datum
left join time_OnHold t9 on t1.Datum = t9.time_OnHold_Datum
left join time_Ringing t10 on t1.Datum = t10.time_Ringing_Datum
left join time_Transferring t11 on t1.Datum = t11.time_Transferring_Datum
left join time_Wrapup t12 on t1.Datum = t12.time_WrapUp_Datum

Group By
t1.Datum,
t1.Wochentag,
t1.Kalenderwoche,
t2.IntervallStart,
t2.IntervallEnde

Order By
t1.Datum,
t2.IntervallStart

----------------------------------------------------------------------------------------------------
 
Werbung:
Hast du dich mal mit dem Ausführungsplan deines "simplen" Selects befasst? STRG + M im SQL Management Studio und dann laufen lassen. Ich würde mir die Selects im WITH Teil erstmal einzeln anschauen.
 
Zurück
Oben