VIEW for better performance ?

Last Post 20 Apr 2010 03:21 AM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sam g
New Member
New Member

20 Apr 2010 02:48 AM

We have a report which run 15 store procedures to fetch the data. This report takes 1 hours to process during non buisness hours and about 4 hours during buisness hours. The majority of data is pulled form two large tables by using a join. Each containing about 30 millows rows each.

To improve the performace, we are looking at the following options:

Create a view on these tables
Optimise the stored procedures

So I need some guidance in my action plan:
  • Put a trace to check how much time sp are taking to execute. -- any scripts to check the execution time ??
  • Create the view and check how much time the sp would take.
  • Any other alter ways to improve performance.
New Member
New Member

20 Apr 2010 03:21 AM
Why do you think a view will improve performance? You are better off trying to find ways to optimize the code in the stored procedures. If you are really processing 30 million rows you are most likely being blocked while trying to read them all. You might want to try using one of the Snapshot Isolation levels to reduce blocking. But it also sounds like the hardware is not up to the task either and you might want to see what is bottlenecked most and add resources to alleviate that. I agree that you should run a trace but don't concentrate so much on the time as the CPU or Reads. They will tell you how much work each step is doign where as duration can be affected by blocking so it doesnt tell you true indications of where the trouble is. Another question is why are you running this report on the production machine? If its that intensive maybe you should have a reporting server to run those on so you are not affected by and dont affect the regular users. As far as details I can't say much more because it all depends on what the issues are as to how you fix them.

Acceptable Use Policy