SQL Server Performance

Will VIEW improve performance

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sqlwars, Apr 20, 2010.

  1. sqlwars New Member

    Hi,
    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.
  2. FrankKalis Moderator

    Welcome to the forum!
    No, a view is unlikely to help you very much. A view is not much more than a macro that is expanded by SQL Server at runtime.
    Check the tables to see if they have proper indexes and that these indexes are used and then check the code of your SP's. Sounds like there are good chances to optimize things there significantly. If you want to find out which procedure takes what time, run Profiler and monitor RPC:Completed & SP:Completed and then take it from there by optimizing the slowest procs first.
    A run-time of 1 hour is a ridiculously long time and even 30 million rows is not considered a big table these days.
  3. satya Moderator

    VIEWs are always helpful that can hide actual columns of table whereby giving some security and they are optimized as well, as suggested by Frank it depends on relevant indexes and moreoever you can take help of INDEXED VIEWS for better performance.

Share This Page