Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?
You could loose a few milliseconds the first time the procedure is loaded into the procedure cache ifyou use the view, but the difference is too small to care about, and probably too small to measure.
Using views can make an application much simpler to understand, particularly if there are a lot of complex things going on in the SQL. I would recommend using views, as long as you don't go nuts with them. In other words, don't build a view as a SELECT * FROM myTable unless that somehow might buy you something down the road, but feel free to take that fourteen table join on forty columns and make that puppy into a view pronto!
Thanks for your reply Pat. The reason I asked is because I thought I read somewhere that a view is "compiled" each and everytime it is executed. This seems to be the equivalent of running a query where each time the query is run, it is first compiled then put into action.
The whole purpose of a stored procedure, is that the procedure is compiled once, when it is first executed, hence better performance is the outcome compared to sending a "SELECT ...." SQL statement.
So, it seems that using views would be a performance loss comparable to sending a SQL statement rather then using a stored proc.