Stored procedure poor performance

  • 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.

  • Please post full query, table definitions, index definitions and execution plan, as per http://www.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
  • 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.

  • 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
  • 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