SQL Server Performance

Functions/StoredProcs/Views

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Aug 23, 2005.

  1. PAMUR New Member

    Hi,

    I know the functionality of Functions,StoredProcs and Views. I wan't to know the performance issues.

    I heard that views are really bad for performance.

    What about Functions vs Stored Procs?
  2. satya Moderator

    SP tips:
    http://www.sql-server-performance.com/stored_procedures.asp
    http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp

    Views tips:
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx
    http://www.sql-server-performance.com/indexed_views.asp
    Take care not to let the number of special case views explode in your database. Although it might be tempting to provide every users a view to meet specific filtering requirements, remember it is possible to SELECT from an existing view and provide additional filtering. A large number of views can be difficult to maintain, especially when breaking changes are required to the underlying schema.

    Functions:
    http://www.sql-server-performance.com/user_defined_functions.asp
    http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1063700,00.html?bucket=ETA

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. PAMUR New Member

    Thanks a loooooooot Satya. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Given me a good insight. Will get back to you if need more.<br /><br />Thanks once more
  4. PAMUR New Member


    Hi,

    For my problem where a base view is accessing many tables and returning huge result set and is used by other views and queries and has a performance issue, the ideal solution seems to be to convert my base view to indexed view than Function or Stored proc.

    Thanks.
  5. satya Moderator

    BOL notifies:
    Indexed views can be created in any edition of SQL Server 2000. In SQL Server 2000 Enterprise Edition, the query optimizer will automatically consider the indexed view. To use an indexed view in all other editions, the NOEXPAND hint must be used.
    .
    .
    .
    .
    Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. PAMUR New Member


    Yes I have Got information. Thanks a lot.

    I will try in test environment and implement it. Hopefully I will have no issues.

    I have three options:

    1) Indexed views (modify tables, views ) - no front end change required
    2) Modifying the views to join base tables directly instead of joining base view created
    3) Modifying the views to join base tables directly instead of joining base view created
    and converting them to stored procs (involves front end code change to implement sp)

    Though I prefer option 1 i will have to try 2,3,1 in that Order.

    Is it good to spend all that time? Surly This requires that kind of tuning - crusial.

    Thanks again Satya.
  7. PAMUR New Member


    Hi,

    I have tried option 2 and 3 I have reduced the cost of IO and CPU by changing nested view into just view and also to a stored proc. But have not gained much benefit in duration. What do I do?

    COnfused!

    Regards
  8. satya Moderator

    Ensure the underlying indexes are reindexed and you've compiled SPs after any change in the data.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. mmarovic Active Member

    Also regurarly or autoupdate statistics. Review index design. Improve sql code...
  10. PAMUR New Member



    Thank You. I have reduced the cost of IO and CPU by tuning indexes, improving join but yet!

    I will try again and check if I've overlooked somethig.


    I shall get back.


    Thanks a lot to all of you.
  11. PAMUR New Member

    Hello!

    Will the following statement in a stored proc building the @SqlString reduce performance?
    The Stored proc actually takes parameters for Where clause and Order By clause. Hope am clear.

    EXEC sp_executesql @SqlString

    Thanks.
  12. Adriaan New Member

    Using sp_ExecuteSQL may improve performance, but not if the query by itself is performing poorly because of any of the issues mentioned above.

    sp_ExecuteSQL is a way to allow more query plans to be re-used, but that depends mainly on feeding the parameters into it as true parameters, not as values concatenated into @SqlString.
  13. FrankKalis Moderator

  14. satya Moderator

  15. PAMUR New Member

    Thanks to All
    edited




  16. PAMUR New Member

    I am trying to tune nested views.




    Thanks [B)]

  17. Adriaan New Member

    The only critical part of that procedure is the actual query that is run against the data.

    Copy the script, and make it print out the entire SELECT statement with all parameter values concatenated into the string.

    Copy that complete SELECT statement into another query window, and run the query to see the response time.

    Then run the Index Tuning Wizard on that query.
  18. Adriaan New Member

    I was responding to your previous posting, before the nested views.
  19. PAMUR New Member

    The link provided by frank has been given me good insight into dynamic SQL, thanks Frank. I think I will stick with simple SPs.

    My serious issue is performance tuning those nested views.


    Thanks to all of you.

Share This Page