Why would updating a view cause a speed up?

Question

We have a peculiar situation with a particular query.

This query joins to a view which creates a PIVOT on some data.

Now we are finding at some times this query runs really slow (10 seconds). I havent got a handle on how to consistently reproduce it running slowly.

However, when it runs slow we can drop and recreate the View joined in the query and this will get the query running quickly again (< 1 second). Now this UPDATE VIEW does not change the actual schema of the view in any way.

I am confused. Any ideas as to what could be going on here?

What happens whilst recreating a view that could speed up the query?

Answer 1

This sounds like parameter sniffing.

On recreation, it's a new object and a new plan. Even though the view is expanded, the query text refers to the view and it isn't the same view as before

Answer 2

I have seen this issue before, and it is a strange one. What happens when you recreate the view, SQL Server recreates the query execution plan used by the view. Over time the queries in your view probably have joins on tables with bad indexes so the SQL statistics on the indexes actually start degrading the performance rather than helping it out. So once you recreate the view, SQL Server now identifies the best query execution plan for the view with the given statistics and that is why you see the view running faster again. Then again, once the table statistics start increasing the view starts to reduce in performance since SQL Server doesn't know the best way to run the query execution plan for the view. I would suggest that you review the tables in your view, and investigate the joins and indexes for the tables that are being used. If you execute the contents of the view in a different query window you would be able to compare the estimated query execution plan for the query that is run by the view, and the estimated query execution plan used by the view. With this you can identify the problematic query.