Share via

executing a sql server view vs running code in view

Scott Cabral 1 Reputation point
2020-12-10T11:51:27.983+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2020-12-10T13:14:56.643+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. Viorel 114.7K Reputation points
    2020-12-10T12:41:57.43+00:00

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

    0 comments
  3. Scott Cabral 1 Reputation point
    2020-12-10T13:53:16.803+00:00

    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)

  4. Scott Cabral 1 Reputation point
    2020-12-10T14:16:58.217+00:00

    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.

  5. Erland Sommarskog 107.2K Reputation points
    2020-12-10T23:03:21.867+00:00

    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.

    0 comments