SQL Server Performance

Procedure / Function / View?

Discussion in 'T-SQL Performance Tuning for Developers' started by gaurav_bindlish, Apr 4, 2003.

  1. gaurav_bindlish New Member

    Hi,

    I am preparing for SQL Server 2000 Design and Implementation Exam. One of the areas where I am making lot of mistakes is when it comes to selecting data from tables, I am not able to select the right opion - stored procedure, view or User defined function.

    My understanding for best conditions for these objects goes like this

    Stored Procedures should be used when the query needs to be called again and again so that performance is improved due to reuse of execution plan

    Functions should be used when the resultset needs can be used to join with other tables and also the data needs to be filtered on the basis of some parameter

    Views should be used Mainly for Security of data Concerns

    Help me out in classifying the use of these three objects.....



    Gaurav
  2. bambola New Member


    stored procedures are used to improve performance but not only. Suppose you want to show a client some data. Suppose you have a lot of different clients. You will not write a view for each client, but a procedure that accepts client as a parameter and returns the appropriate results.
    In this case, you would not give the client a direct access to run this procedure or he could exec it with whatever parameter he wishes. You would have to protect it from yout application and give the application the permission to run it.
    If you have a very few clients, you would create differents views for different clients.

    Views are also used to improve performance, and I am talking about indexed views. The are very useful with agg functions.

    Functions, like you've said are very useful when you still need to work on the result set.

    Bambola.
  3. satya Moderator

    What was the question you're getting confused between these 3 objects?
    These 3 objects have got their own advantages and importance in the SQL server. What you'd defined is basic functionality. When it comes to the question it depends how significant the object importance is.

    HTH

    _________
    Satya SKJ
  4. gaurav_bindlish New Member

    The question is when u have a query that can be written as view/sp/function, what are the factors that affect your decision?

    Gaurav
  5. Argyle New Member

    If it's a query that an application calls I would use a stored procedure since the library on the client has special methods for preparing/calling a stored procedure and you avoid doing an EXEC. It might also be a bit faster, as I understand it. I would not use a function here since the T-SQL that can be used is more limited than in a stored procedure. It is also easier to incorporate business rules inside a SP.

    Once inside a SP, if you need repetitive tasks made I would use functions as a help. If you need to join inside and SP on some special data I would use a view or a function, with one of the differences being that a function can be parameterized. Joining on results from another SP can be quite tricky.

    As mentioned views can also be used to seperate data for security reasons. Performance wise SQL server caches plans for re-use both for stored procedures and dynamic sql, be it select * from myView or select * from myTable.

    /Argyle

Share This Page