October 1, 2009 at 2:51 am
Hi,
Please tell me, how to improve the SSIS Package perpormance.
October 1, 2009 at 3:56 am
sridhar.yalamanchi (10/1/2009)
Hi,Please tell me, how to improve the SSIS Package perpormance.
That is a big question, do you have a SSIS package where performance is an issue. and can you say what the package is doing and then we might be able to help you further
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
December 7, 2010 at 5:47 am
Browse thru http://technet.microsoft.com/hi-in/library/cc966529(en-us).aspx to clearly understand on how to tune ssis packages.
January 27, 2011 at 6:34 am
1. Select only those columns in source which are required.
2. Increase max row size and max buffer size.
3. Avoid asynchronous transformations.
4. Try to do order by and aggreate by sql queries.
5. Use parallel execution.
6. Drop index in destination table and recreate after import.
7. Use partial/full cache in look up.
Regards,
Ashish
January 27, 2011 at 1:43 pm
siluctc (1/27/2011)
5. Use parallel execution.
Will this always improve package performance?
Please elaborate...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 12:46 am
Use SQL command iso table or view fot input
put as many transformations in the SQL iso a derived column
Use order by in the SQL not in the flow
January 28, 2011 at 1:14 am
frank_suijkerbuijk (1/28/2011)
put as many transformations in the SQL iso a derived column
What if your source is a heavily used production system?
Will you burden it with extra transformations, or will you quickly extract the data and get out of there?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 3:46 am
Well, that is as design decision.
If it is heavily used, certainly use SQL command to extract to a local table and then do the transformations in a SQL command on that table.
This because the SQL command can be a factor 10 faster as compared to a table or view. (I know, depends on size, system and other variables) But in general it is faster and that was the question, how to improve the performance of the SSIS component. Every situation will have other parameters to consider, so every answer should be seen as a general statement, to be adapted to your situation.
January 28, 2011 at 3:55 am
frank_suijkerbuijk (1/28/2011)
Well, that is as design decision.If it is heavily used, certainly use SQL command to extract to a local table and then do the transformations in a SQL command on that table.
This because the SQL command can be a factor 10 faster as compared to a table or view. (I know, depends on size, system and other variables) But in general it is faster and that was the question, how to improve the performance of the SSIS component. Every situation will have other parameters to consider, so every answer should be seen as a general statement, to be adapted to your situation.
You are right, the SQL statement will (in 99% of the cases) be faster than the table or view dropdown box.
But regarding transformations, that indeed depends on design decisions and on too many variables.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 5:54 pm
Another thing that comes to my mind, is
1) Use TABLOCK option on OLEDB Destination adapter, this causes the inserts to speed up.
2) Unless absolutely necessary, replace the row by row OLEDB command with a flow that first dump data to be updated a staging table, followed by the UPDATE statement.
Still thinking on other ideas to improve performance....
Amol Naik
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply