I am guessing that this has been discussed already but I cannot seem to find any threads on the specific topic. I am trying to determine if there is reason to modify a bunch of store procedures that select rows from views. We have a large number of stored procedures that were generated via a DAL tool. The sp simply selects data from a view with a WHERE clause to filter the results. So the simple question is; is there a performance reason or best practice reason to NOT select data from views in stored procedures. I have ran some tests by substituting the reference to the view in the sp with the view's SELECT statement and there seems to be no difference in performance between the versions of the sp. However, it is possible that my tests are flawed in some way. Or is the SQL engine smart enough to merge the WHERE in the sp with the view's definition and not return all rows in the view before applying the selection criteria specified in the sp's WHERE clause? Thanks for any advise.
How are you measuring the difference in performance? Are you comparing the Execution plan? CPU Costs? I've found that we gained performance on a query by not using a view that was a simple select statement. I've read that it's best to use a view on more complex statements.
Thanks for replying. We have done tests on both complex and simple views and used profiler to check duration, reads, etc. For a simple test I created a view that simply returned a million rows in 10 seconds: Create View viewTest as SELECT * FROM TableA Then created an sp that selected a specific row from the view: Create sp as SELECT * FROM viewTest WHERE PKid = 334543 The sp executes in < 1 sec which leads me to believe that the result set from the view (1,000,000 rows in 10 secs) is not created first and then the row is selected. BTW, the above is not the exact test but just a psuedo explanation. In fact, I am really just looking for a general answer to the question of whether or not it is a good idea/practice to select data from views from within stored procedures. Does it matter or not? With this I am trying to determine if we should pursue this line of analysis or concentrate more on the efficiency of the views themselves. I have an associate that insists this is an issue and we should eliminate the use of views in sp's. Thanks again for any feedback.
There is no such rule carved on the stone that you should use VIEW and not SP, vice-versa. Based on the capacity planning and number of rows to return you can select either VIEW or SP based on the facility to upgrade the resources in future, if required. In my experence you are good to go with Stored procedures for better performance, also with INDEXED views for a long term association of performance in data presentation.
From what I've learned the first question to ask is why are you using the view. Is it for security reasons? I'll share a quote I just found which sums it up for me: A view is like a table, but SQL Server does not store the data, only the definition. Views are typically used for two primary purposes: to simplify complex schemas and/or queries, and to implement security. With the exception of indexed and distributed partitioned views, they are *not* used to enhance performance! This is a very common myth, and I'm not sure where the sentiment comes from, but it is inaccurate. FROM http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html So for me it would be testing that same thing but without the view at all. Create View viewTest as SELECT * FROM TableA -- Create sp as SELECT * FROM viewTest WHERE PKid = 334543 -- Create sp2 as SELECT * FROM TableA WHERE PKid = 334543 -- Now compare the two and see what you have(i've only used the following in 2000 but I assume it will work in 2005) Set Statistics IO On Set Statistics Profile On Set Statistics Time On Go exec sp GO Set Statistics IO Off Set Statistics Profile Off Set Statistics Time Off Go -- Set Statistics IO On Set Statistics Profile On Set Statistics Time On Go exec sp2 GO Set Statistics IO Off Set Statistics Profile Off Set Statistics Time Off Go
Thanks Satya and Jason. Yes, in the above test sp and sp2 have identical IO, CPU results. Which makes sense based upon this line in the page you referenced... "Essentially, the optimizer swaps out your reference to dbo.RIMA with the actual code from the view, and they can all be optimized together." I believe this answers my basic question. Which is what i thought and expected. I just wanted some SQL Guru confirmation!
Thanks for the link. Again, the basic question for me is whether to leave the "SELECT FROM VIEW" entries in my stored procedures or replace the VIEW with the VIEW's definition. Which I believe the answer was that it did not matter since the Optimizer substitued the VIEW's definition during optimization. However, I have run across this statement while researching the issue... http://tinyurl.com/6ozvvc "As for the impact of using the View, there will be much difference if you use view or select statements directly, the SQL Server just map the view to the select statement defined in the view and then materialized it. However, some other objects in the SQL Server would benifit you in the performance, such as stored procedure. The stored procedure is pre-complied on the server side and have the execution plan stored in the cache. It will be more efficient to run the execution plan than the ad-hoc queries." I believe the answer is still that it does not matter since I would suspect that the sp's execution plan and behaviour is the same regardless of whether the VIEW or it's definition is used in the sp itself. However, I just thought I would throw this out in case anyone had anything to add. Thanks again for all the feedback.
The view takes up space is something else to manage. My opinion is that you use a view when you can justify it, but don't use it just because you can.
Welcome to the forum. TO extend Jason's question I would like to ask what is the rowcount here and do you have any reference on that DAL tool that it is generating as per the TSQL preference. IN general the performance of view or SP execution is dependant upon the indexes on the columns that are mentioned after WHERE clause in that code.