SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Impact Analysis of Changing Table to View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLJames
Starting Member

30 Posts

Posted - 05/15/2013 :  12:04:09  Show Profile  Reply with Quote
I was asked to make some changes to an existing database, specifically I was asked to replace a table view a view and analyze the impact of the change.

The table (with PK, four columns) is populated with a procedure run on a schedule where as the view is a join between three tables (all with FK's indexes, etc). The justification is that the view is of current data where as the table populated from a procedure and is stale after it is run.

I determined the dependencies from the database - there were five procedures. For each, I generated the Query Plan with the table as the source and the view as the source for each SQL statement in the procedures. Comparing the two query plans does not give me an idea of the impact of the change only that the two have different query paths. I see that the view is using the right indexes on the joins and the where clause. No table scans. There is a difference however because the table is used as a lookup and the view uses three tables in a join. There will be an impact. Do you have any suggestions of how I can analyze the impact of the change from a table to a view? How can I compare the two to say the view will have a X percentage change vs. the table. Can I discern that from the query plan?

Thank you in advance for your consideration and time!

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 05/15/2013 :  13:51:15  Show Profile  Reply with Quote
In a development/testing environment create the view. Place query using the table and query using the view into the same SSMS query window, turn on execution plan (control-m) and run both together. In the query plan window, it should now show you the relative costs of each query.

Run the queries several times. Also, you might want to clear the query plans and run a few times (DBCC FREEPROCCACHE - but don't clear cache in production database).
Go to Top of Page

SQLJames
Starting Member

30 Posts

Posted - 05/15/2013 :  16:26:15  Show Profile  Reply with Quote
Thanks so much for the advice!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000