SQL Server : view MUCH slower than same query by itself

Question

From this SO answer a view should provide the same performance as using the same query directly.

Is querying over a view slower than executing SQL directly?

I have a view where this is not true.

This query targeting a view

SELECT 
    * 
FROM 
    [Front].[vw_Details] k
WHERE 
    k.Id = 970435

Takes 10 seconds to complete. Copying the query from the view and adding WHERE k.Id = 970435 to it completes in less than 1 second. The view is nothing special, 4 LEFT JOINs, and a few CASE directives to clean up data.

How can I figure out what the problem is, or what do I need to complete this question with in order for this to be answerable?

Update 1:

• SQL Server Version: 12.0.4436.0
• Query plan for view: https://pastebin.com/RY40Ab0k
• Query plan for select: https://pastebin.com/gwahhgpu

Answer 1

Your query plan is no longer visible, but if you look in the plan, you will most likely see a triangle complaining about the cardinality estimation and/or implicite declaration. What it means is that you are joining tables in a way where your keys are hard to guess for the SQL engine.

It is instant when you run from a query directly, probably because it doesn't need to guess the size of your key is

For example:

k.Id = 970435 

SQLSERVER already knows that it is looking for 970435 a 6 digit number. It can eliminate all the key that doesn't start by 9 and doesn't have 6 digits. :)

However, in a view, it has to build the plan in a way to account for unknown. Because it doesn't know what kind of key it may hold.

See the microsoft for various example and scenario that may help you.

https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver15

If you are always looking for an int, one work around is to force the type with a cast or convert clause. It's may cause performance penalty depending on your data, but it is a trick in the toolbox to tell sql to not attempt the query a plan as varchar(max) or something along that line.

SELECT * 
FROM  [Front].[vw_Details] k
WHERE TRY_CONVERT(INT,k.Id) = 970435