SQL Execute task - Improve stored procedure execution time.

• Saintmount.Sql

  Ten Centuries

  Points: 1023

  More actions

  August 2, 2013 at 9:57 am

  #297159

  Hi,

  I’m executing the Procedure thru SQL execute task along with input Param( which will retrieved from another SQL task).

  EXECUTE Proc_GetVal @a = ?, @b-2 = ?,@C = ?

  But it takes long time to complete .

  Note:

  I will be using SET NOCOUNT ON in Proc, Temp tables with kind of complex logic in procedure.

  Even I tried to use expressions and replace execute SQL statement with a variable which took long time to complete procedure....

  Let me know if any method to reduce execution time Procedure.

  Thanks.

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  August 2, 2013 at 10:03 am

  #1638250

  Saintmount.Sql (8/2/2013)

  ---

  Hi,

  I’m executing the Procedure thru SQL execute task along with input Param( which will retrieved from another SQL task).

  EXECUTE Proc_GetVal @a = ?, @b-2 = ?,@C = ?

  But it takes long time to complete .

  Note:

  I will be using SET NOCOUNT ON in Proc, Temp tables with kind of complex logic in procedure.

  Even I tried to use expressions and replace execute SQL statement with a variable which took long time to complete procedure....

  Let me know if any method to reduce execution time Procedure.

  Thanks.

  I think you might misunderstand something, this isn't an SSIS issue, the assembly of the actual command and the call to the SQL Server of that command will take fractions of a second. The logic within the sproc is what will determine how long it runs so you should be concentrating your efforts there, you will not get anywhere tribbling with the actual call.

  CEWII

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  August 5, 2013 at 12:49 am

  #1638520

  As Elliott suggests, this is really a T-SQL performance question.

  Unless you are suggesting that the proc runs more quickly when executed in SSMS than in SSIS?

  If this is the case, one of the first things I would be looking at would be the wonderfully termed 'parameter sniffing' - the closest T-SQL usually gets to perverted behaviour.

  Here [/url]is one of many articles describing the phenomenon.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin