asked on

SQL Server Code Optimization when a single Proc gets called multiples times inside another Stored Proc

I have the below AuditLogProc Stored Proc which the following

1. Execute different Stored Proc and Log the StartTime and End Time of Different Stored Proc execution by calling a another Stored Proc AuditLogProcDetails everytime as shown in the code below.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dba].[AuditLogProc]
@Id  as int,
@ProcessId as int

as 

 BEGIN 
    try 

        SELECT @StartTime=getDate();

            EXEC AppPopTimeInc @Id, @ProcessId;

        BEGIN
            exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppPopTimeInc',@StartTime, getDate()
        END


        SELECT @StartTime=getDate();

            EXEC AppPopTimeIncDetails @Id, @ProcessId;

        BEGIN
            exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppPopTimeIncDetails',@StartTime, getDate()
        END

        SELECT @StartTime=getDate();

            EXEC AppObsResultsAggInc @Id, @ProcessId;

        BEGIN
            exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppObsResultsAggInc',@StartTime, getDate()
        END

        SELECT @StartTime=getDate();

            EXEC AppPricedDetailsInc @Id, @ProcessId;

        BEGIN
            exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppPricedDetailsInc',@StartTime, getDate()
        END

        SELECT @StartTime=getDate();

            EXEC AppPricedDetailsIncDetails @Id, @ProcessId;

        BEGIN
            exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppPricedDetailsIncDetails',@StartTime, getDate()
        END

        SELECT @StartTime=getDate();

            EXEC AppLoggedData @Id, @ProcessId;

        BEGIN
            exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppLoggedData',@StartTime, getDate()
        END

        END try

        BEGIN catch 

            declare @ErrorMessage nvarchar(4000);
            declare @ErrorState int;

            SELECT 

                @ErrorMessage = error_message(),
                @errorState = error_state();

            raiseerror(@ErrorMessage, @ErrorState);
        END catch
    RETURN
    GO

Open in new window

My Questions are

Is below the best way to call the same proc AuditLogProcDetails everytime after different Stored Proc gets called i.e Including the in the BEGIN and END block ?

Since AuditLogProcDetails proc gets called multiple times , How can I refactored it better ?

AuditLogProc.sql

HainKurt

looks ok to me...
and probably that sp, AuditLogProcDetails, is just a few insert statments...

why do you have so many begin ends here? like line 20/22, 29/31 etc...

ronan_40060

ASKER

Thank you Huseyin for your comment and time , Yes thats what my question , Do I really need to enclose BEGIN and END around every INSERT made to AuditLogProcDetails ?

Is there any way to further Optimized this INSERT calls to AuditLogProcDetails ?

Please let me know what you think.

Thanks again for your time .

HainKurt

no need for those "BEGIN/END" and each call to AuditLogProcDetails have different parameters...

Create procedure [dba].[AuditLogProc]
    @Id  as int,
    @ProcessId as int
as 
BEGIN 
    try 
        SELECT @StartTime=getDate();
        EXEC AppPopTimeInc @Id, @ProcessId;
        exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppPopTimeInc',@StartTime, getDate()

        SELECT @StartTime=getDate();
        EXEC AppPopTimeIncDetails @Id, @ProcessId;
        exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppPopTimeIncDetails',@StartTime, getDate()

        SELECT @StartTime=getDate();
        EXEC AppObsResultsAggInc @Id, @ProcessId;
        exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppObsResultsAggInc',@StartTime, getDate()

        SELECT @StartTime=getDate();
        EXEC AppPricedDetailsInc @Id, @ProcessId;
        exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppPricedDetailsInc',@StartTime, getDate()

        SELECT @StartTime=getDate();
        EXEC AppPricedDetailsIncDetails @Id, @ProcessId;
        exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppPricedDetailsIncDetails',@StartTime, getDate()

        SELECT @StartTime=getDate();
        EXEC AppLoggedData @Id, @ProcessId;
        exec [dba].[AuditLogProcDetails] @Id, @ProcessId,'Exiting AppLoggedData',@StartTime, getDate()
    END try

    BEGIN catch 
        declare @ErrorMessage nvarchar(4000);
        declare @ErrorState int;

        SELECT 
                @ErrorMessage = error_message(),
                @errorState = error_state();

        raiseerror(@ErrorMessage, @ErrorState);
    END catch
GO

Open in new window

and I am assuming time is spent on other procedures not on this: AuditLogProcDetails
your code looks good to me...

HainKurt

can you please check what do you have in the log for

Exiting AppPopTimeInc - getDate()
Exiting AppPopTimeIncDetails - @StartTime

difference between these two date time is the time needed for one call to AuditLogProcDetails
if this one is very small, which is my guess, then no need to think about any optimization...

you can focus on each procedure if they are slow...

like these ones

AppPopTimeInc
AppPricedDetailsInc
AppObsResultsAggInc

etc...

ronan_40060

ASKER

Thank you so much for your inputs , yes internal Procedures are written with performance consideration, Please find attached AuditLogProcDetails which gets called everytime and AuditLogProcDetails INSERTS the logging into a Table ProcessLogging

Now ProcessLogging table used in AuditLogProcDetails is used for various logging Insertions like Batch Processing and Import Processing ,
I have attached the code for AuditLogProcDetails.sql , please have a look once and suggest any changes .

USE[MYDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dba].[AuditLogProcDetails]
(@Id  as int,
@ProcessId as int,
@ProcessDescription varchar(2000) = NULL,
@ProcessStartDate datetime = current_timestamp,
@ProcessEndDate datetime = current_timestamp,
@Task varchar(25) = NULL
)
as 

 BEGIN 
 
	IF @Task='Batch_Processing'
	
	Begin
 
	INSERT INTO dbo.ProcessLogging(Id,
								   ProcessId,
								   ProcessDescription,
								   StartTime,
								   EndTime)
	SELECT @Id,
		   @ProcessId,
		   @ProcessDescription,
		   @ProcessStartDate,
		   @ProcessEndDate
		   
    COMMIT;
	
	End
	
	IF @Task='Import_Processing'
	
	Begin
	
	INSERT INTO dbo.ProcessLogging(Id,
								   ProcessId,
								   ProcessDescription,
								   StartTime,
								   EndTime)
	SELECT @Id,
		   @ProcessId,
		   @ProcessDescription,
		   @ProcessStartDate,
		   @ProcessEndDate
		   
    COMMIT;
	
	End
 END
 GO

Open in new window

AuditLogProcDetails.sql

HainKurt

no issues with this, it is just a simple insert...

but

Create procedure [dba].[AuditLogProcDetails] (
  @Id  as int,
  @ProcessId as int,
  @ProcessDescription varchar(2000) = NULL,
  @ProcessStartDate datetime = current_timestamp,
  @ProcessEndDate datetime = current_timestamp,
  @Task varchar(25) = NULL
) as 
BEGIN 
  IF (@Task='Batch_Processing') or (@Task='Import_Processing')
  Begin
    INSERT INTO dbo.ProcessLogging(
      Id,
      ProcessId,
      ProcessDescription,
      StartTime,
      EndTime
      ) values(
      @Id,
      @ProcessId,
      @ProcessDescription,
      @ProcessStartDate,
      @ProcessEndDate
      );
    COMMIT;
  End
END;

Open in new window

and I guess no need for commit or if then else here....

Create procedure [dba].[AuditLogProcDetails] (
  @Id  as int,
  @ProcessId as int,
  @ProcessDescription varchar(2000) = NULL,
  @ProcessStartDate datetime = current_timestamp,
  @ProcessEndDate datetime = current_timestamp,
  @Task varchar(25) = NULL
) as 
BEGIN 
    INSERT INTO dbo.ProcessLogging(
      Id,
      ProcessId,
      ProcessDescription,
      StartTime,
      EndTime
      ) values(
      @Id,
      @ProcessId,
      @ProcessDescription,
      @ProcessStartDate,
      @ProcessEndDate
      );
END;

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ronan_40060

ASKER

Thank you for your comments and time , it does help