SQL Server Performance

View vs Stored Proc

Discussion in 'General Developer Questions' started by JusticeLeague, Jun 13, 2005.

  1. JusticeLeague New Member

    If I just have a SELECT statement, is it better to have it in a stored proc or in a view?

    I know that one of VIEW's advantages is that you can apply security with it, and a STORED PROC performs well (speed).

    Please consider that my select statement could be complicated.

    Thanks in advance!
  2. satya Moderator

    I feel stored procedure would be fine in this case, as your can define the security measures to SP also.

    If you can post sample of your script, then definetly someone will help you.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. jastone New Member

    Stored Procedures are always better....EXCEPT WHEN
    you want to be able to join off of the sproc's returned results

    Joe Janka
  4. dineshasanka Moderator

    I also agree with the stored procedure
    if you need to use select with some parameters then you should definitely go with Sps
    but better if you give us the scripts of yours
  5. JusticeLeague New Member

    But what can you say about the whole thing BOL explains about the benefit of using a view.
    From BOL
    Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.

    This explains that when you create a unique clustered index on your view your query or other query could benefit from this.

    Did i understood it correctly?
  6. mmarovic Active Member

    Yes, but inserts, updates, deletes will have to update newly created index on top of what they already do.
  7. JusticeLeague New Member

    Hi mmarovic!

    Yes, but it will only happen once, since you said newly created index. So the next time an update/insert/delete happens on a table/view that has the index, there's no need to update it.

    Can you give me more feedbacks or comparisons between using views and stored proc?

    Anybody has any reaction on what I quote from BOL?
  8. ghemant Moderator

    <b>Benefits of Using Stored Procedure </b> :<br /><br />Improvement of Performance -- Because you / application not need to compile the procedure again and again<br /><br />Reduced Network Congestion -- Application not need to submit the multile T-SQL Statement to server<br /><br />Enhance Accuracy -- Less prone to errors<br /><br />Better Security -- User can be granted / revoke permission<br /><br /><b>Benefit of Using Views </b> :<br /><br />Focus data for User<br /><br />Hiding data complexity<br /><br />Reduce Object Size<br /><br />Regards.<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />hsGoswami<br />ghemant@gmail.com
  9. dineshasanka Moderator

  10. JusticeLeague New Member

    Hi dineshasanka!

    Thanks for the link. I guess that's sufficient enough to for me to consider things when designing views/SP/functions. And Mr. Aaron Bertrand, as well as others who replied on this post, is pretty sure right about using stored proc. He has a very good point on using SP, may I quote him:
    "Now, having said all that, there is a difference between client->server interaction, and interaction within SQL Server itself. All client->server interactions with SQL Server, whether they be from an application or an end user in Query Analyzer, should be through stored procedures. Processes outside of SQL Server should not have direct access to tables, views or functions. So my basic ground rule is, for anything client-facing, build a stored procedure. Whether that stored procedure accesses tables, views or UDFs behind the scenes, rests entirely on the points above that are relevant to your environment, and the testing you perform in order to yield which implementation works best."

    Performance wise, I always go for stored proc, I just thought BOL topics about Views was telling me that using stored proc was a bad idea or at least not the best choice for my queries.

    Well, thank you all who replied on this post!
  11. ranjitjain New Member

  12. SQL2000DBA New Member

    As per me the link given by ranjitjain explain how SP are better than view.

    Thanks Rahitjain. it is realy useful.

    Thanks & regards

    Ravi K
  13. JusticeLeague New Member

    Okay I guess my first question was not good.<br /><i>...is it better to have it in a stored proc or in a view?</i><br /><br />As it is a fact that SP should really not be compared to a view in terms of performance. Though as stated in BOL views can provide performance if you the designer would be able to create a well-designed index on a view.<br /><br />Having said all of that, I should have probably asked: <i>Which would yield a better design, using SP or using a view?</i><br /><br />No need to answer this brothers as it was clearly explained in both url given.<br /><br />Again, thank you for you replied! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  14. JusticeLeague New Member

    Forgive the grammar, typo error. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]

Share This Page