question

Coilover avatar image
Coilover asked

Nested stored procedure performance issue SQL Server 2017

I have a large and complicated stored procedure that gathers and processes customer records. If I run it for a large data set it takes about 4 seconds to process (about the same as the code if run straight from SSMS).

I use this as a master data retrieval stored procedure within other stored procedures (which format the data in various ways).

If I run the stored procedure from inside another stored procedure it runs like a dog (> 3 minutes)

I stripped the 'parent' stored procedure right down and it is now very simple:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER PROCEDURE [dbo].[usp_MyReport]
    (@StartDate DATETIME = NULL,
     @EndDate DATETIME = NULL,
     @HeadId INT = 5587,
     @AreaId INT = NULL,
     @BranchId INT = NULL,
     @SiteId INT = NULL,
     @WasteOrderHeaderId INT = NULL,
     @WasteOrderDetailId INT = NULL,
     @ContainerDesc Varchar (50) = NULL,
     @ContainerSizeDesc Varchar (50) = NULL,
     @WasteDesc Varchar (50) = NULL,
     @Month INT = NULL,
     @Year INT = 2021)
AS
    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;
BEGIN


    DECLARE
        @localStartDate DATETIME = @StartDate,
        @localEndDate DATETIME = @EndDate,
        @localHeadId INT = @HeadId,
        @localAreaId INT = @AreaId,
        @localBranchId INT = @BranchId,
        @localSiteId INT = @SiteId,
        @localWasteOrderHeaderId INT = @WasteOrderHeaderId,
        @localWasteOrderDetailId INT = @WasteOrderDetailId,
        @localContainerDesc Varchar (50) = @ContainerDesc,
        @localContainerSizeDesc Varchar (50) = @ContainerSizeDesc,
        @localWasteDesc Varchar (50) = @WasteDesc,
        @localMonth INT = @Month,
        @localYear INT = @Year


    EXECUTE  [dbo].[usp_MyMasterData] 
        @StartDate = @localStartDate,
        @EndDate = @localEndDate,
        @HeadId = @localHeadId,
        @AreaId = @localAreaId,
        @BranchId = @localBranchId,
        @SiteId =  @localSiteId,
        @WasteOrderHeaderId = @localWasteOrderHeaderId,
        @WasteOrderdetailId = @localWasteOrderdetailId,
        @ContainerDesc = @localContainerDesc,
        @ContainerSizeDesc = @localContainerSizeDesc,
        @WasteDesc = @localWasteDesc,
        @Month = @localMonth,
        @Year  = @localYear WITH RECOMPILE


END

I have tried:

1) Allocating the parameters to local variants (see above code)

2) DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE

3) Running the EXECUTE with a WITH RECOMPILE


I have an older version of the `MyMasterData` stored procedure which works just fine within other stored procedures. I recently re-wrote it to improve performance using a temporary table over a table variable. Is it possible that the table variable is causing the slow-down?

sqlserver
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

You'll need to look at the execution plans to understand why one performs better than the other. Using RECOMPILE is giving you a fresh plan each time, so you could be having parameter sniffing issues on the other executions. The plan will help to determine this.

As for the table variable vs temp table - it can depend on the number of rows that you are putting into them. A smaller number of rows can work better with variables, avoiding statement recompiles, whereas a larger number of rows will benefit from the improved statistics of temp tables.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.