Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
---- QUESTION START --------
If you have a report in your web application, your are loading data to this report from SQL SERVER db and this report is having performance issues like taking forever to load, affecting entire website.

What steps you would take to resolve the slowness issue and make the report load as quickly as possible.
----- QUESTION ENDS --------


----- FURTHER EXPLANATION ---
I have been asked above question in several interviewS, I answered this question as per my knowledge and experience. but I think it did not impress interviewer. Its a very IMPORTANT question..

Hence I would like to know answer of this question as per your PRACTICAL EXPERIENCE, EXPERTISE and WORK KNOWLEDGE.

I don't want to search this question on google, because I know part of answer based upon my knowledge. But I want to see if their is a room of improvement for this question.

So, I would appreciate answers based upon your expertise and personal experience rather than posting some google links.

Thanks in advance
Posted
Updated 3-Jul-13 19:11pm
v2
Comments
Thanks7872 4-Jul-13 1:31am    
I don't want to search this question on google,Why? Let me tell you google is capable to solve almost every issue now a days either its technical or non technical.I dont know why you are afraid of using it,but as per my personal views,for a professional,Google should be the first friend... :)
sam7one 4-Jul-13 3:03am    
I am not afraid of using google, infact, I (or we all) use google. But for this question I need an answer based upon work experience of individuals who worked on reporting, web applications and have faced such a problem. On google, I won't get a specific answer, Here if memebers of CODEPROJECT answer this question, It will help me as well as entire community of developers who seeks their answer on GOOGGLE or code project or etc...

In this way I will have versatile range of answers and evantually it will be searched through GOOGLE...and helpful for all

1 solution

Option 1. Ensure your report is only returning the data you require, not all data... ie: let SQL Server select the correct data through the appropriate use of a where clause/parameters in the stored procedure.

Option 2. If the report itself requires a massive amount of data, and that data can afford to be slightly out of date, have a replicated database, using the replica to generate your huge report on (so that you aren't using the main server).

Option 3. Have your SQL Server and your IIS on a different physical machine... One user running a report on SQL Server shouldn't affect every user of your web system...
 
Share this answer
 
Comments
sam7one 4-Jul-13 3:12am    
What if Client can't afford having DB and IIS on different server or separating IIS and DB server on different server still does not resolve the issue, what would be the solution.

Suppose a project is hosted using 2 web servers and a database server, we are having issue with one of the major report, this report displays several colums with heavy joins on multiple tables. then what will be your solution.
_Damian S_ 8-Jul-13 20:00pm    
In that instance, the solution might fall under Option 2 above... alternatively, you could run the reports after hours and stash the data in a temp table for querying, with the data refreshed nightly.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900