August 2, 2013 at 9:57 am
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.
August 2, 2013 at 10:03 am
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
August 5, 2013 at 12:49 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply