April 13, 2011 at 8:58 am
has anyone seen any performance variation between running processes in sp's versus embedding that same sql logic inside of SSIS packages? Kind of like arguing religion
April 13, 2011 at 9:14 am
Like everything SQL, It kind of depends on what you are doing and how you are set up from a hardware perspective.
If you have a "script" you can either put it in a stored proc or execute it directly from SSIS. If that is your question, there isn't really any difference in performance.
If you are talking about running a stored procedure that does a big chuck of work (lets say a large Merge statement that does Inserts and Updates) versus an SSIS package that does that same fucntion but in a different way, but both still are running on the same box. Then that is a slightly different set of factors. In general I think set-based solutions are best. However, on large datasets it's possble to see some rather large Tempdb growth to keep that one transaction atomic. If you used SSIS you might batch up things to "lighten the load" so to speak.
There is another option using SSIS on a different server and keeping data flowing through the pipeline, but I don't think you are asking about that. If you are, just let me know.
Hope that helps!
April 13, 2011 at 11:39 am
The is no one single way of determining when SSIS will perform better than a Stored Procedure.
But you must take this into cosideration:
1) When you make operational changes within a SP, it is handle by SQL itself, which means that it will use lots of memory and CPU depending on what you are doing.
2) When you perform operations in SSIS that is installed on Another server, the the operation is done by the local SSIS server using its memory and CPU to perform the operation and data manipulation.
This means, that this workload is not done on the SQL side, but rather on the SSIS buffer.
However, you have to consider the data when it's being moved across the network from SQL to the SSIS server to the Final destination.
3) if you are using an SSIS server that is not the same that your source SQL server, you have other choices to improve performance like:
Try to run as many operations in parallel as possible
You can manipulate the EngineThreads to perform operations.
Try to properly use the BufferSize and BufferTempStorage.
Those features are not available easily in SQL Server.
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 13, 2011 at 12:07 pm
timscronin (4/13/2011)
has anyone seen any performance variation between running processes in sp's versus embedding that same sql logic inside of SSIS packages? Kind of like arguing religion
I have, but it's specific circumstances. In particular, one off column lookups. I currently have been optimizing a significantly large data load. I've determined certain things are better off using a lookup component vs. multiple passes on a large staging table. Other things are not. Any time I've got to cause row duplication (ie many to many or looking up children of a parent) you'll want the staging table. Any time you've got 1 (one, uno) row to one lookup and the lookup is small, you'll want to use the LOOKUP component in SSIS under most circumstances.
What's small? That's going to depend on you. I'm currently processing files in about 30k rowsets, 15 of them at a clip. I optimized for a worst case scenario of 1 million rows in the resultant staging table. Dealing with the rows in the stream against cached lookups was MUCH more effective then a final multi-pass update, even with very tight index optimizations.
Then I had to do some mult-table joins to calculate a couple of things and this fell flat on its face.
End result: Small lookups to find surrogate keys for business keys are best off in SSIS. Any significantly heavy lifting is probably 50/50 at best, but T-SQL seems to handle it better and has more tools available to show you where your slowdowns 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply