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

Nat

Step #1 is the query plan. Performance many also depend on where/how this query is executed; plain SSMS queries likely promote through constant selection (which would not apply to a parameterized query or as a SP parameter). Constant selections can result in 'ridiculously different/better' plans. – Rattan

Anyway, tldr: while MSSQL considers the SQL inside a view as part of the original query/plan, it does not guarantee the same plan selection as a verbatim copy of said view - which is where the different plans and performance profiles come from. – Rattan

SQL version and query plans added – Nat

Search for 970435, which should show the 'issue'. In the fast/non-view case it is used in a seek (SeekPredicate). – Rattan

Hmm, could you possibly spell it out for me. I do see that the executionplan for the view has "parameterized" paramaters, but I dont know why that is bad or what I should do about it. – Nat

If running queries directly from SSMS, the "parameterized" issue generally doesn't apply (note how it used the constant value in the parameter), which can also make queries there significantly different from code. However, the plan difference is still real. The fast query uses an index seek first, to select only rows with ID=970435. The slower query does not; the slow query 'gets most of the data' (doing much more IO and possibly expanding multiplicity) before using a predicate filter on the 'working set'. The 'visual' mode of the query plans is usually simpler to at-a-glance check :} – Rattan

In this particular case, adding an index hint (or plan guide) may be appropriate. However, this may limit SQL Server's ability to choose a better query when not only looking a specific ID (or two). Another consideration, should be to ensure that the statistics are up to date. – Rattan

@JensB, do you get a good plan if you specify a literal when querying the view (like you did the non-view query)? SELECT * FROM [Bospar].[Front].[vw_Kontakt_Detaljer] [k] WHERE [k].[Kontakt_sk]=970435 – Woodnote

That query plan does not reflect the posted view query. – Dichroite

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://learn.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

Simmonds

That worked. I still don't understand why. I'd think the query engine would already know it's dealing with an int, even in a view. – Squilgee

Answer 2

use a stored procedure to return results. stored procedures use indexes, whereas, views often don't

or

use a table function and query the table function

Thereafter