Query Problem with SUM() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Problem with SUM()

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2009-09-23 : 15:46:05

Ok when I execute the query below it returns results in less than a second but when I execute the second query listed where I have added the SUM() function it take between 40 sec and a minute to execute. Do I need to index the Duration field to speed it up? I just can't imagine that I would have to do that.

Thanks for the help

Nick

SELECT CONVERT(VARCHAR(8), DATEADD(s, DateTimeInt, '1970-01-01 00:00:00'), 1) AS CallDate, COUNT(*) AS CallCount
FROM CDRS
WHERE (DateTimeInt BETWEEN 1252886400 AND 1253491199) AND (Duration > 1) AND (OrigGw LIKE 'GB-GW2') AND (OrigPort = '3')
GROUP BY CONVERT(VARCHAR(8), DATEADD(s, DateTimeInt, '1970-01-01 00:00:00'), 1)
ORDER BY CallDate

----------------------------------------------------------

SELECT CONVERT(VARCHAR(8), DATEADD(s, DateTimeInt, '1970-01-01 00:00:00'), 1) AS CallDate, COUNT(*) AS CallCount, SUM(RoundDuration) / 60 AS Duration
FROM CDRS
WHERE (DateTimeInt BETWEEN 1252886400 AND 1253491199) AND (Duration > 1) AND (OrigGw LIKE 'GB-GW2') AND (OrigPort = '3')
GROUP BY CONVERT(VARCHAR(8), DATEADD(s, DateTimeInt, '1970-01-01 00:00:00'), 1)
ORDER BY CallDate

bbbgggn
Starting Member

1 Post

Posted - 2009-09-23 : 16:35:27

Hi
it seems the problem is in the 2 occasions of "Duration" field, so
try to replace this peace SUM(RoundDuration) / 60 AS Duration
by SUM(RoundDuration) / 60 AS Duration2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources