Announcement

Collapse
No announcement yet.

abfarge dauert extrem lange, optimierungen?

Collapse
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
  • #1

    abfarge dauert extrem lange, optimierungen?

    Hallo,

    ich hab hier 2abfragen:
    Code:
    DECLARE @Monatstart DATETIME; SET @Monatstart = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
DECLARE @Monatende DATETIME; SET @Monatende = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

SELECT
     CAST(FLOOR(CAST(IWrkgrpQueueStats.dIntervalStart AS FLOAT)) AS DATETIME) AS Datum,
     DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) AS Monatsstart,
	DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS Monatsende,
     "IWrkgrpQueueStats"."cName" AS Servicegruppe,
     {fn IFNULL(SUBQ.MaxAgentenLoggedIn,0)} AS MaxAgentLoggedIn,
     SUM(nAnsweredAcd + nAbandonedAcd + nFlowOutAcd) As SumAnrufe,
     SUM(tTalkAcd + tAcw) As Szenariozeit,
     SUM("IWrkgrpQueueStats"."nDuration") AS SumnDuration,
     SUM("IWrkgrpQueueStats"."nEnteredAcd") AS SumnEnteredAcd,
     SUM("IWrkgrpQueueStats"."nAlertedAcd") AS SumnAlertedAcd,
     SUM("IWrkgrpQueueStats"."nAbandonedAcd") AS SumnAbandonedAcd,
     SUM("IWrkgrpQueueStats"."nAnsweredAcd") AS SumnAnsweredAcd,
     SUM("IWrkgrpQueueStats"."nAnswered") AS SumnAnswered,
     SUM("IWrkgrpQueueStats"."nAnsweredAcdSvcLvl1") AS SumnAnsweredAcdSvcLvl1,
     SUM("IWrkgrpQueueStats"."nAnsweredAcdSvcLvl2") AS SumnAnsweredAcdSvcLvl2,
     SUM("IWrkgrpQueueStats"."nAnsweredAcdSvcLvl3") AS SumnAnsweredAcdSvcLvl3,
     SUM("IWrkgrpQueueStats"."nAnsweredAcdSvcLvl4") AS SumnAnsweredAcdSvcLvl4,
     SUM("IWrkgrpQueueStats"."nAnsweredAcdSvcLvl5") AS SumnAnsweredAcdSvcLvl5,
     SUM("IWrkgrpQueueStats"."nAnsweredAcdSvcLvl6") AS SumnAnsweredAcdSvcLvl6,
     SUM("IWrkgrpQueueStats"."nAbandonAcdSvcLvl1") AS SumnAbandonAcdSvcLvl1,
     SUM("IWrkgrpQueueStats"."nAbandonAcdSvcLvl2") AS SumnAbandonAcdSvcLvl2,
     SUM("IWrkgrpQueueStats"."nAbandonAcdSvcLvl3") AS SumnAbandonAcdSvcLvl3,
     SUM("IWrkgrpQueueStats"."nAbandonAcdSvcLvl4") AS SumnAbandonAcdSvcLvl4,
     SUM("IWrkgrpQueueStats"."nAbandonAcdSvcLvl5") AS SumnAbandonAcdSvcLvl5,
     SUM("IWrkgrpQueueStats"."nAbandonAcdSvcLvl6") AS SumnAbandonAcdSvcLvl6,
     SUM("IWrkgrpQueueStats"."nNotAnsweredAcd") AS SumnNotAnsweredAcd,
     SUM("IWrkgrpQueueStats"."nGrabbedAcd") AS SumnGrabbedAcd,
     SUM("IWrkgrpQueueStats"."tTalkAcd") AS SumtTalkAcd,
     SUM("IWrkgrpQueueStats"."tAnsweredAcd") AS SumtAnsweredAcd,
     SUM("IWrkgrpQueueStats"."tHoldAcd") AS SumtHoldAcd,
     SUM("IWrkgrpQueueStats"."tAcw") AS SumtAcw,
     SUM("IWrkgrpQueueStats"."tAlertedAcd") AS SumtAlertedAcd,
     SUM("IWrkgrpQueueStats"."tAgentTalk") AS SumtAgentTalk,
     SUM("IWrkgrpQueueStats"."tAgentInAcw") AS SumtAgentInAcw     
FROM
     "IWrkgrpQueueStats" LEFT OUTER JOIN
	(SELECT 	IAgentQueueStats.cReportGroup, CAST(FLOOR(CAST(IAgentQueueStats.dIntervalStart AS FLOAT)) AS DATETIME) AS Datum, COUNT(DISTINCT(IAgentQueueStats.[cName])) AS MaxAgentenLoggedIn
	 FROM	[IAgentQueueStats] IAgentQueueStats
	 WHERE	IAgentQueueStats.[cReportGroup] IN ('WoWi_1647')
	 AND 	IAgentQueueStats.[dIntervalStart] BETWEEN @Monatstart AND @Monatende
	 AND 	DATEPART(Hour,"IAgentQueueStats"."dIntervalStart") >= 8
 	 AND 	DATEPART(Hour,"IAgentQueueStats"."dIntervalStart") < 18
 	 AND		DATEPART(dw,"IAgentQueueStats"."dIntervalStart") >= 2
 	 AND 	DATEPART(dw,"IAgentQueueStats"."dIntervalStart") <= 6
	 AND 	IAgentQueueStats.[tAgentAcdLoggedIn] > 0 
	 AND		IAgentQueueStats.[cHKey3] = '*'
	 AND 	IAgentQueueStats.[cHKey4] = '*'
	 GROUP BY IAgentQueueStats.cReportGroup, CAST(FLOOR(CAST(IAgentQueueStats.dIntervalStart AS FLOAT)) AS DATETIME)) SUBQ 
	 ON		SUBQ.Datum = CAST(FLOOR(CAST(IWrkgrpQueueStats.dIntervalStart AS FLOAT)) AS DATETIME) AND SUBQ.cReportGroup = IWrkgrpQueueStats.cName
WHERE"IWrkgrpQueueStats"."cName" IN ('WoWi_1647')
 AND "IWrkgrpQueueStats"."dIntervalStart" BETWEEN @Monatstart AND @Monatende
 AND DATEPART(Hour,"IWrkgrpQueueStats"."dIntervalStart") >= 8
 AND DATEPART(Hour,"IWrkgrpQueueStats"."dIntervalStart") < 18
 AND DATEPART(dw,"IWrkgrpQueueStats"."dIntervalStart") >= 2
 AND DATEPART(dw,"IWrkgrpQueueStats"."dIntervalStart") <= 6
 AND "IWrkgrpQueueStats"."cReportGroup" = '*'
 AND "IWrkgrpQueueStats"."cHKey3" = '*'
 AND "IWrkgrpQueueStats"."cHKey4" = '*'
GROUP BY "IWrkgrpQueueStats"."cName", CAST(FLOOR(CAST(IWrkgrpQueueStats.dIntervalStart AS FLOAT)) AS DATETIME), SUBQ.MaxAgentenLoggedIn
ORDER BY "IWrkgrpQueueStats"."cName", CAST(FLOOR(CAST(IWrkgrpQueueStats.dIntervalStart AS FLOAT)) AS DATETIME), SUBQ.MaxAgentenLoggedIn
    und
    Code:
    DECLARE @Monatstart DATETIME; SET @Monatstart = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
DECLARE @Monatende DATETIME; SET @Monatende = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

SELECT 	"IWrkgrpQueueStats"."cName", CAST(FLOOR(CAST(IWrkgrpQueueStats.dIntervalStart AS FLOAT)) AS DATETIME) AS Datum, {fn IFNULL(SUBQ.MaxAgentLoggedIn,0)} AS MaxAgentLoggedIn
FROM 	IWrkgrpQueueStats LEFT OUTER JOIN
	(SELECT 	IAgentQueueStats.cReportGroup, CAST(FLOOR(CAST(IAgentQueueStats.dIntervalStart AS FLOAT)) AS DATETIME) AS Datum, COUNT(DISTINCT(IAgentQueueStats.[cName])) AS MaxAgentLoggedIn
	 FROM	[IAgentQueueStats] IAgentQueueStats
	 WHERE	IAgentQueueStats.[cReportGroup] IN ('WoWi_1647')
	 AND 	IAgentQueueStats.[dIntervalStart] BETWEEN @Monatstart AND @Monatende
	 AND		DATEPART(Hour,"IAgentQueueStats"."dIntervalStart") >= 8
 	 AND		DATEPART(Hour,"IAgentQueueStats"."dIntervalStart") < 18
 	 AND		DATEPART(dw,"IAgentQueueStats"."dIntervalStart") >= 2
 	 AND 	DATEPART(dw,"IAgentQueueStats"."dIntervalStart") <= 6
	 AND 	IAgentQueueStats.[tAgentAcdLoggedIn] > 0 
	 AND		IAgentQueueStats.[cHKey3] = '*'
	 AND 	IAgentQueueStats.[cHKey4] = '*'
	 GROUP BY IAgentQueueStats.cReportGroup, CAST(FLOOR(CAST(IAgentQueueStats.dIntervalStart AS FLOAT)) AS DATETIME)) SUBQ 
	 ON		SUBQ.Datum = CAST(FLOOR(CAST(IWrkgrpQueueStats.dIntervalStart AS FLOAT)) AS DATETIME) AND SUBQ.cReportGroup = IWrkgrpQueueStats.cName
WHERE"IWrkgrpQueueStats"."cName" IN ('WoWi_1647')
 AND "IWrkgrpQueueStats"."dIntervalStart" BETWEEN @Monatstart AND @Monatende
 AND DATEPART(Hour,"IWrkgrpQueueStats"."dIntervalStart") >= 8
 AND DATEPART(Hour,"IWrkgrpQueueStats"."dIntervalStart") < 18
 AND DATEPART(dw,"IWrkgrpQueueStats"."dIntervalStart") >= 2
 AND DATEPART(dw,"IWrkgrpQueueStats"."dIntervalStart") <= 6
 AND "IWrkgrpQueueStats"."cReportGroup" = '*'
 AND "IWrkgrpQueueStats"."cHKey3" = '*'
 AND "IWrkgrpQueueStats"."cHKey4" = '*'
GROUP BY "IWrkgrpQueueStats"."cName", CAST(FLOOR(CAST(IWrkgrpQueueStats.dIntervalStart AS FLOAT)) AS DATETIME), SUBQ.MaxAgentLoggedIn
ORDER BY "IWrkgrpQueueStats"."cName", CAST(FLOOR(CAST(IWrkgrpQueueStats.dIntervalStart AS FLOAT)) AS DATETIME), SUBQ.MaxAgentLoggedIn
    die abfrage dauert extrem lange (bei 3min hab i aufgebrochen), gibt es irgendwelche optimierungen womit das ganze schneller geht?

    schonmal besten dank im voraus!!
    Tags: None
  • #2
    Was sagt der Queryplan bezüglich benötigte Kosten?

    So überflogen würde ich sagen: Versuch das Sub-Query (... LEFT OUTER JOIN (SELECT IAgentQueueStats ...) aufzulösen.

    Comment

    • #3
      hi,

      ähm hab leider nicht wirklich so die ahnung von SQL (das was dort oben steht hab ich überwiegend zusammenkopiert), wie löse ich das Sub-Query denn auf?

      Comment

      • #4
        Originally posted by Colttt View Post
        ähm hab leider nicht wirklich so die ahnung von SQL (das was dort oben steht hab ich überwiegend zusammenkopiert),
        Wenn du wenig Ahnung hast wie sollen wir sowas Lösen können wenn wir dein Tabellenmodell nicht kennen wird das Problematisch ...

        Originally posted by Colttt View Post
        wie löse ich das Sub-Query denn auf?
        Gugst du z.B. mal hier.

        Comment

        • #5
          Wenn du wenig Ahnung hast wie sollen wir sowas Lösen können wenn wir dein Tabellenmodell nicht kennen wird das Problematisch ...
          wenn es einfach wäre, wäre es ja langweilig

          wenn du mir sagst was du mit Tabellenmodell meinst, bzw wie ich es dir zeigen kann, kann ich es dir zeigen damit du/ihr mir helfen könnt

          Comment

          Previous template Next
          Working...
          X