Hi Chris,

I have found that stored procedures execute faster than views prior to SQL 2000 and the introduction of indexed views. For some complex retrieval operations, stored procedures still run faster. Specifically, if lots of conditional code needs to be considered prior to retrieval and you cannot efficiently contruct it without passing long where clauses to the view (views do not accept parameters), you have to use stored procedures. However, if the the select statement can be efficiently constructed and passed, indexed views are quite efficient. Like many things, I have found that the answer to this question can only be solved by running the profiler on the slower queries to really get a definitive answer. In many cases, the difference is neglible and views provide a cleaner interface to the tables and since they are great for allowing end users to use in report writers, etc.. However, for complex retrieval operations, store procedures will almost always run faster. Here is a good article:

http://www.sql-server-performance.com/views.asp
http://www.winscriptingsolutions.com/Articles/Index.cfm?ArticleID=26605


Just my experience.

Cheers,
Bob

>I understand that once a stored procedure is run, the execution plan is stored on the server, but I think I remember reading that this is not true for views. Does that mean that generally, a stored procedure that returns a result set the same as a view will be faster than a view?