Tuesday, January 19, 2010

Wide Table...

I had a SSRS Report in SQL Server 2008 which using a table which a lot of columns. The columns in the table kept getting added as requirements kept changing/modified. I hit a point where the report performance slowed and decided to investigate the root cause. Ideally this situation should not have arised if the design of the table was done properly. The table which SSRS report was using had more than 300 columns, the reason this happened table was designed as a flat table. I decided to break this table out into a category master table and a category metrics table. The master table was a like a dimension table which had all the category id's and the corresponding category names. The metric(fact table) had the id's and the metrics for each id. In order to populate these table i wrote a stored procedure and this stored procedure also had to feed the data to my report. Given that my inital flat table with the lot of columns was populated, the performance of the report using the  dimension fact table setup was very much faster. The time that the report was taking earlier before the design was close to a minute, after the table split the run time was around 15 seconds. Based on this exercise what i learnt was that it is not necessary a wide flat table with huge number of columns will give better performance.

No comments:

Post a Comment