Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    140

    Unanswered: optimize stored proc

    Hi all

    What are the all the ways to optimize a stored proc ?
    should stored proc parameters be used in WHERE clause ?

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    1) use SARGs in your conditions, avoid TABLE SCAN analyzing the showplan
    2) optimize each SQL statement of the SP
    3) optimize it using relevant data volume
    4) update your statistics + sp_recompile
    5) etc...

    The stored procedure parameters MUST be used in the where clause (else I really do not understand their goal ;-))

    E.g.

    Code:
    CREATE PROC myProc (@a int)
    as
    BEGIN
    SELECT * from YourTable where yourID=@a
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •