HI Database Query Performance Problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

HI Database Query Performance Problem

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
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
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.
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.