Reporting Services Performance Issue with Store Procedures VS SQL Query

Question

Reporting Services Performance Issue with Store Procedures VS SQL Query

Jojomay

SSC Rookie

Points: 42
More actions
August 31, 2006 at 6:49 am

#170230

Hi,
I have build some reports using VS 2003 and SQL 2000. I'm using embedded SQL code in report designer to generate report. The process to generate report is quite slow. So, is it the performance issue will be improve if I move my SQL query to store procedures?
Thanks for the help!!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

moe_mayne SSCommitted Points: 1933 More actions · Answer 1

instead of useing embedded SQL use Stored Procedures and use with(nolock) on joins ... as well read up on Sargable clauses and make sure you got your indexes where u need them... the list goes on for performance mann its all about the way you write you SP and manage your indexes

Moe C

Martin N Ten Centuries Points: 1216 More actions · Answer 2

SProcs will give advantages in terms of security control, as the user shouldn't need direct select permission on the table(s). Also if the query plan is cached then you'll get better performance... however if the query plan drops out of the cache (there's a finite amount of room) then performance will drop away until SQL caches the query plan again.

To improve performance more can you pre-build some of the data you need? Rather than build it up in the report? I assume this is why your report has a long execution time? Indexes as Moe suggests will also help.

Kindest Regards,

Martin

Jojomay SSC Rookie Points: 42 More actions · Answer 3

Hi MArtin,

May I know what you mean with pre-build some of the data?

Thanks.

Regards,

-Yan-

Hans Lindgren SSChampion Points: 10496 More actions · Answer 4

He probably means caching or building 'summary' tables (or materialized views)...

Martin N Ten Centuries Points: 1216 More actions · Answer 5

If you are using a data warehouse / mart can you simplify your query by building tables overnight. Then you can use the server to process your calculations when business activity is low.

Reporting queries often run poorly on OLTP systems which are designed for quick insert, update and delete transactions... So if you can prebuild specially designed reporting tables they can really help performance...

On the other hand you may have no choice, depends on what you are allowed to do.

Kindest Regards,

Martin