SQL Server Performance

HI Database Query Performance Problem

Discussion in 'Analysis Services/Data Warehousing' started by amolnimkar, Dec 17, 2004.

  1. amolnimkar New Member

    HI,<br /><br />I have a view which pull out the data by making union of three other views<br />of same database. these three views again pull out data from many other<br />views. and one among the three view another union of two other views. and<br />these two views pull out data from different tablsew of same database. all<br />the views r from same database.<br />and i have written the store procedure create_tables. this procedure creates<br />different temp tables, views etc. but from same database.<br />I have put this sp in the job which runs every morning. Useually it takes<br />one hr to complete the job but now it take 7 to 8 hrs to complete.<br /><br />help me out with this problem<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Amol Nimkar<br />Software Developer
  2. thomas New Member

    Check for -

    up-to-date index statistics (run manually sp_update_stats if necessary)

    look at the execution plan. check for tables scans and create requisite indexes. run the whole thing through the Index Tuning Wizard and review its recommendations.

    Tom Pullen
    DBA, Oxfam GB
  3. stephenbryant New Member

    As per Thomas, I would also ensure that you have index references within your views to ensure you're getting the best performance from the first point of drawing the information.

    ie. select "yourcolumn1", "yourcolumn2" from "yourtable" (index("yourtableindex)) where....

    If your tables are indexed with the same primary the index reference will pull up the performance considerably.
  4. satya Moderator

    How abotu taking advantage of indexed views for optimum performance.
    Refer to the books online for mor einformation on indexed views if you're using SQL enterprise edition.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page