Dear colleagues
Currently I am facing a very interesting situation where I need to optimize a special process of querying. The query need to get information from a huge table with around of 75 million of rows, but it's mandatory to get the information from the begin of the time, for this, but each month we could make a type of "closed" of the total amount for this month, because part of the query make a delta or differential between the current and previous month and I only need to calculate totals for each month (but with a lot of combinations).
I have some possible ideas for reducing this time of processing which is repetitive and not well:
1) Create a special process of aggregation with stored procedure which would process and closed with the totals for each month for storing in a new table of aggregation.
2) Partitioning the huge table taking a partition function who must take a range of time for the partition.
I would like to hear some ideas or piece of advises for trying to solving this scenario.
Thanks.