October 16, 2012 at 5:23 am
Hi All,
We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps:
1. create a table and insert data into that using 'SELECT INTO' statement, which has many joins.
2. Update the table - this also has many joins.
3. Create indexes on the created tables.
What approach should we use here? Since there is no ETL in the SP, we converted each set of statements to one ‘Execute SQL’ task. Is this the right approach?
What we can do to improve the performance of the packages?
There are SPs that call another SP many times with different parameters(The parameters are hardcoded in the SP). Which SSIS task would be suitable here? I tried using ‘ExecuteSQL task’ with ‘RetainSameConnection’ true.
Thanks in advance
October 16, 2012 at 2:28 pm
deepa lekshmi (10/16/2012)
Hi All,We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps:
1. create a table and insert data into that using 'SELECT INTO' statement, which has many joins.
2. Update the table - this also has many joins.
3. Create indexes on the created tables.
What approach should we use here? Since there is no ETL in the SP, we converted each set of statements to one ‘Execute SQL’ task. Is this the right approach?
What we can do to improve the performance of the packages?
There are SPs that call another SP many times with different parameters(The parameters are hardcoded in the SP). Which SSIS task would be suitable here? I tried using ‘ExecuteSQL task’ with ‘RetainSameConnection’ true.
Why are you required to convert the stored procedures to SSIS? If you have a process that is working without problems, I wouldn't monkey with it. Or are there issues that you're trying to correct?
You can easily move your logic into the 'Execute SQL' task, but you're not going to see any real performance difference.
Good luck,
Rob
October 17, 2012 at 2:40 am
Rob, We are asked to do that since the long procedures are not easy to manage. Also, wanted to improve the performance. I am not sure whether converting it to packages will improve the performance, as there are no data load or any complex transformations.
October 17, 2012 at 2:48 am
deepa lekshmi (10/17/2012)
Rob, We are asked to do that since the long procedures are not easy to manage. Also, wanted to improve the performance. I am not sure whether converting it to packages will improve the performance, as there are no data load or any complex transformations.
Complex SSIS packages are more likely to increase maintenance cost than decrease it, because there are far more places to look for functionality, and more scope for introducing errors. A stored procedure has almost all of its' functionality in front of you. Whoever suggested this should be done to improve performance should be politely requested to explain why, as it isn't at all straightforward. I'll stick my neck out and suggest that unless you are importing from heterogenous data sources, or writing to them, an SSIS package is unlikely to perform faster than a stored procedure at the same task. And that's without mentioning bcp.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 17, 2012 at 2:50 am
deepa lekshmi (10/16/2012)
There are SPs that call another SP many times with different parameters(The parameters are hardcoded in the SP).
There may be some scope for improvement here. That's logically equivalent to a WHILE loop.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 17, 2012 at 3:21 am
Complex SSIS packages are more likely to increase maintenance cost than decrease it, because there are far more places to look for functionality, and more scope for introducing errors. A stored procedure has almost all of its' functionality in front of you. Whoever suggested this should be done to improve performance should be politely requested to explain why, as it isn't at all straightforward. I'll stick my neck out and suggest that unless you are importing from heterogenous data sources, or writing to them, an SSIS package is unlikely to perform faster than a stored procedure at the same task. And that's without mentioning bcp.
Totally agree with Chris on this after having to do the reverse, converting SSIS to Sprocs and the performance was much improved and an easier solution to maintain..
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
October 17, 2012 at 3:24 am
Andy Hyslop (10/17/2012)
Complex SSIS packages are more likely to increase maintenance cost than decrease it, because there are far more places to look for functionality, and more scope for introducing errors. A stored procedure has almost all of its' functionality in front of you. Whoever suggested this should be done to improve performance should be politely requested to explain why, as it isn't at all straightforward. I'll stick my neck out and suggest that unless you are importing from heterogenous data sources, or writing to them, an SSIS package is unlikely to perform faster than a stored procedure at the same task. And that's without mentioning bcp.
Totally agree with Chris on this after having to do the reverse, converting SSIS to Sprocs and the performance was much improved and an easier solution to maintain..
Thanks for the +1 Andy.
I'm hearing of more and more companies doing the same - "reverse engineering" their SSIS packages as stored procedures for performance and maintainability.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 17, 2012 at 4:06 am
deepa lekshmi (10/16/2012)
Hi All,We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps:
1. create a table and insert data into that using 'SELECT INTO' statement, which has many joins.
2. Update the table - this also has many joins.
3. Create indexes on the created tables.
What approach should we use here? Since there is no ETL in the SP, we converted each set of statements to one ‘Execute SQL’ task. Is this the right approach?
What we can do to improve the performance of the packages?
There are SPs that call another SP many times with different parameters(The parameters are hardcoded in the SP). Which SSIS task would be suitable here? I tried using ‘ExecuteSQL task’ with ‘RetainSameConnection’ true.
Thanks in advance
The source & destination databases are on same server or on different servers?
1. If both source and destination databases are on different servers & therefore you are using link server to connect, you may get some performance improvement using SSIS.
2. However, if both databases are on same server you are not going to get any performance improvment when using SSIS.
3. Talking about maintainability, stored procedures are very much easy to manage compared to SSIS packages.
October 18, 2012 at 1:04 am
Thanks all for the quick response.. We will rethink about converting SPs to packages if it can't improve the performance.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply