October 14, 2016 at 8:12 am
Dear all,
If I have a procedure like:
sp_1 (@par1, @par2)
AS
begin
exec sp2
exec sp3
end
if I add the expression "with recompile" on the master SP, all the sps that run inside it will also use recompile ? or I have to add it for each procedure ?
thanks
October 14, 2016 at 8:26 am
No, it applies only to the procedure it's on.
Why are you planning to add it? There's very few good reasons to recompile an entire procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2016 at 9:51 am
Thank you for the reply.
Because I have a stored procedure which calls several others.
Every time I call this procedures I pass difterent parameters and it seems that the query plans demage performance based on this.
October 14, 2016 at 10:19 am
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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2016 at 10:22 am
Understood. Thank you
October 14, 2016 at 10:24 am
It is very strange, because if they trigger the same SPs (in same order with same parameters) from SSIS, it works fine and takes always the same time.
If you instead create a proc and execute all the 7 procs (same order and same argumets) it takes much more and the time it takes varies a lot...
October 14, 2016 at 10:28 am
If I execute the 6 SPs like this (from inside another SP) the time is not consistent and I takes a lot of time.
exec [BMB1_Market].bmb_ms.[usp_BMBBI_audit_procedure_result_update] @Start_date,@End_Date, @market_code, @intNumRead = @intNumRead OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED OUTPUT, @INTNUMFAILED = @INTNUMFAILED OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_poc_update] @Start_date,@End_Date, @market_code, @intNumRead = @intNumRead2 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED2 OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED2 OUTPUT, @INTNUMFAILED = @INTNUMFAILED2 OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_machine_counter_estim_insert]
@market_code, @Start_date,@End_Date, @intNumRead = @intNumRead3 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED3 OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_machine_counter_estim_control_insert]
@market_code, @Start_date,@End_Date, @intNumRead = @intNumRead4 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED4 OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_last_ingredient_sale_insertupdate]@Start_date,@End_Date, @market_code, @intNumRead = @intNumRead5 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED5 OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED5 OUTPUT, @INTNUMFAILED = @INTNUMFAILED5 OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_last_ingredient_sale_oper_insertupdate] @Start_date,@End_Date, @market_code, @intNumRead = @intNumRead6 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED6 OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED6 OUTPUT, @INTNUMFAILED = @INTNUMFAILED6 OUTPUT
The other people also trigger in the same order and using the same parameters (but from inside SSIS), but in their case time is consistente and they take much less time
October 14, 2016 at 10:46 am
Both of us using the same SQL Server and at the same time...
October 14, 2016 at 10:47 am
So, after they execute I execute and vice-versa.
October 14, 2016 at 2:35 pm
My earlier statements stand.
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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2016 at 3:06 pm
Let me please put one more question.
If I have a SP which as several others inside and I stop that speaks, all the others will stop also ? Or they continue to execute?
Sample
SP name1 ( )
As
Begin
Sp2
Sp3
End
October 15, 2016 at 3:23 am
Since SQL executes those sequentially, there's only one running at a time. If you stop the execution (SSMS stop button or app timeout) then that session stops executing, the running statement gets rolled back and no further statements are executed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2016 at 10:52 am
Hello,
Now I have more details. Maybe this is relevant.
So I am executing this stored procedure
alter Proc usp__BMBBI_oltp_trigger (@IsMarket as int = null,
@IsZone as int = null,
@market_code as int,
@Start_date as datetime,
@End_Date as Datetime)
AS
BEGIN
SET ANSI_warnings OFF
SET TRANSACTION ISOLATION LEVEL serializable
Declare @intNumRead as int
Declare @intNumRead2 as int
Declare @intNumRead3 as int
Declare @intNumRead4 as int
Declare @intNumRead5 as int
Declare @intNumRead6 as int
Declare @INTNUMINSERTED as int
Declare @INTNUMINSERTED2 as int
Declare @INTNUMINSERTED3 as int
Declare @INTNUMINSERTED4 as int
Declare @INTNUMINSERTED5 as int
Declare @INTNUMINSERTED6 as int
Declare @INTNUMUPDATED as int
Declare @INTNUMUPDATED2 as int
Declare @INTNUMUPDATED3 as int
Declare @INTNUMUPDATED4 as int
Declare @INTNUMUPDATED5 as int
Declare @INTNUMUPDATED6 as int
Declare @INTNUMFAILED as int
Declare @INTNUMFAILED2 as int
Declare @INTNUMFAILED3 as int
Declare @INTNUMFAILED4 as int
Declare @INTNUMFAILED5 as int
Declare @INTNUMFAILED6 as int
Declare @BeginDate datetime
Declare @EndDate datetime
Declare @Ended as int
if (@IsMarket <1000)
begin
WHILE @market_code < 2033
begin
exec [BMB1_Market].bmb_ms.[usp_BMBBI_audit_procedure_result_update] @Start_date,@End_Date, @market_code, @intNumRead = @intNumRead OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED OUTPUT, @INTNUMFAILED = @INTNUMFAILED OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_poc_update] @Start_date,@End_Date, @market_code, @intNumRead = @intNumRead2 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED2 OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED2 OUTPUT, @INTNUMFAILED = @INTNUMFAILED2 OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_machine_counter_estim_insert]
@market_code, @Start_date,@End_Date, @intNumRead = @intNumRead3 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED3 OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_machine_counter_estim_control_insert]
@market_code, @Start_date,@End_Date, @intNumRead = @intNumRead4 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED4 OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_last_ingredient_sale_insertupdate]@Start_date,@End_Date, @market_code, @intNumRead = @intNumRead5 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED5 OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED5 OUTPUT, @INTNUMFAILED = @INTNUMFAILED5 OUTPUT
exec [BMB1_Market].[bmb_ms].[usp_BMBBI_last_ingredient_sale_oper_insertupdate] @Start_date,@End_Date, @market_code, @intNumRead = @intNumRead6 OUTPUT,@INTNUMINSERTED = @INTNUMINSERTED6 OUTPUT,
@INTNUMUPDATED = @INTNUMUPDATED6 OUTPUT, @INTNUMFAILED = @INTNUMFAILED6 OUTPUT
select @market_code, @BeginDate,@EndDate,@intNumRead as intNumRead,@INTNUMINSERTED as INTNUMINSERTED ,@INTNUMUPDATED as INTNUMUPDATED, @INTNUMFAILED as INTNUMFAILED,
@intNumRead2 as intNumRead2, @INTNUMINSERTED2 as INTNUMINSERTED2, @INTNUMUPDATED2 as INTNUMUPDATED2, @INTNUMFAILED2 as INTNUMFAILED2, @intNumRead3 as intNumRead3 ,@INTNUMINSERTED3 as INTNUMINSERTED3,
@intNumRead4 as intNumRead4, @INTNUMINSERTED4 as INTNUMINSERTED4,
@intNumRead5 as intNumRead5, @INTNUMINSERTED5 as INTNUMINSERTED5, @INTNUMUPDATED5 as INTNUMUPDATED5, @INTNUMFAILED5 as INTNUMFAILED5,
@intNumRead6 as intNumRead6, @INTNUMINSERTED6 as INTNUMINSERTED6, @INTNUMUPDATED6 as INTNUMUPDATED6, @INTNUMFAILED6 as INTNUMFAILED6
SET @market_code = @market_code + 1;
END
END
END
END
The goal of it is tho achieve the same time that the package (SSIS) that I have.
This package calls exactly the same procedures as per below:
Name: Execute usp_BMBBI_audit_procedure_result_update
Timeout:0
CodePage:1252
TypeConversionMode: Allowed
ResultSet: None
ConnectionType: OLEDB
Connection: DatabaseName
SQLSourceType: DirectInput
SQLStatement: EXEC bmb_ms.usp_BMBBI_audit_procedure_result_update ?,?,?,? OUTPUT,? OUTPUT,? OUTPUT,? OUTPUT
IsQueryStoredProcedure: False
BypassPrepare: true
Parameters mapping:
User::glbSSISStartDate input date Size -1
User::glbSSISEndDate input date Size -1
User::glbMarketCode input long Size -1
User::numReadRowCount Output long Size -1
User::numInsertedRowCount Output long Size -1
User::numUpdatedRowCount Output long Size -1
User::numFailedRowCount Output long Size -1
Can you please help understand what am I doing different to not be able to achieve the same times?
Note, Each stored proc inside the pachage is coonected as on success proceed to the next one.
Thank you very much for the support
October 17, 2016 at 11:51 am
Are you actually executing SSIS on the same exact machine / instance in SSIS as you are executing the procs in SSMS?
And, if so, how are you executing SSIS?
Which is giving you the better time? SSMS or SSIS?
October 17, 2016 at 12:00 pm
How did you implement that WHILE loop in your SSIS package?
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.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply