Advices for reduce the process of querying a huge table

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.

September 9th, 2015 4:20pm

It seems that this is the job of SSAS cubes or even for Tableau. The idea of creating a partition is helpful when you're wanting to limit the data your accessing but in this case you'd be looking at everything every month unless you pre-aggregate the data. You can certainly do this with a table and pre-aggregate the data in the many ways you want to see it but you could also load the data into a DW and then build something for the longer term.

I think the partitions will help with your pre-aggregation because you will only ever need to pre-aggregate the current month, since you would have already done the work for each month prior. Partitioning helps in that scenario because you can cut the number of rows being queried significantly.

Tableau seems to be quite popular these days. I'm not an expert with Tableau but I'm pretty sure it can store your data internally on its server so you wouldn't need to keep the pre-aggregated information. I know a few of my customers use Tableau to crunch 10+ TB of data for some reports.

Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 5:13pm

Thanks for your answer Daniel, but I forget to say that for different reasons the solution will be hosted in our main Database, for that the use of a BI solution not apply.
September 9th, 2015 5:24pm

Thanks for your answer Daniel, but I forget to say that for different reasons the solution will be hosted in our main Database, for that the use of a BI solution
Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 5:35pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics