Stored procedure poor performance

Question

Stored procedure poor performance

ico-601891

Hall of Fame

Points: 3992
More actions
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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

ico-601891 Hall of Fame Points: 3992 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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