Report to be build from Very large amount of data.

Question

Report to be build from Very large amount of data.

dkhandka

SSC Enthusiast

Points: 186
More actions
February 4, 2010 at 11:05 am

#218830

I have a Web reporting tool which takes data from a view. This view is based on one main transaction table which has very large amount of data. Size of this table is more than 25 giga bytes. Problem is that we have four such table for each year and report is generated from the historical data since 2004. So we have total of 24 such tables used in a union inside the view. View again performing aggregation in the select statement. When I try to run the report which is using this view it is taking a lot of time even after having all the indexes.
I am using SQL Server 2008.
So I wanted to know what strategy I should use to build the report out from this large amount of data.
Thanks
Deependra

Viewing 10 posts - 1 through 9 (of 9 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Warehousing is probably where you have to go to. Does the data change for Old records? If not why not have a pre populated summary table? You can have a job that runs every night that populates a table as per your business needs.

-Roy

dkhandka SSC Enthusiast Points: 186 More actions · Answer 2

Thanks for your valuable comments.

Data does not change for the historical records. Actually historical records are the main problem, they comprise bulk of data.If I try store aggregate data to new tables, they take a lot of space. I tried that for just one year's data and ended up with disk full. It was taking more than 100 GB data.

Thanks

Deependra

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Have you looked up on Indexed views? here is a bit that might interest you about indexed views. (taken from MSDN)

* Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.

* Tables can be prejoined and the resulting data set stored.

* Combinations of joins or aggregations can be stored.

Read more about it if you feel that this could help you here.

http://msdn.microsoft.com/en-us/library/dd171921.aspx

-Roy

dkhandka SSC Enthusiast Points: 186 More actions · Answer 4

To create an indexed view there should not be any union, intersection etc and we can refer only two part name in the indexed view. Data for a year has been divided into four quarters. So I can not combine whole year's data into one indexed view.

Still I will try to create indexed view for each quarter and if I am able to gain performance and also able to save disc space.

Again thanks for your help.

Deependra

dkhandka SSC Enthusiast Points: 186 More actions · Answer 5

I can't create indexed view because there are many conditions that should match.

So if there is any other way to solve my problem , please let me know.

Deependra

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

Are your tables normalized? I am just wondering why you would have single table with 25 gig of data for just one year.

I think your best option is to go to the route of Data-warehousing.

-Roy

dkhandka SSC Enthusiast Points: 186 More actions · Answer 7

Table is normalized and and it is very big because of large volume of data. I am not joining only 1 other table which is a reference table in the view.

Porblem is that we have a web tool for report which uses view for its data source. So I can't go to create the OLAP cube.

Thanks

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 8

If, as you say, the historical doesn't change, then just pre-aggregate it and store it in a reporting table. If you're careful about how it's done, then there's no need for OLAP cubes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dkhandka SSC Enthusiast Points: 186 More actions · Answer 9

Thanks for your suggestion.

I agree with you pre aggregation looks the best solution for my problem.

Thanks

Deependra