Aggregate Data to New Table

Question

Aggregate Data to New Table

thbaig

Hall of Fame

Points: 3006
More actions
December 17, 2024 at 11:08 am

#4499522
Hi ,
I am using webedition of 2019 MSSQL on AWS. I don't have SSAS or other license .
I have a big table that is growing and part of active read and DML operations. I have a stored proc that is applying SUM and count on few columns. as this table has data for many years, I am thinking to store these aggregates in new table. I can't use indexview as it will impact insert/update operation on table, i cannot have license of other options like SSAS.
My plan it to write a proc and schedule it to execute after every 5min. it is one time activity to execute query and generate aggregates for day-1 in new table. I am curious about current day data. What could be best approach to accomplish it ? if there are other options I could explore, please suggest i will read.
Following is simple table structure
```
OrderId - INT

lines  - INT

Amount - decimal

UserId - INT

CreatedDate - datetime
```
Following is simple query that is serving the needs
```
SELECT  userId,COUTN(OrderId),SUM(Amount)

from table

where CreatedDate >= InDate1

  and CreatedDate <=Indate2

group by userid
```

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

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

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 1

Something entirely different to look at would be to add the use of a columnstore index. Those things are specifically built to work with analytical style queries like this. You can add a nonclustered columnstore to your average clustered rowstore table pretty easily. Obviously, there are implications to insert/update/delete operations with an added index, especially a columnstore index. Further, you would need to deal with the fact that columnstore isn't the greatest when it comes to high volumes of data changes. You may, for example, need to rebuild a columnstore index more frequently to deal with the delta store within it. However, this means no new table AND no new data migration process where you write your data out multiple times. Instead, the index manages that for you.

I'd start there rather than a new table and all that implies.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

thbaig Hall of Fame Points: 3006 More actions · Answer 2

insert and updates to table are high. also re-creating columnstore will impose locks on table and that table is high in use. such activity could impact a lot 🙁

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 3

Well, doesn't sound ideal then. Still, might be worth a test.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Phil Parkin SSC Guru Points: 246977 More actions · Answer 4

For the current day, what data changes are being made? Inserts, updates, deletes or all three?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel