Click here to Skip to main content
12,399,561 members (60,296 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL SQL-Server performance , +
---- 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 --------

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 3-Jul-13 19:02pm
Updated 3-Jul-13 19:11pm
Rohan Leuva 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

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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...
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 4 Jul 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100