Should I use stored procedures for reports in SQL Reporting Services

Question

Hello,

When I write report for SQL Reporting Services I put SQL-script inside my report (rdl). When I run this script in SQL Management Studio it takes about 1 second, but report are generated very long time (Sometimes report throws exception about timeout).

Should I write stored procedure and use it in report? Will it be faster?

And one more question - When SQL-script returns a lot of rows, does report get all rows and show only one page, or it takes only one page from SQL-server?

Thanks in advance.

Answer 1

I'd use stored procedures but not for any speed reasons (I doubt that it will make any difference). It is much easier to refactor your database if you know all your calling code is in stored procedures and not buried in rdl files.

From what I remember about reporting services the rendering step itself can be quite consuming and it can time out on the rendering rather than the data retrieval.

Have you tried using SQL Profiler whilst viewing the report to see how long the gap is between the statement getting the data showing as completed and the report being rendered?

Answer 2

Stored procs vs ad-hoc/dynamic sql: http://www.codinghorror.com/blog/2005/05/stored-procedures-vs-ad-hoc-sql.html

If data is relatively unchanged between the report being run, you may wish to consider caching: http://msdn.microsoft.com/en-us/library/ms155927.aspx

I personally favour procs as it makes reuse easier.

You can tap into the RSExecutionlog/(or not!) to see what is going on in terms of how long things are taking to process: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/804174a9-0f22-49a7-93f2-be4dbb3c0a4d