SQL Server Performance

Selecting data from a View in a Stored Procedure

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by rmcellhiney, Jul 29, 2008.

  1. rmcellhiney New Member

    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.

  2. jason7655 New Member

    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.
  3. rmcellhiney New Member

    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.


  4. satya Moderator

    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.
  5. jason7655 New Member

    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
  6. rmcellhiney New Member

    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!
  7. FrankKalis Moderator

    This might probably be interesting for you: http://www.craigsmullins.com/viewnw.htm
  8. rmcellhiney New Member

    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.
  9. jason7655 New Member

    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.
  10. satya Moderator

    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.

Share This Page