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