Post reply

Stored procedure poor performance

  • November 18, 2009 at 3:07 am

    #214107

    Hi all,

    We have a stored procedure that run a INSERT statement:

    INSERT (c1, c2, c3, ...)

    select (c1, c2, c3)

    from ...

    The select part query is a complex query. If we execute the INSERT query in a new query window it costs 1-2 minute (for us it is good time).

    However if we execute the stored procedure in a new query window it cost more than 15 minutes (we have to cancel after this time because the execution does not end durign this time).

    Someone can help me? why this succeeded? how we can improve the stored procedure performance?

    Many thanks in advance.

  • November 18, 2009 at 5:37 am

    #1080813

    Please post full query, table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

  • November 24, 2009 at 9:28 am

    #1083535

    Hi,

    Thanks for you reply.

    I have been doing many test during the last week and i detect that the problem probably is the 'Parameter sniffing'.

    In this URL are a general article about this tecnique (it is spanish): http://grimpidev.wordpress.com/2009/01/23/parameter-sniffing/.

    As you can see in this article to solve the 'Parameter sniffing' are some solution. In my case using local variables in the stored procedure (to solve the Parameter sniffing) the stored procedures run in 2 min.

    I don't like very much this solution but i don´t find any other (i test adding new indexes, optimizing query etc ...)

    Someone could give me any other alternative solution?

    Many thanks in advance.

  • November 24, 2009 at 9:50 am

    #1083547

    ico-601891 (11/24/2009)

    Someone could give me any other alternative solution?

    Maybe, if you can post the full stored procedure, some info on the tables and indexes and the execution plan, as previously requested.

    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

  • November 25, 2009 at 7:44 am

    #1084028

    simple answer since you gave us to little to go on is to us dynamic sql in your sproc which should get you exactly the same performance you would see when running the query directly.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 5 posts - 1 through 4 (of 4 total)

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