executing a sql server view vs running code in view

Question

Hi,

I have a view that was created in SQL Server. The view has about 20 joins some CTE's and a union. The code within the view runs and returns results in about 4-5 minutes. However, if I do a select * from the the view or select [column names] from the view, it doesn't finish. I let it run for 90 minutes and it never comes back with any results.

Any idea why the code within the view works but selecting from the view does not?

thanks

Answer 1

A SQL Server view is essentially a string macro for the SELECT statement. It does not "pre-compile" the view or anything else. So there should be no difference between running the view and running the select statement. I use views extensively and have done lots of performance testing and found no significant difference.

An "indexed view" is a little different. It is basically a virtual table with its own stats and indexes, etc.

Most likely your query was blocked by something, rather than a problem with the actual view. Look at the wait state is on the query when it is running. Also look at the query plan.

Please post the results of SELECT @@VERSION.

Answer 2

In case of indexed views, try *‘select * * * from MyView with(noexpand)’* and maybe *‘select * * * from MyView with(noexpand) option(recompile)’*.

Answer 3

select @@VERSION

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Answer 4

Hi Viorel-1 ,

I tried select * from viewname with(noexpand) but received this:

Msg 8171, Level 16, State 2, Line 2
Hint 'noexpand' on object 'dbo.v_fmg_claim_reserve_values' is invalid.

Answer 5

As others have said, the expected outcome is that the performance is the same, no matter you say SELECT * FROM viewname or you run the query within the view. However, since the query texts are different you will get different query plans, and with some bad luck also different query plans. This could not the least happen if there is some parameter sniffing going on (which it could be if sysdatetime() or getdate() is inside the view.)

I would suggest that you check the estimated query plans to see if they are the same or not.