SQL Server Performance

Where T-sql statements give better performance than Stored Procedures?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by medo, Oct 10, 2007.

  1. medo New Member

    Hi all,
    I want to ask a question that i need a reply to it as soon as possible:
    Where T-sql statements give better performance than Stored Procedures?
    please any one who know the answer send it as soon as possible.
    thanks to all the members of this great forum.
  2. thomas New Member

    You mean ad-hoc TSQL batches as opposed to SPs?

    In theory SPs won't perform any better, as execution plans for both types of query can be cached.

    there are other advantages to using SPs.
    - They reduce network traffic as a call to an SP is smaller than abatch of ad-hoc SQL
    - They help making programming more modular
    - They can be used as a security mechanism
    - They are part of the database, stored in system tables, therefore are backed up with the database
    - They integrate well with versioning tools such as Visual SourceSafe and development tools such as Visual Studio

    generally speaking they are considered best practice.

    However, it is not hard to find successful commercial software that does not use SPs at all, e.g. Peoplesoft.
  3. satya Moderator

    Thomas has summed up pretty much on this one, but the answer for your question is purely depends on the factors. As a whole both of them should have no difference, but for SPs it always goes with cached plan and best stored to get the results.
  4. Madhivanan Moderator

    The only disadvantage of SP may be "Parameter Sniffing". Do google search on the same to know how to avoid it

Share This Page