Take the 2-minute tour ×
Webmasters Stack Exchange is a question and answer site for pro webmasters. It's 100% free, no registration required.

We use SQL reports in a .NET web application, but some of our reports are heavy and involve more table join and columns.

As you know we cannot index all the involved columns (performance problem).

These reports impact web application performance. Now we are running reports against the same database that is serving web application.

What are some solutions to have reports on a separate database?

share|improve this question
    
Before anyone votes to close this, I'd note that this is an issue that a lot of webmasters have to face, but unlike DBAs, webmasters tend to be less aware of the need for proper datawarehousing practices, so I think it's good to have this question brought up on this particular SE. –  Lèse majesté Aug 17 '12 at 10:40

3 Answers 3

It's pretty standard practice to have a data warehouse separate from your application database to run reports off of. This keeps your app from slowing down when generating reports and allows you to keep more data and in a more suitable (denormalized if necessary) format for running reports.

It's analogous in principle to Command Query Separation/CQRS in programming. So your application database is optimized for recording and high transaction volumes (fewer indices, smaller index sizes, normalized schema, sharding, and other optimizations that make sense in an OLTP scenario), whereas your data warehouse is an OLAP database optimized for reading and running reports, and will probably use a star or snowflake schema or a denormalized schema, make use of aggregates, etc.

share|improve this answer

Why not setup a version of the database on another server, simply have the two replicate every 12 hours, and you can then run your reports without killing the live server.

share|improve this answer
    
yes,surely as soon as we will separate reports to another server, but some reports has complex queries and multiple join, SO migrating to another server although will help the application but steel the report server will be slow. i think it is better to have some custom denormalize table with more columns that serve complex queries. but i don't know that sql server has any embeded mechanism to doing this or if i myself had to fill this table? i dont know how? is any other application i should setup to do this ? –  Memmed Apr 18 '12 at 11:25

What about creating views for the reports you need instead of rerunning all the joins?

share|improve this answer
1  
If you're not using indexed views, views could potentially worsen performance. –  Lèse majesté Aug 17 '12 at 10:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.