Compact Stats Table

Question

Compact Stats Table

shoope

SSC Rookie

Points: 30
More actions
August 31, 2003 at 8:40 pm

#81954

I have a stats table that is growing out of control. It basically looks like this
objectId UNIQUEIDENTIFIER
statsType INT
dtCreated DATETIME
Then I have procs that queary this data. I don't need it this granular so I added
hits INT, DEFAULT 1
and the INSERT now increments this number if the objectId EXISTS and dtCreated is Today. All the quearies simple SUM(hits) and it works great. It's even backwards compatible.
Here's my problem and question.
How can I go back and effectivly compress the table, by adding up all the objectId's by day, and incrementing the [hits], then delete the duplicates?
This will dump about 400K rows from the table, so it needs to be done ASAP.
Thanks in advance.
Scott

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

Hopefully this is not to far of the target



Set Nocount On

Create Table TMP(objectId UNIQUEIDENTIFIER,statsType INT,dtCreated DATETIME,hits INT DEFAULT 1)

GO

Declare @n UNIQUEIDENTIFIER

Select @n=NewId()

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,1,'2 Sep 2002 0:1')

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,2,'2 Sep 2002 0:2')

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,3,'2 Sep 2002 02:3')

Select @n=NewId()

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,1,'1 Sep 2002 18:1')

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,2,'1 Sep 2002 19:1')

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,3,'1 Sep 2002')

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,1,'1 Sep 2002')

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,2,'1 Sep 2002')

INSERT TMP(ObjectId,StatsType,dtCreated)  values(@n,3,'1 Sep 2002')

GO

Select ObjectId,

StatsType,

dtCreated,Sum(Hits) 

From (

Select ObjectId,StatsType,Hits,Cast(Convert(Char(9),dtCreated,107) as DateTime) 

From Tmp Where dtCreated < Cast(Convert(Char(10),GetDate(),107) as DateTime))

as MyDerived(ObjectId,StatsType,Hits,dtCreated)

Group by ObjectId,StatsType,dtCreated

Order by ObjectId,StatsType,dtCreated

GO

Drop Table TMP

GO

Set Nocount Off

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Did not add part to remove old data and to add the new aggregated data.

shoope SSC Rookie Points: 30 More actions · Answer 3

THanks,

This is right on topic. Not exactly the finished product, but it put me int he right direction.

I created a tmpTable, selected by group appropraitly into the tmpTable, deleted the rows, then instered the rows back into the tblStats.

Thanks for the point in the right direction.