SQL Reporting Services Report taking LONG time to process

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » SQL Reporting Services Report taking LONG...

15 posts, Page 1 of 2

1 2 »»

SQL Reporting Services Report taking LONG time to process

Rate Topic

Display Mode

Topic Options

Author

Message

ramu.valleti

Posted Thursday, April 16, 2009 8:03 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 07, 2013 12:16 AM
Points: 381, Visits: 340

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

Post #698464

denglishbi

Posted Thursday, April 16, 2009 3:22 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:09 AM
Points: 347, Visits: 1,339

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

Post #698942

ramu.valleti

Posted Friday, April 17, 2009 12:15 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 07, 2013 12:16 AM
Points: 381, Visits: 340

Thanks a lot for the immediate response.

Here are the Details of my SQLServer,

Edition Enterprise Edition
ProductLevel SP3
ProductVersion 9.00.4035.00
ResourceVersion 9.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....!

Post #699130

Arun Sathianathan

Posted Friday, April 17, 2009 2:40 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:46 AM
Points: 464, Visits: 431

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.

Post #699193

denglishbi

Posted Friday, April 17, 2009 4:40 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:09 AM
Points: 347, Visits: 1,339

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

Post #699269

ramu.valleti

Posted Friday, April 17, 2009 4:59 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 07, 2013 12:16 AM
Points: 381, Visits: 340

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

Post #699276

denglishbi

Posted Friday, April 17, 2009 5:14 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:09 AM
Points: 347, Visits: 1,339

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

Post #699278

ramu.valleti

Posted Friday, April 17, 2009 11:48 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, March 07, 2013 12:16 AM
Points: 381, Visits: 340

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

Post #699667

denglishbi

Posted Friday, April 17, 2009 11:56 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:09 AM
Points: 347, Visits: 1,339

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

Post #699673

denglishbi

Posted Friday, April 17, 2009 11:59 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:09 AM
Points: 347, Visits: 1,339

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

Post #699676

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions