April 16, 2009 at 8:03 am
Hi all,
I've a query which is taking 10 to 20 sec when i execute it from SSMS.
But when i execute the Report which is developed using the same sql query from report server, is taking 2 min to process.
What could be the possible reason for the report server to take long time to execute the same query and why it is not taking that much time in management studio?
Please suggest me how to improve the performance of report server..!
Regards,
Ramu
Ramu
No Dream Is Too Big....!
April 16, 2009 at 3:22 pm
Probably need some more information in regards to this to offer more advice. Are you using all SQL Server 2005 components? What version (build) are all of the components? Are they running on the same server or in a distributed environment? How does the report run locally from BIDS? Are you able to run the dataset query without issues in BIDS, but the report generation is taking longer? Are you trying to output this to a particular format or just running this in Report Manager (HTML)? Is there anything special going on with the report like Images embedded? Have you reviewed the Profiler trace when executing this against the SQL Server? Are you using a stored procedure as the data reference or just using embedded T-SQL? Have you run a network trace utility or reviewed the traceroute to see what is going on as far as your network is concerned?
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
April 17, 2009 at 12:15 am
Thanks a lot for the immediate response.
Here are the Details of my SQLServer,
Edition Enterprise Edition
ProductLevel SP3
ProductVersion 9.00.4035.00
ResourceVersion9.00.4035
Yes, all are running on the same server.
Yes, i can see the result of the query in 10 to 20 sec in BIDS as well as Management studio.
I'm just running this in Report Manager (HTML) or Reportserver.
It is a normal chart report and its just using embedded T-SQL.
I've not used any network trace utility, can u suggest me how to use that?
Hope these details will help you to understand the problem.
Please let me know if you have any suggestions.
Thanks,
Ramu
Ramu
No Dream Is Too Big....!
April 17, 2009 at 2:40 am
Check the executionlog table to determine if the time is spent on data retrieval or processing or rendering. If your resultset is very large, eventhough the query results may be returned faster, the processing time of the report will be high.
April 17, 2009 at 4:40 am
If everything is on the same server then most likely not a network related issue. How large is the dataset? Does the report generate quickly in BIDS, but just slow on the actual Report Server? How much memory do you have on the server and have you monitored the resources on the server (CPU, Memory, etc.) while you are running the report. Since everything is running on the same server and maybe there is even more running on it you are experiencing some resource issues. What else is installed on the server and are you experiencing issues with other reports? Have you tried moving the logic to a stored procedure and test it that way?
If you ever need to use a network trace utility you can use Wireshark[/url] and you can also utilize the traceroute by doing a tracert or tracert from your cmd prompt.
TRACEROUTE
Traceroute tracks the path that a packet takes from your computer to a destination address. A traceroute also shows how many times your packets are being rebroadcast by other servers until it gets to the final destination.
Ping and Traceroute
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
April 17, 2009 at 4:59 am
I've checked the Execution log, there most of the time it is taking for data retrieval only.
Report is fast in BIDS and its very slow in reportserver.
I'm checking in both the places one after the other, always BIDS is faster and reportserver is slower.
I tried changing to a stored procedure, its even more worse(taking almost double the time).
If it is resource issues, why it is taking long time in reportserver not in BIDS.
And i've almost 30,000,000 rows in the table.
Is there anything wrong with IIS?
Thanks,
Ramu
Ramu
No Dream Is Too Big....!
April 17, 2009 at 5:14 am
The big difference would be that everything is running on the single server and you are going through IIS with the Report Server. I would monitor the resources on the server. Are you really returning 30 million rows of data for the report or is that just the table that you are querying?
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
April 17, 2009 at 11:48 am
Its just the table having rows.
Returned rows are 4 or 5 aggregated rows based on certain conditions.
But i'm running only reportserver, no other applications are running on server.
As of now i'm the only one accessing reports, soon users will access the application.
But if the no of hits are more, the report performance will be even worse right?
Regards,
Ramu
Ramu
No Dream Is Too Big....!
April 17, 2009 at 11:56 am
But how many rows are being returned and it sounds like the aggregation is being performed in the table with grouping, so that will add some overhead. Look at performing the aggregation ahead of time if you don't need the detail.
You are running SQL Server, IIS, Report Server, ... and what else on the server. It is just not Report Server. Monitor the resources on the server and run a Profiler trace on SQL Server to see what is going on. Because of the volume of data you might need to look at making some modifications to the packet size possibly in IIS or test running the report with a smaller dataset.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
April 17, 2009 at 11:59 am
I would also look at your database and make sure the statistics are updated and that you have proper indexes setup. Perform the heavy lifting in the database and not in SSRS to get at your aggregate values. The stored procedure should definitely be an improvement over the embedded T-SQL.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
April 13, 2010 at 1:19 am
has there any resolution on this issue? We are having the same issues with rendering reports from a sql querie on IIS. Managers are complaining that it slow as a turtle gettng just a month's worth of data.
April 14, 2010 at 2:38 am
Hi,
I have actually been dealing with the same issue over the past 24 hours and this is how i resolved it.
Firstly I added some proper indexes on the database, I was still having speed issues. Then i disabled parameter sniffing on the query.
Basically ssrs sniffs the parameter coming into a report and can sometimes produce a convulted execution plan based on those parameters. To disable parameter sniffing you should create a local variable within your sproc or sql statement and assign the parameter to that instead.
I.E If you passed a variable from your report called @customerId and your query looked like this
select * from customer where id = @customerId
Change it to
declare @localCustomerId varchar(10)
set @localCustomerId = @customerId
select * from customer where id = @localCustomerId
This will produce a much more efficient execution plan if parameter sniffing is the problem.
Also if you are using a sproc try setting nocount on
Hope this helps !
Cheryl
December 8, 2011 at 12:20 am
Hi Cheryl Hanlon,
It's quite terrific point that you have pointed out. It literally saved me from a disaster today. I was facing the same issue and by replacing parameters with local variables, it rendered my data within 5 seconds. Awesome.
Now I have to go back to all my SPs to get this implemented.
Thanks so much for this point.
Vaidy Mohan
January 11, 2012 at 9:51 am
Hi,
How did this issue get rresolved?
I am facing a similar problem now but could not find any solution.
I've a stored procedure which is taking 22 sec when i execute it from SSMS.The number of rows being returned are 2962.
But when i execute the Report which is uses the same stored proc from report server, is taking 3 min to process.
I have checked the report execution log and it shows more time for data retrieval but not rendering or processing.
My report does not have any aggregations or groups .there are no filters and yes I have handled parameter sniffing but that did not help much.
I am displaying the data returned by data set in a simple table(tablix) control without any grouping or sorting.
The time taken to load the report is increasing with the increase in number of rows being returned by the stored procedure.
Also,I dO not have any paging in my report as per the requirement.all the records are displayed in single page. The interactive page size is set to 0
What could be the possible reason for the report server to take long time to display the report.
March 1, 2013 at 11:47 am
Thats a great solution but i have a problem, my parameters have multiselect option setting local parameters is giving me error, can any one help... please
Query -
DECLARE @LOCAL_VAR_ORGN VARCHAR(MAX)
SET @LOCAL_VAR_ORGN in (@Organisation)
DECLARE @LOCAL_VAR_BL VARCHAR(MAX)
SET @LOCAL_VAR_BL in @BusinessLine
DECLARE @LOCAL_VAR_SE VARCHAR(MAX)
SET @LOCAL_VAR_SE in @SalesExecutive
selectvd.Month,
vd.month_No,
vd.Year,
vd.Week,
vd.Date,
vd.Organisation,
vd.[Business Line],
vd.[Sales Executive],
count(distinct vd.CSTMR_KEY)[Total no of Lines],
(
case when SUM(vd.[Total Amount Due])>0
then COUNT(distinct vd.CSTMR_KEY) else 0 end
)[Total No Of Unpaid Lines],
SUM(vd.[Total Amount Due])[Total Amount Due]
from IV_DBT_RPT_FR_ORGNSTN VD
where (vd.Date>=@FromDate and vd.Date<=@ToDate)
AND VD.Organisation IN (@LOCAL_VAR_ORGN)
AND VD.[Business Line] IN (@LOCAL_VAR_BL)
AND VD.[Sales Executive] IN (@LOCAL_VAR_SE)
group by vd.AR_BHVR_KEY,
vd.[Business Line],
vd.C,
vd.CNTRCT_DD,
vd.CSTMR_KEY,
VD.Year,
vd.Date,
vd.month_No,
vd.Month,
VD.Week,
vd.Organisation,
vd.[Sales Executive]
--order by VD.Date
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply