January 14, 2008 at 3:58 am
Is it beneficial to use stored Proc in a Execute SQL task in SSIS compared to writing a query using SQL statement property in Execute SQL task. Will there be better performance if i use a stored proc.?
January 14, 2008 at 12:35 pm
The benefit to using a stored procedure is that the execution plan might be stored in the procedure cache so at run time the SQL Engine won't have to compile the SQL before executing unless statistics have changed significantly on the underlying table(s). This benefit is usually not important when it comes to executing SSIS packages because they are likely not going to be executed over and over again as would SQL that was OLTP related.
For my SSIS packages I usually use a stored procedure if one already exists that does what I want. However, if I have to create the SQL I usually just use in-line SQL because I find that dealing with the paramters is a little less painful this way.
Hope this helped
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply