Why would a query be faster in SQL Server 2005 just because it's in a view?

Question

We have a (large) SELECT query, that can take ~30 seconds to run. I am told that when placed in a view, it takes less than 5 seconds to run.

My assumption is that SQL Server caches query plans for queries that don't change, so why the massive improvement in performance here?

Just to be clear, this really is just a case of taking something like:

select * from table /* Lots of joins, where clauses */

and making it a view:

create view myfirstview as select * from table /* Lots of joins, most of the where clauses */
select * from myfirstview where /* The rest of the where clauses (i.e. the ones that can change) */

Answer 1

Maybe what was told to you is Indexed Views. Materialized Views is the standard term

Answer 2

No, a view that is not indexed/materialised is expanded like a macro.

There is no special "view" magic despite what you have been told.

Answer 3

"I am told"..... have you checked and verified that yourself?

I'd be surprised, quite honestly.... a "regular" view is just a SQL statement that's stored - no caching, nothing

Unless they're talking about an indexed view ("materialized" view) which is really a system-maintained table in the end - the data is stored on disk like for a normal table.

This is no longer really a view - even though it's still called a view ;-) Those indexed views (with a clustered index) can be extremely fast - since they store the actual data that makes up the view like a table would.

Answer 4

I would wager that the person who told you this did a test something like this:

First, time how long the SELECT by itself takes Next, time how long the VIEW takes

The problem is that the SELECT causes SQL Server to read a bunch of data in from disk. Once in memory, the query will execute faster. You would notice a similar improvement if you just ran the SELECT twice, or if you reversed the order of the two queries above.

For a true apples-to-apples comparison, run the following commands before each query:

CHECKPOINT
DBCC DROPCLEANBUFFERS