Reporting System architecture for better performance

Question

Hi,
We have a product that runs Sql Server Express 2005 and uses mainly ASP.NET. The database has around 200 tables, with a few (4 or 5) that can grow from 300 to 5000 rows per day and keep a history of 5 years, so they can grow to have 10 million rows.
We have built a reporting platform, that allows customers to build reports based on templates, fields and filters.
We face performance problems almost since the beginning, we try to keep reports display under 10 seconds but some of them go up to 25 seconds (specially on those customers with long history).
We keep checking indexes and trying to improve the queries but we get the feeling that there's only so much we can do. Off course the fact that the queries are generated dynamically doesn't help with the optimization. We also added a few tables that keep redundant data, but then we have the added problem of maintaining this data up to date, and also Sql Express has a limit on the size of databases.
We are now facing a point where we have to decide if we want to give up real time reports, or maybe cut the history to be able to have better performance.
I would like to ask what is the recommended approach for this kind of systems.
Also, should we start looking for third party tools/platforms? I know OLAP can be an option but can we make it work on Sql Server Express, or at least with a license that is cheap enough to distribute to thousands of deployments?

Thanks

Answer 1

We face performance problems almost since the beginning

Before your tables got large? This makes me think that you have underlying problems in your reporting application or SQL queries. Do these wait times happen with only 1 user on the system?

Have you used SQL trace to document long running queries and fix them? How have you gone about adding indexes?

There is the open source OLAP suite - http://www.pentaho.com/index.php But I cannot vouch for its ease of use or performance.

Answer 2

Query execution in a case like this can be improved by maintaining a denormalized version of your database. SQL Express does not offer much in the way of BI "out of the box," so your best option (within the constraints of SQL Express) is to design a solution manually.

This means designing a denormalized version of your database, to which you export data for reporting. Be aware that denormalized databases take up a lot more space. Here is a book that deals with designing data warehouses.

You should also look into architectural options that would centralize the data in a fully-featured RDBMS.