Click here to Skip to main content
11,490,801 members (65,584 online)
Rate this: bad
good
Please Sign up or sign in to vote.
Hey guys I thought I had this issue sorted but turns out I dont. Ok the issue is when I run the below query in management studio it runs in 4 seconds but when I run the report in reporting services it just hangs. I reall can't get to the bottom of this. Here's the query
    DECLARE @FromDate smalldatetime = '01-Oct-2010'--DateAdd(m, -1, Getdate());
    DECLARE @ToDate smalldatetime = '30-Nov-2012' -- GetDate()
    Declare @CarrierId smallint = 0 -- All
    Declare @CarrierClassId smallint = 0 -- All

    SELECT  dg.DestinationGroupName AS Destination, c.CarrierShortName AS Carrier,
	SUBSTRING(CONVERT(VARCHAR(11), ba.CDRDate, 106), 4, LEN(CONVERT(VARCHAR(11),ba.CDRDate, 106))) AS Month,
 
	CASE WHEN DatePart(Month, ba.CDRDate) = DatePart(Month, getdate()) and DatePart(year, ba.CDRDate) = DatePart(year, getdate())
	THEN ((SUM(ba.TotalDuration)/ 60) / DatePart(day, GetDate())) * DAY(DATEADD(mm, DATEDIFF(mm, -1, GetDate()), -1)) 
	ELSE  SUM(ba.TotalDuration) / 60  END AS DurationInMinutes,
 
	CASE WHEN DatePart(Month, ba.CDRDate) = DatePart(Month, getdate()) and DatePart(year, ba.CDRDate) = DatePart(year, getdate())
	THEN (SUM(ba.TotalCost) / DatePart(day, GetDate())) * DAY(DATEADD(mm, DATEDIFF(mm, -1, GetDate()), -1))  
	ELSE SUM(ba.TotalCost)  END AS TotalRevenue,
 
	CASE WHEN DatePart(Month, ba.CDRDate) = DatePart(Month, getdate()) and DatePart(year, ba.CDRDate) = DatePart(year, getdate())
	THEN (SUM(av.AvgCost * ba.TotalDuration) / DatePart(day, GetDate())) * DAY(DATEADD(mm, DATEDIFF(mm, -1, GetDate()), -1))
	ELSE SUM(av.AvgCost * ba.TotalDuration)   END AS TotalCost,
 
	SUM(ba.TotalCost) - SUM(av.AvgCost * ba.TotalDuration) AS Margin
 
    FROM mvwBillingAggregateCurrencyConverted AS ba INNER JOIN
        Destination AS d WITH (nolock) ON d.DestinationId = ba.DestinationId INNER JOIN
        DestinationBand AS db WITH (NoLock) ON d.DestinationBandId = db.DestinationBandId INNER JOIN
        DestinationGroup AS dg WITH (nolock) ON dg.DestinationGroupId = db.DestinationGroupId INNER JOIN
        Carrier AS c ON c.CarrierId = ba.CarrierId LEFT OUTER JOIN
        (SELECT  dg2.DestinationGroupId, SUM(ba2.TotalCost) / SUM(ba2.TotalDuration) AS AvgCost
    FROM mvwBillingAggregateCurrencyConverted AS ba2 INNER JOIN
        Destination AS d2 WITH (nolock) ON d2.DestinationId = ba2.DestinationId INNER JOIN
        DestinationBand AS db2 WITH (NoLock) ON d2.DestinationBandId = db2.DestinationBandId INNER JOIN
        DestinationGroup AS dg2 WITH (nolock) ON dg2.DestinationGroupId = db2.DestinationGroupId INNER JOIN
        Carrier AS c2 ON c2.CarrierId = ba2.CarrierId
    WHERE (ba2.CDRDate BETWEEN @FromDate AND @ToDate) AND (ba2.BillRecFlag <> 0) and dg2.DestinationGroupId in(1,4,5,6,7)
    GROUP BY dg2.DestinationGroupId) AS av ON 
        dg.DestinationGroupId = av.DestinationGroupId
    WHERE (ba.CDRDate BETWEEN @FromDate AND @ToDate) AND (ba.BillRecFlag = 0) AND (@CarrierId = 0 or ba.CarrierId = @CarrierId)
    AND (@CarrierClassId = 0 or ba.CarrierId in (Select c.CarrierId From dbo.Carrier c with (nolock) where c.CarrierClassId = @CarrierClassId)) and dg.DestinationGroupId in(1,4,5,6,7)
    GROUP BY dg.DestinationGroupName, c.CarrierShortName, SUBSTRING(CONVERT(VARCHAR(11), ba.CDRDate, 106), 4, LEN(CONVERT(VARCHAR(11), ba.CDRDate, 106))),  ba.CDRDate
    ORDER BY  Year(ba.CDRDate) ,Month(ba.CDRDate)

Maybe someone could tell me how to tidy up the query maybe to get it to run faster because im at a loss.
Thanks for the help guys.
Posted 14-Feb-13 5:22am
frostcox2.4K
Comments
ryanb31 at 14-Feb-13 10:45am
   
Have you used SQL profiler to do a trace? How many records does this return? It may be that the SQL is done in 4 seconds but there are a million rows and the report viewer is choking on that.
Mickt1985 at 14-Feb-13 11:01am
   
Hey the query returns about 4000 rows so I don't think its a massive chunk of data. Yeah there is a good bit of aggregation that happens in the report but if I leave out one of the destinationgroups it runs fine?? Any ideas?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 476
1 Andy Lanng 288
2 Sascha Lefèvre 240
3 OriginalGriff 178
4 Maciej Los 160
0 Sergey Alexandrovich Kryukov 9,713
1 OriginalGriff 8,475
2 Sascha Lefèvre 3,544
3 Maciej Los 3,106
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 14 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100