June 4, 2020 at 5:10 pm
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
June 4, 2020 at 5:57 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 4, 2020 at 6:03 pm
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.
June 5, 2020 at 8:18 am
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.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply