snippetsqlModerate
How can I prevent SSMS from re-writing my code when saving a view?
Viewed 0 times
preventcanssmswritingviewhowcodefromsavingwhen
Problem
I'm creating a view that uses a statement with a
which takes an average of 10 seconds to run. However, when I try to save this query as a View, SQL Server (or the MS SQL Server Management Studio Client) "optimizes" the query to use this structure, instead:
Slowing down the query to 6+ minutes. Is there any way to disable this behavior, so that the view uses exactly the SQL Query I give it?
WHERE clause similar to this:WHERE
(
col1 IS NOT NULL
OR
col2 IS NOT NULL
)
AND
NOT EXISTS (SELECT ...)which takes an average of 10 seconds to run. However, when I try to save this query as a View, SQL Server (or the MS SQL Server Management Studio Client) "optimizes" the query to use this structure, instead:
WHERE
(col1 IS NOT NULL AND NOT EXISTS (SELECT ...))
OR
(col2 IS NOT NULL AND NOT EXISTS (SELECT ...))Slowing down the query to 6+ minutes. Is there any way to disable this behavior, so that the view uses exactly the SQL Query I give it?
Solution
Don't use the SSMS wizards.
just type your
This will stop it rearranging the query syntax to semantically equivalent but worse performing alternatives.
just type your
CREATE VIEW dbo.Foo AS SELECT ... statement into a new query window and execute it. This will stop it rearranging the query syntax to semantically equivalent but worse performing alternatives.
Context
StackExchange Database Administrators Q#27970, answer score: 14
Revisions (0)
No revisions yet.