Stored Procedure performance

• Roppie020

  SSC Rookie

  Points: 28

  More actions

  January 17, 2007 at 5:57 am

  #115813

  Hi,

  I have a create script for a stored procedure, a select statement from a view. When I run this script the SP is created, but the performance is dramatic (over one minute).

  When I run a script to alter the SP (exactly the same SP), the performance is much better (within 2 sec.).

  The execution plan is dramatically changed after I run the alter procedure statement.

  Does anyone know why the create procedure statement does not create the SP with good performance, but when I alter it, the performance is much better?

  Thanks in advandce!

  Roppie020

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  January 17, 2007 at 6:29 am

  #683699

  When you test the script declare variables to relace SP parameters.

  Don't replace it with hardcoded values within query..

  _____________
  Code for TallyGenerator

• Roppie020

  SSC Rookie

  Points: 28

  More actions

  January 17, 2007 at 6:33 am

  #683702

  Thanks for your reply.

  I test the SP I do this with SQL Query Analyzer. Right click on SP name and choose open. The following code will be executed:

  DECLARE @rc int

  DECLARE @customerId int

  SELECT @customerId = 5151

  EXEC @rc = [Storage].[dbo].[GetContractsForCustomer] @customerId

  DECLARE @PrnLine nvarchar(4000)

  PRINT 'Stored Procedure: Storage.dbo.GetContractsForCustomer'

  SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @rc)

  PRINT @PrnLine

  In both cases I use this code (after only create script and after alter script).

• Arthur-398534

  Valued Member

  Points: 59

  More actions

  January 17, 2007 at 6:03 pm

  #683889

  Hi,

  I don't understand your issue here, but what could be happening is.

  That the create stored procedure action of you is not held against the

  database statistic and optimizations, and therefor the second action

  altering the stored procedure optimizes the query.

  I'm not sure here.

  You can also try the With Recompile option on the Create Procedure statement.

  You can also recompile your views.

  I experienced also some unexpected behaviour in the past

   

  Cheers

  Arthur