December 7, 2010 at 5:01 am
My Execute sql task is having one store procedure along with some sql statemetns in the sql query. when we run the package it is taking more time to compelte this task.
when i run the same proc from the SSMS it is taking very less time.(say 5 mins for proc to run in ssms and more than 30 mins in the Execute Sql task).
why it is taking more time in the packge execution.
Thanks in advance
December 7, 2010 at 6:32 am
statistics most like is the culprit.
if your SSIS package is insertint/deleting/updating rows, and then calling the procedure, the statistics are probably very different than when the stored procedure was created.
update the statistics on any tables the stored proc is using just before you call the proc, and I think you'll see a big improvement.
remember statistics will auto update when 20% of the rows in the table have changed...but 20% on a million row table for example is a lot, and it takes a much smaller percentage than that to throw off performance.
Lowell
December 7, 2010 at 6:43 am
Lowell (12/7/2010)
statistics most like is the culprit.if your SSIS package is insertint/deleting/updating rows, and then calling the procedure, the statistics are probably very different than when the stored procedure was created.
update the statistics on any tables the stored proc is using just before you call the proc, and I think you'll see a big improvement.
remember statistics will auto update when 20% of the rows in the table have changed...but 20% on a million row table for example is a lot, and it takes a much smaller percentage than that to throw off performance.
sorry i for got to mention that i already using the update statistics sp before calling the main Procedure.
in my Procedure i am inserting huge (1 million) records into one of the table. and i am using cursor for also. any more suggestions.
December 7, 2010 at 6:59 am
well first lets get rid of the cursor.
there is nothing a cursor does that a set based operation cannot do, and the set based would be a couple of orders of magnitude faster (100X faster!)
show us the cursor code you are using, and we can offer suggestions of re-written code.
Lowell
December 7, 2010 at 7:14 am
Lowell (12/7/2010)
well first lets get rid of the cursor.there is nothing a cursor does that a set based operation cannot do, and the set based would be a couple of orders of magnitude faster (100X faster!)
show us the cursor code you are using, and we can offer suggestions of re-written code.
but my question is why the packge is taking more time than what it takes in the SSMS
December 7, 2010 at 8:04 am
It's a valid question, but here's mine in response:
If you want to get somewhere fast, would you try to fix your injured camel when you've been offered a racehorse as an alternative?
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.
December 7, 2010 at 11:29 am
sharath.chalamgari (12/7/2010)
My Execute sql task is having one store procedure along with some sql statemetns in the sql query. when we run the package it is taking more time to compelte this task.when i run the same proc from the SSMS it is taking very less time.(say 5 mins for proc to run in ssms and more than 30 mins in the Execute Sql task).
why it is taking more time in the packge execution.
Thanks in advance
As Phil and the others mentioned above, fixing the method will probably fix your problem.
To answer your specific question, it comes down to execution plans. At a guess, for some reason the request from the SSIS is producing a different plan than when you run it from SSMS. This could be the parameters you use, this could be the optimizer doing something odd.
Only way to know for sure is to trap the execution plan via a trace from both sides and confirm they're they same/different, and if different, how they are.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2012 at 2:21 am
Hi, did you solve it, because I have the same issue.
I also use a cursor, but that has to stay in the procedure.
When running it from SSMS, it flies, but from within SSIS it's as slow as a turtle!!!
Please advise.
/Jan
September 21, 2012 at 6:13 am
JanTimmermans (9/21/2012)
Hi, did you solve it, because I have the same issue.I also use a cursor, but that has to stay in the procedure.
When running it from SSMS, it flies, but from within SSIS it's as slow as a turtle!!!
Please advise.
/Jan
Not enough information, I'm afraid. You'll have to at least tell us what the proc is doing and how it is doing it.
Can you also please describe why the cursor cannot be replaced?
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.
September 21, 2012 at 6:42 am
I've put the option ARITHABORT ON and now it's as fast as before again...
so issue solved!
Thanx,
/Jan
September 21, 2012 at 6:47 am
But I'm still curious how you'd rewrite the procedure without using an cursor 🙂
What we do:
Select ID, Adress, Zipcode
From aTable
Open Cursor
Fetch Next into variables
Do while eof
Exec StoredProcedure_to_cleanse_and_add_a_streetID (It uses many other storedprocs)
Update aTable with found variables
Fetch Next into variables
End
...
/Jan
September 21, 2012 at 2:07 pm
JanTimmermans (9/21/2012)
But I'm still curious how you'd rewrite the procedure without using an cursor 🙂What we do:
Select ID, Adress, Zipcode
From aTable
Open Cursor
Fetch Next into variables
Do while eof
Exec StoredProcedure_to_cleanse_and_add_a_streetID (It uses many other storedprocs)
Update aTable with found variables
Fetch Next into variables
End
...
/Jan
You extract the 'one by one' code from the executed procedure into your 'clean everything' procedure and expand it to use aTable as one of its components.
Not having seen SP_to_cleanse I don't know what all is involved, but that would be the overall process.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 22, 2012 at 9:25 am
Evil Kraig F (9/21/2012)
JanTimmermans (9/21/2012)
But I'm still curious how you'd rewrite the procedure without using an cursor 🙂What we do:
Select ID, Adress, Zipcode
From aTable
Open Cursor
Fetch Next into variables
Do while eof
Exec StoredProcedure_to_cleanse_and_add_a_streetID (It uses many other storedprocs)
Update aTable with found variables
Fetch Next into variables
End
...
/Jan
You extract the 'one by one' code from the executed procedure into your 'clean everything' procedure and expand it to use aTable as one of its components.
Not having seen SP_to_cleanse I don't know what all is involved, but that would be the overall process.
Or, change the SSIS process so it runs the 'cleanse' process on each row as that row is moved through the pipeline. In fact, you can probably rewrite that cleansing code in .NET to be a whole lot more efficient and use that code in a derived column transformation.
There are probably other ways in SSIS to tackle this - just one idea. Using a cursor over a million rows with SQL procedures to cleanse data doesn't sound like an ideal process to me.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply