Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
SQL
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
Comments
ZurdoDev 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.
frostcox 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900