October 17, 2016 at 2:35 pm
Hello, Thank you for the feedback. Both on same machine. The ssis package performed much better then the script. With the package I take 16 minutes. With the script more then one hour. In the ssis packages are executed as sql tasks and each one is connect to the next stored procedures with a , on success, to execute the next. Can a warning be considered as not success ?
October 17, 2016 at 2:41 pm
The ssis is executed by a bat file
October 17, 2016 at 3:42 pm
river1 (10/17/2016)
Hello, Thank you for the feedback. Both on same machine. The ssis package performed much better then the script. With the package I take 16 minutes. With the script more then one hour. In the ssis packages are executed as sql tasks and each one is connect to the next stored procedures with a , on success, to execute the next. Can a warning be considered as not success ?
Did you implement the WHILE loop in the outer proc as a FOR LOOP container in SSIS?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 18, 2016 at 12:55 am
The ssis package was not done by me. But the for loop that exists in the spring should be fast. Faster then cursor, correct?
October 18, 2016 at 5:39 am
GilaMonster (10/14/2016)
river1 (10/14/2016)
Every time I call this procedures I pass difterent parameters and it seems that the query plans demage performance based on this.With recompile might fix that, might not, but it's not the best solution. Investigate your queries and make sure that what you think is happening is really happening, and targeted recompile hints on the queries are far better than on the entire procedure.
I haven't suggested or used WITH RECOMPILE since the statement-level recompile was added in SQL 2005.
Maybe (some of) these stored procedures already do have an active WITH RECOMPILE option. I do not know whether they will be recompiled for each iteration through the WHILE loop, or only once for the entire execution of the outer stored procedure. That might influence the performance and even (to some extend) explain why calling them from within as SSIS sequence runs faster. Gail, do you know whether SQL Server wil recompile the called stored procedures with an WITH RECOMPILE option for each iteration or only once?
October 18, 2016 at 5:41 am
river1 (10/18/2016)
The ssis package was not done by me. But the for loop that exists in the spring should be fast. Faster then cursor, correct?
I have no idea how spring, or any other season, comes into the equation, so I will ignore that bit for now.
It clearly is not apparent to you why I am asking this, so I will spell it out.
Procedure usp__BMBBI_oltp_trigger contains a WHILE loop which will execute 6 procs for every iteration, and there are over 1,000 iterations, meaning > 6,000 proc executions.
I wanted to ensure that you were comparing like with like. Does your SSIS package also do the same number of iterations?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 18, 2016 at 5:58 am
river1 (10/17/2016)
Hello, Thank you for the feedback. Both on same machine. The ssis package performed much better then the script. With the package I take 16 minutes. With the script more then one hour. In the ssis packages are executed as sql tasks and each one is connect to the next stored procedures with a , on success, to execute the next. Can a warning be considered as not success ?
If I am reading this correctly, you aren't even executing the procs in the same manner in the SSIS package as you are in SSMS. It appears as if each individual proc is being run in an Execute SQL Task rather than looped through with the WHILE statement that Phil pointed out.
If this is the case, then it's no wonder SSIS is faster. It's not doing a RBAR type loop that multiples the number of proc runs like SSMS is.
EDIT: Added bolding.
October 18, 2016 at 5:59 am
Brandie Tarvin (10/18/2016)
river1 (10/17/2016)
Can a warning be considered as not success ?If I am reading this correctly, you aren't even executing the procs in the same manner in the SSIS package as you are in SSMS. It appears as if each individual proc is being run in an Execute SQL Task rather than looped through with the WHILE statement that Phil pointed out.
If this is the case, then it's no wonder SSIS is faster. It's not doing a RBAR type loop that multiples the number of proc runs like SSMS is.
EDIT: Added bolding.
And a warning is just an informational error. There's nothing in a warning that prevents a step from failing or succeeding after you're warned about a potential issue.
October 18, 2016 at 6:42 am
Hello,
The ssis as a loop also. It is a forearm loop container. It loops the same number of times as the sp.
Also, tests are being executed on the same machine
October 18, 2016 at 6:45 am
river1 (10/18/2016)
Hello,The ssis as a loop also. It is a forearm loop container. It loops the same number of times as the sp.
Also, tests are being executed on the same machine
Then what did you mean by separate Execute SQL Tasks?
October 18, 2016 at 6:53 am
river1 (10/18/2016)
Hello,The ssis as a loop also. It is a forearm loop container.
A what?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 18, 2016 at 7:24 am
ForEach
October 18, 2016 at 11:38 am
You are comparing apples to oranges. SSIS is set up differently than SSMS. We advise you hire a consultant to come in and look at your environment to give you advice on how to fix your issues.
October 18, 2016 at 2:23 pm
Separated sql tasks means that each species is executed inside is sql tasks. Task are connect through success arrows ( green arrows). So, exactly as the sp I created, each market executes all stored props and then it loops to the next market to execute again all the stored procs
October 19, 2016 at 11:23 am
river1 (10/18/2016)
Separated sql tasks means that each species is executed inside is sql tasks. Task are connect through success arrows ( green arrows). So, exactly as the sp I created, each market executes all stored props and then it loops to the next market to execute again all the stored procs
Apples and oranges.
If you want a good answer, hire a local expert to come in and look at your setup. We cannot diagnose your issue remotely.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply