none
Report takes long time loading RRS feed

  • Question

  • I have a report which is fairly simple but takes a very long time..

    It involves the incidents being counted by categories hence it has several Union All.

    Also the report numbers are generetd through 2 tables hence within every Union All tehre is a left or an Inner join.

    sample code:

    SELECT
    1 Sort_Order,
    COUNT(*) AS Call_Count,
    'Incident Resolved at Level 1' AS Count_Type
    FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
    INNER JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
     ON T1.NUMBERPRGN = T2.NUMBERPRGN
     
    WHERE  PROBLEM_STATUS = 'closed'
                 AND T2.THIRD_ASSIGNEE IS NULL
     AND T2.THIRD_ASSIGNMENT IS NULL
     AND T1.SECONDARY_ASSIGNEE IS NULL
     AND HAL_FIRST_RES='t'
     AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
     AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR
      AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)

    UNION ALL

    -- Calls RESOLVED BY L2
    SELECT
    2 Sort_Order,
    COUNT(*) AS Call_Count,
    'Incidents Resolved at Level 2 or 3' AS Count_Type
    FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
    LEFT JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
     ON T1.NUMBERPRGN = T2.NUMBERPRGN
    WHERE  (HAL_FIRST_RES<>'t' OR HAL_FIRST_RES IS NULL)
     AND PROBLEM_STATUS = 'closed'
     AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
     AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR
       AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)

    UNION ALL

     

    could you suggest what might be the reason why teh report churns for so long.

    thanks,

    kiran.

     

    Friday, January 19, 2007 9:33 PM

All replies

  • Hello Kiran,

    You might want to do some tuning on the SQL code you are running from the report to try to speed it up.

    Jarret

    Friday, January 19, 2007 9:48 PM
  • Kiranvukkadala

     

    Do you have indexes on your tables? That would help you out a lot. Also,

    AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
    AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR  AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)

    Are some problem areas, cast and function calls will be problematic if you a returning a large row volumes. I would use a inner join on t1.OTI_Originator to UserID in the HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents table. I don't think you need a sub query here.

     

     

    Friday, January 19, 2007 9:50 PM