Re: Are selects defined in view faster than run-time selects?
Date: Sun, 7 Apr 2002 12:59:36 -0400
Message-ID: <3cb07e21_1_at_news1.prserv.net>
Dragan,
A view (other than an indexed view) is simply a pre-programmed SELECT. When you send a query to SQL Server that references a view, the SQL Server engine takes the definition of the view and "plops" in it in place of the view. So there is no performance gain, in any way, shape or form, by using a view. One could argue that there is in fact a very minor performance degradation because SQL Server has to access the view definition from syscomments and do the work to replace the view with the view definition, but this performance degradation is going to be very minor.
But, bottom line, as re: your question. using a (non-indexed) view will NOT provide a performance gain.
However, SQL Server 2000 supports "indexed views", and an indexed view, can provide, under appropriate circumstances, significant performance gains. An "indexed view" is not just a pre-programmed SELECT, but is, in fact, a lot more like a trigger than maintains a fully materialized "copy" of the data. One can think of an indexed view as creating a trigger on the tables referenced in the indexed view, and populating and maintaining a separate table. Indexed views are very useful when dealing with aggregated data.
Additional details about indexed views can be found in the SQL Server 2000 Books Online.
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.
"Dragan Matic" <gekko_at_eunet.yu> wrote in message
news:3CB00F87.3040807_at_eunet.yu...
> Are selects that are defined as views faster than normal selects? I
> am asking this because I have a big select where I retrieve data from
> multiple tables. Is such a select going to work faster if it is defined
> as a wiew. I found nowhere in literature that views are speeding up
> selects but I recently heard such an opinion.
>
Received on Sun Apr 07 2002 - 18:59:36 CEST