Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server Reports , +
---- 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 3-Jul-13 20:02pm
sam7one513
Edited 3-Jul-13 20:11pm
v2
Comments
Rohan Leuva at 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 at 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
good
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...
  Permalink  
Comments
sam7one at 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_ at 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
0 Maciej Los 205
1 Richard MacCutchan 185
2 DamithSL 170
3 Tomas Takac 146
4 OriginalGriff 132
0 OriginalGriff 5,130
1 DamithSL 4,237
2 Maciej Los 3,700
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,846


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 4 Jul 2013
Copyright © CodeProject, 1999-2014
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