Store procedure & Views - Performance Clarification

[chanakya]

whats is difference between Store Procedure and Views ?
Is view is complied or not? If yes then which will execute fast for same query if you execute store procedure and views

Whats is Complied View

 

[hmckillop]

When a view is created, the name of the view is stored in the sysobjects table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the CREATE VIEW statement is added to the syscomments table. This is similar to a stored procedure; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.

There isnt a compiled view in my understanding. What you can do is create an indexed view, which creates a physical clustered index representing the view. This is highly performant on reads, but will slow the inserts down on the base tables.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[chanakya]

Thanks for the reply

If you can give me more clear idea about complied view you had written that
the name of the view is stored in the sysobjects table

so performance wise which is better and why?
and in single statement tell me "View is complied or not"

 

[hmckillop]

A view is compiled but each time its then recompiled.

A stored proc the query plan/tree will only be recompiled if a significant change in the expected outcome of the data is going to happen - so generally once compiled it stays the same.

Performance wise and in order (though a sweeping generalisation and based on all things are indexed appropriately)
Clustered Indexed View
Stored Procedure (well written)
View


"I'm living so far beyond my income that we may almost be said to be living apart