Menu
I am trying to alter an existing view in my SQL Server database. When I run the query by itself it finishes in about 4 seconds. When I run the alter statement with the same query it runs and never finishes (waited 15 minutes before stopping it).
I do not have any indexes on the view I am trying to alter. Any ideas what would cause something like this to happen? Is there something I should be doing differently to speed things up?
553
When SQL Server processes a SELECT from a view, it evaluates the code in the view BEFORE it deals with the WHERE clause or any join in the outer query. With more tables joined, it will be slow compared to a SELECT from base tables with the same results.
Reduce nested views to reduce lags This nesting causes too many data returns for every single query, which either makes the database crawl, or completely give up and give no returns. Minimizing nesting is a simple way to make your SQl query efficient and significantly improve speeds.
Make sure there's no contention for that view. If something else is accessing it, or if there's a spid somewhere that's idle but has a connection to it, you may be blocked from the ALTER statement.
A simple sp_who2 active during the ALTER should give you the culprit.
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
