Post reply

How to use indexes for improving performance of stored procedure

  • July 28, 2009 at 12:18 am

    #385068

    Hi,

    I have one SP which contains some while loops and calls to other SP's internally. The SP takes approx 1min 14 sec for execution, when table contains approx 2lakh records. the execution time increases as no of records in table increases. my requirement is, i want to complete the execution of this SP within 1min; no matter, how many records table contains.

    Can indexing help me in improving the performance?

    if yes, please provide some guidelines about indexing in SQL server 2005.

    I have never worked with indexes. i know how to create the indexes, but not sure, about the way, they are used.

    Thanks in advance.

  • July 28, 2009 at 1:16 am

    #1030877

    Please post the procedure, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    First thing I'd suggest, try to get rid of that while loop. Looping in SQL is slow and there's usually a better way.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply