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.
| Author |
Topic |
|
nhess80
Yak Posting Veteran
83 Posts |
 Posted - 2009-10-01 : 13:55:09
|
| So my problem is this. I have a query that calculates the sum of a column and when I run this query it take about 30 to 60 sec to complete. So I tried to index that column and now the query will execute in 4 seconds or less which is great but this table gets updated every day and takes hours as opposed to minutes when that column is indexed. Wondering if anyone has any advice. The query is below.SELECT CONVERT(VARCHAR(8), DATEADD(s, DateTimeInt, '1970-01-01 00:00:00'), 1) AS CallDate, COUNT(*) AS CallCount, SUM(CONVERT(numeric(18, 0), RoundDuration) / 60) AS DurationFROM CDRSWHERE (DateTimeInt BETWEEN 1253491200 AND 1254095999) 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-01 : 14:29:22
|
| is the updation happening once per day? if yes, you may drop the index before updation and create it afterwards |
 |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2009-10-01 : 14:35:15
|
Yes I do this update everyday. I will try doing that. I just takes so long to do it that way but I guess that is the only way around it.Thanks,Nickquote:
Originally posted by visakh16
is the updation happening once per day? if yes, you may drop the index before updation and create it afterwards
|
|
|
|
|
|
|
|
|
SUM() Function