SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Reporting Services Report taking LONG time to process


SQL Reporting Services Report taking LONG time to process

Author
Message
ramu.valleti
ramu.valleti
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 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....!
denglishbi
denglishbi
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2159 Visits: 1364
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
ramu.valleti
ramu.valleti
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 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....!
Arun Sathianathan
Arun Sathianathan
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 506
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.
denglishbi
denglishbi
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2159 Visits: 1364
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
ramu.valleti
ramu.valleti
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 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....!
denglishbi
denglishbi
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2159 Visits: 1364
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
ramu.valleti
ramu.valleti
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 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....!
denglishbi
denglishbi
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2159 Visits: 1364
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
denglishbi
denglishbi
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2159 Visits: 1364
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search