optimizing SSIS package

  • sizal0234

    SSCrazy

    Points: 2194

     

    I am trying to optimize an SSIS package which has multiple streams..so step is loading some data ( using sp) and other streams are calculating ( using different sp).

    Now for loading using the below code

    DECLARE @yesterday DATE

    SET @yesterday =DBO.FNGETDATEONLY(GETDATE())

    SET @yesterday =DATEADD(DAY, -1, @YESTERDAY)

    EXEC SPNAMETEST @DATETOBEPROCESSED=@yesterday

    calculating steams uses below sql task code:

    DECLARE @yesterday DATE

    SET @yesterday =DBO.FNGETDATEONLY(GETDATE())

    SET @yesterday =DATEADD(DAY, -1, @YESTERDAY)

    EXEC SPNAMETESTdemo  @DATETOBEPROCESS=@YESTERDAY, @forcetotalrecalc=0.

     

    Now, I will be looking at the sp's for actual code, however any tips if any optimization if possible for above sampe code part of ssis package, which has no data flow tasks. only control tasks.

     

     

     

    loading

     

  • Jeffrey Williams

    SSC Guru

    Points: 88547

    I am not sure what you are expecting - as these code snippets cannot be optimized, but they can be shortened:

    DECLARE @yesterday DATE = dateadd(day, -1, getdate());
    EXECUTE spnametest @datetobeprocessed = @yesterday;

    Because the variable @yesterday is defined as a DATE data type - the time is automatically stripped so you don't need to get that separately and then subtract a day.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • sizal0234

    SSCrazy

    Points: 2194

    So, I am actually doing the high level package review, where I want to rule out if the code snippets can be optimized in any way. So, I got the answer, thanks!

    Second step for me would be to look at the SP code using SP_helptext and then look at the estimated execution plan.. example below and go from there.  Example, attached is the est plan..for one of the sp's..out of the two sp's which the ssis package uses.

     

  • Thom A

    SSC Guru

    Points: 98644

    sizal0234 wrote:

    So, I am actually doing the high level package review, where I want to rule out if the code snippets can be optimized in any way. So, I got the answer, thanks!

    Second step for me would be to look at the SP code using SP_helptext and then look at the estimated execution plan.. example below and go from there.  Example, attached is the est plan..for one of the sp's..out of the two sp's which the ssis package uses.

    If you want to optimise the SQL, then what you should be looking at is the definition of the Stored Procedures and seeing if they can be. and EXEC {Stored Procedure} statement can't be optimised, it the underlying object that needs to be.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply