Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Impact Analysis of Changing Table to View

Author  Topic 

SQLJames
Starting Member

35 Posts

Posted - 2013-05-15 : 12:04:09
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-15 : 13:51:15
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

35 Posts

Posted - 2013-05-15 : 16:26:15
Thanks so much for the advice!
Go to Top of Page
   

- Advertisement -