Stored Procedure "Caching"

  • I'm having an issue with a database that I recently moved from SQL 2014 to SQL 2019 (Standard in both cases).  The users get data from the database using stored procedures either through the UI or linked to Excel (using an ODBC).  The users will execute the same procedure multiple times with different parameters expecting to get different result sets to support daily processes.  However, they're not getting the result set they expect.

    As an example - we'll call the stored procedure PricingReport.

    EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '12345';
    EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '56789';

    If the stored procedure gets executed as above, sometimes the user will get the result set for PricePoint = 12345 twice.  If the two procedure calls get executed again immediately after, they might return the results for PricePoint = 56789 twice.

    Under the hood, the stored procedure uses the parameters passed to generate dynamic SQL to populate a temp table and then returns the results in the temp table to the user.

    I was able to replicate the behavior in SSMS, but I'm having trouble finding the right search phrase to figure what setting I need to change to fix this issue.  I've simplified the example code a bit, but I hope it's enough for someone to at least point me in the right direction.



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • Make sure the proc is dropping or truncating the temp table every time at the start of the proc.

    It sounds as if rows from the prior run are being left in the temp table after the proc exists ... which should not be possible, but it still sounds like that.  Would you by any chance be using a global temp table named ##table rather than #table?

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I'm looking at the details of the stored procedure now.  From what I'm seeing, all temp tables created within the stored procedure are dropped at the end.



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • Last few statements in the stored procedure are:

    --export results
    SELECT * FROM #ReportResults ORDER BY dateStamp

    --clean up temp tables
    DROP TABLE #ReportResults

    If I add option (recompile) to the select, I can get the proper result set executing the procedures back to back.  Since the #ReportResults temp table is created dynamically for the procedure, I don't know the names of the columns in the table.  That's determined by the parameters the user passed in.

    Is that my best shot for a resolution of the issue?  If it came down to it, I could generate the column list for the select.  It's being done elsewhere in the procedure.



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • Is it possible to exit the proc without falling thru to the DROP TABLE?

    What about the start of the proc?

    If the temp table already exists, does the proc scratch (drop or truncate) it?

    If the temp table really is dropped every time, your issue is something else.  Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does the proc scratch (drop or truncate) it?

    If the temp table really is dropped every time, your issue is something else.  Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.

    I don't think it matters if you do not drop a temporary table (named with only one #), that has been created inside the stored procedure, at the end of a stored procedure. They are automatically dropped on exiting the procedure.

  • LightVader wrote:

    I'm having an issue with a database that I recently moved from SQL 2014 to SQL 2019 (Standard in both cases).  The users get data from the database using stored procedures either through the UI or linked to Excel (using an ODBC).  The users will execute the same procedure multiple times with different parameters expecting to get different result sets to support daily processes.  However, they're not getting the result set they expect.

    As an example - we'll call the stored procedure PricingReport.

    EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '12345';
    EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '56789';

    If the stored procedure gets executed as above, sometimes the user will get the result set for PricePoint = 12345 twice.  If the two procedure calls get executed again immediately after, they might return the results for PricePoint = 56789 twice.

    Under the hood, the stored procedure uses the parameters passed to generate dynamic SQL to populate a temp table and then returns the results in the temp table to the user.

    I was able to replicate the behavior in SSMS, but I'm having trouble finding the right search phrase to figure what setting I need to change to fix this issue.  I've simplified the example code a bit, but I hope it's enough for someone to at least point me in the right direction.

    Is it possible for you to provide an overview of exactly how the table is created and populated, can you supply the code even with out the full SQL so we can see the structure of it as it would be a help.

  • Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does the proc scratch (drop or truncate) it?

    If the temp table really is dropped every time, your issue is something else.  Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.

    I don't think it matters if you do not drop a temporary table (named with only one #), that has been created inside the stored procedure, at the end of a stored procedure. They are automatically dropped on exiting the procedure.

    THAT is why I asked if the table was always created in the proc.

    I have some procs where I can deliberately pass in the temp table table myself; thus, the proc only creates the table and loads it if it doesn't already exist.  I'm sure that's not a common thing, but it is possible.  I just wanted to be absolutely certain before making a definitive statement.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Did you change the Compatibility Level when upgrading from SQL 2014? (e.g. from 120 to 150)

    If so maybe try it back at 120 just as a test for this query?

    All Statistics refreshed after the upgrade?

  • ScottPletcher wrote:

    Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does the proc scratch (drop or truncate) it?

    If the temp table really is dropped every time, your issue is something else.  Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.

    Apparently, I never submitted the post I wrote yesterday...

    There is a little bit of error trapping in the procedure.  At each of the error traps, the temporary tables are dropped before exiting the procedure.  Given that myself and my coworkers are all application developers that had to pick up database development, I can't dismiss the possibility that you could exit the procedure without dropping the temp tables.

    There is no check for the existence of the temp table before the create table statement.



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does the proc scratch (drop or truncate) it?

    If the temp table really is dropped every time, your issue is something else.  Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.

    I don't think it matters if you do not drop a temporary table (named with only one #), that has been created inside the stored procedure, at the end of a stored procedure. They are automatically dropped on exiting the procedure.

    THAT is why I asked if the table was always created in the proc.

    I have some procs where I can deliberately pass in the temp table table myself; thus, the proc only creates the table and loads it if it doesn't already exist.  I'm sure that's not a common thing, but it is possible.  I just wanted to be absolutely certain before making a definitive statement.

    We've started using that technique here in the last few years.  It might be used elsewhere in this database, but not in this procedure.  The most common way we accomplish that is by including a flag in the procedure parameters.



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • Kristen-173977 wrote:

    Did you change the Compatibility Level when upgrading from SQL 2014? (e.g. from 120 to 150)

    If so maybe try it back at 120 just as a test for this query?

    All Statistics refreshed after the upgrade?

    The compatibility level was set to 150 as part of the migration.  I did try changing it back to 120 when I was testing things yesterday, but that didn't seem to fix the issue.  I also played with the settings for parameters sniffing and the cardinality estimator (grasping at anything I can).

    No, the statistics were not refreshed after the upgrade.  I started working on that yesterday, but haven't made it through all the tables in the database yet.



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • Jonathan AC Roberts wrote:

    LightVader wrote:

    I'm having an issue with a database that I recently moved from SQL 2014 to SQL 2019 (Standard in both cases).  The users get data from the database using stored procedures either through the UI or linked to Excel (using an ODBC).  The users will execute the same procedure multiple times with different parameters expecting to get different result sets to support daily processes.  However, they're not getting the result set they expect.

    As an example - we'll call the stored procedure PricingReport.

    EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '12345';
    EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '56789';

    If the stored procedure gets executed as above, sometimes the user will get the result set for PricePoint = 12345 twice.  If the two procedure calls get executed again immediately after, they might return the results for PricePoint = 56789 twice.

    Under the hood, the stored procedure uses the parameters passed to generate dynamic SQL to populate a temp table and then returns the results in the temp table to the user.

    I was able to replicate the behavior in SSMS, but I'm having trouble finding the right search phrase to figure what setting I need to change to fix this issue.  I've simplified the example code a bit, but I hope it's enough for someone to at least point me in the right direction.

    Is it possible for you to provide an overview of exactly how the table is created and populated, can you supply the code even with out the full SQL so we can see the structure of it as it would be a help.

    Here's the majority of the code from the procedure.  I've tried to add comments where I've removed business logic.  There's two temp tables in this code #Nodes2Process and #ReportResults.

    The #Nodes2Process temp table is created with the same columns every time.  That table seems fine.  The #ReportResults temp table has the same first two columns every time but any columns after that would change depending on the @pricePoint and @priceComponent parameters.  Since the columns and data are all added through dynamic SQL, I had add a few print statements yesterday to try to diagnose the issue.  All the dynamic SQL matched what I was expecting, but the results from the final select * from #ReportResults had different columns then what was in the dynamic SQL.

    CREATE PROCEDURE PricingReport (
    @startDate datetime, @endDate datetime,
    @pricePoint varchar(1000), --Pipe delimited list
    @priceComponent varchar(1000) --Pipe delimited list
    ) AS

    --Variable Declaration
    DECLARE @nullNodeID int, @nullFigID int, @distDataInt int, @currNodeID numeric, @currFigID numeric, @nodeCnt int, @hasNode int,
    @currTblName varchar(150), @sql2Run nvarchar(4000), @currDataTable varchar(175),
    @priMinDate datetime, @priMaxDate datetime, @currDate datetime, @myEndDate datetime,
    @dataInterval varchar(10), @currInterval int, @intPerHr int, @intDuration int, @intCnt int, @currColName varchar(256), @hrCnt int,
    @errMsg varchar(255), @nonNumCnt int, @isNonNum int, @fvDataType varchar(16), @fvLength int,
    @dataDefID numeric, @userHasNodeAccess bit, @colList nvarchar(4000)

    --Create a temp table with the nodes & figures to execute
    SELECT n.itemNum, n.splitdata as nodeID, f.splitdata as figureID
    INTO #Nodes2Process
    FROM fnSplitString(@pricePoint, '|') n FULL OUTER JOIN
    fnSplitString(@pricePoint, '|') f ON n.itemNum = f.itemNum

    --ensure that none of the nodeIDs or figureIDs are in the temp table are not null
    --if null, raise error
    SELECT @nullNodeID = COUNT(*) FROM #Nodes2Process WHERE nodeID IS NULL
    SELECT @nullFigID = COUNT(*) FROM #Nodes2Process WHERE figureID IS NULL

    IF @nullNodeID > 0
    BEGIN
    RAISERROR ('Invalid Number of Node IDs Provided. The number of node IDs must match the number of figure IDs. Report processing halted.', 11, 0, 'x')
    DROP TABLE #Nodes2Process
    RETURN
    END

    IF @nullFigID > 0
    BEGIN
    RAISERROR ('Invalid Number of Figure IDs Provided. The number of figure IDs must match the number of node IDs. Report processing halted.', 11, 0, 'x')
    DROP TABLE #Nodes2Process
    RETURN
    END

    /*
    Removed some business logic and reference to specific database tables
    */

    --Add additional columns to the #Nodes2Process temp table
    ALTER TABLE #Nodes2Process ADD dataDefID numeric
    ALTER TABLE #Nodes2Process ADD dataDefName varchar(150)
    ALTER TABLE #Nodes2Process ADD dataInterval varchar(10)
    ALTER TABLE #Nodes2Process ADD tableName varchar(150)
    ALTER TABLE #Nodes2Process ADD nodeName varchar(255)
    ALTER TABLE #Nodes2Process ADD figureName varchar(75)
    ALTER TABLE #Nodes2Process ADD fvDataType varchar(16)
    ALTER TABLE #Nodes2Process ADD fvLength int
    ALTER TABLE #Nodes2Process ADD isNonNum int
    ALTER TABLE #Nodes2Process ADD archTblExists int
    ALTER TABLE #Nodes2Process ADD priMinDate datetime
    ALTER TABLE #Nodes2Process ADD priMaxDate datetime
    ALTER TABLE #Nodes2Process ADD archMinDate datetime
    ALTER TABLE #Nodes2Process ADD userHasNodeAccess bit

    /*
    Removed some business logic and reference to specific database tables to update the columns created above
    */


    --get additional archive and date information about the nodes

    SET @nodeCnt = 1
    SELECT @hasNode = COUNT(nodeID) FROM #Nodes2Process WHERE itemNum = @nodeCnt

    WHILE @hasNode > 0
    BEGIN

    --get information from table
    SELECT @currNodeID = nodeID, @currTblName = tableName, @isNonNum = isNonNum, @dataDefID = dataDefID
    FROM #Nodes2Process
    WHERE itemNum = @nodeCnt

    --create table name
    IF @isNonNum = 1
    BEGIN
    SET @currDataTable = @currTblName + 'FinalStr_' + CAST(@currNodeID AS VARCHAR)
    END
    ELSE
    BEGIN
    SET @currDataTable = @currTblName + 'FinalData_' + CAST(@currNodeID AS VARCHAR)
    END

    --verify that the user has access to the table based on information in the security tables
    SET @userHasNodeAccess = VerifyUserAccessToNode(@currNodeID)

    --get the date and archive values. if no node access, set everything to null
    IF @userHasNodeAccess = 0
    BEGIN
    SET @priMinDate = null
    SET @priMaxDate = null
    SET @archMinDate = null
    SET @archTblExists = 0
    SET @archMinDate = null
    END
    ELSE
    BEGIN
    --get primary and archive min dates & max primary date
    SET @sql2Run = 'SELECT @priMinDate = MIN(dateStamp) FROM ' + @currDataTable
    EXECUTE sp_executesql @sql2Run, N'@priMinDate datetime OUTPUT', @priMinDate = @priMinDate OUTPUT

    SET @sql2Run = 'SELECT @priMaxDate = MAX(dateStamp) FROM ' + @currDataTable
    EXECUTE sp_executesql @sql2Run, N'@priMaxDate datetime OUTPUT', @priMaxDate = @priMaxDate OUTPUT

    END

    --assign values to nodes to process table
    UPDATE #Nodes2Process
    SET priMinDate = @priMinDate, priMaxDate = @priMaxDate
    WHERE nodeID = @currNodeID

    --iterate to the next counter
    SET @nodeCnt = @nodeCnt + 1
    SELECT @hasNode = COUNT(nodeID) FROM #Nodes2Process WHERE itemNum = @nodeCnt

    END

    --create dates
    SELECT TOP 1 @dataInterval = dataInterval FROM #Nodes2Process
    SET @currDate = CASE
    WHEN @dataInterval IN ('5Min', '10Min', '15Min','20Min', '30Min', 'hour', 'day') THEN Cast(Cast(Month(@startDate) as varchar)+ '/' + Cast(Day(@startDate) as varchar)+ '/' + Cast(Year(@startDate) as varchar) as datetime)
    WHEN @dataInterval = 'month' THEN Cast(Cast(Month(@startDate) as varchar)+ '/01/' + Cast(Year(@startDate) as varchar) as datetime)
    WHEN @dataInterval = 'quarter' THEN Cast(Cast(((DatePart(qq, @startDate)-1) * 2) + DatePart(qq, @startDate) as varchar)+ '/01/' + Cast(Year(@startDate) as varchar) as datetime)
    WHEN @dataInterval = 'year' THEN Cast('01/01/' + Cast(Year(@startDate) as varchar) as datetime)
    END

    SET @myEndDate = CASE
    WHEN @dataInterval IN ('5Min', '10Min', '15Min','20Min', '30Min', 'hour', 'day') THEN DateAdd(dd,1,Cast(Cast(Month(@endDate) as varchar)+ '/' + Cast(Day(@endDate) as varchar)+ '/' + Cast(Year(@endDate) as varchar) as datetime))
    WHEN @dataInterval = 'month' THEN DateAdd(mm,1,Cast(Cast(Month(@endDate) as varchar)+ '/01/' + Cast(Year(@endDate) as varchar) as datetime))
    WHEN @dataInterval = 'quarter' THEN DateAdd(qq,1,Cast(Cast(((DatePart(qq, @endDate)-1) * 2) + DatePart(qq, @endDate) as varchar)+ '/01/' + Cast(Year(@endDate) as varchar) as datetime))
    WHEN @dataInterval = 'year' THEN DateAdd(yy,1,Cast('01/01/' + Cast(Year(@endDate) as varchar) as datetime))
    END

    --get interval information
    SELECT @intPerHr = intervalsPerHr, @intDuration = intervalDuration, @intCnt = intervalCnt FROM intervals WHERE dataInterval = @dataInterval


    /*********
    This is the table that seems to be causing my problem
    *********/
    --create initial temp table for report
    CREATE TABLE #ReportResults (dateStamp dateTime NOT NULL, dataInterval int)

    --populate temp table with dates based on interval rollup
    WHILE @currDate < @myEndDate
    BEGIN

    --create the data interval
    SET @currInterval = ((DatePart(hh, @currDate) * @intPerHr) + (DatePart(mi, @currDate) / @intDuration)) + 1

    --insert the row
    INSERT INTO #ReportResults VALUES
    (Cast(Month(@currDate) as varchar)+ '/' + Cast(Day(@currDate) as varchar)+ '/' + Cast(Year(@currDate) as varchar), @currInterval)

    --increment the currDate
    SET @currDate = CASE @dataInterval
    WHEN '5Min' THEN DateAdd(mi, 5, @currDate)
    WHEN '10Min' THEN DateAdd(mi, 10, @currDate)
    WHEN '15Min' THEN DateAdd(mi, 15, @currDate)
    WHEN '20Min' THEN DateAdd(mi, 20, @currDate)
    WHEN '30Min' THEN DateAdd(mi, 30, @currDate)
    WHEN 'hour' THEN DateAdd(hh, 1, @currDate)
    WHEN 'day' THEN DateAdd(dd, 1, @currDate)
    WHEN 'month' THEN DateAdd(mm, 1, @currDate)
    WHEN 'quarter' THEN DateAdd(qq, 1, @currDate)
    WHEN 'year' THEN DateAdd(yy, 1, @currDate)
    END

    END

    --iterate through the node table and add results to final output
    SET @nodeCnt = 1
    SELECT @hasNode = COUNT(nodeID) FROM #Nodes2Process WHERE itemNum = @nodeCnt

    SET @colList = '';
    WHILE @hasNode > 0
    BEGIN

    --get node-figure pair information
    SELECT @currNodeID = nodeID, @currFigID = figureID, @currTblName = tableName, @archTblExists = archTblExists,
    @priMinDate = priMinDate, @isNonNum = isNonNum, @fvDataType = fvDataType, @fvLength = fvLength,
    @userHasNodeAccess = userHasNodeAccess
    FROM #Nodes2Process
    WHERE itemNum = @nodeCnt

    IF @isNonNum = 1
    BEGIN
    SET @currDataTable = @currTblName + 'FinalStr_' + CAST(@currNodeID AS VARCHAR)
    END
    ELSE
    BEGIN
    SET @currDataTable = @currTblName + 'FinalData_' + CAST(@currNodeID AS VARCHAR)
    END

    --create the column
    SET @currColName = ReportColumnName(@currNodeID, @currFigID, null) --Scaler function to create column name for report data
    SET @colList = @colList + ', [' + @currColName + ']';
    SET @sql2Run = 'ALTER TABLE #ReportResults ADD [' + @currColName + '] ' + @fvDataType
    IF @fvLength IS NOT NULL
    BEGIN
    SET @sql2Run = @sql2Run + ' (' + CAST(@fvLength AS VARCHAR) + ')'
    END
    EXECUTE sp_executesql @sql2Run

    --populate the column with result data if user has access to the table
    IF @userHasNodeAccess = 1
    BEGIN
    SET @sql2Run = addCurrRptDataQry(@rollupType, '#ReportResults', @intCnt, @currFigID, @currNodeID, @calcCode, @currDataTable, @startDate, @endDate, '', '', @useArch) --Scaler function to create SQL statement that would populate the #ReprotResults table with data
    PRINT @sql2Run; --Part of my testing yesterday, the expected SQL Statement is returned
    EXECUTE sp_executesql @sql2Run
    END

    --iterate to the next node
    SET @nodeCnt = @nodeCnt + 1
    SELECT @hasNode = COUNT(nodeID) FROM #Nodes2Process WHERE itemNum = @nodeCnt

    END -- end hasNode while

    -- Select the results from the temp table. Adding option (recompile) here seems to resolve the issue
    SELECT * FROM #ReportResults ORDER BY dateStamp, dataInterval


    --clean up temp tables
    DROP TABLE #Nodes2Process
    DROP TABLE #ReportResults


     



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • I can't see how presence of existing TEMP table would cause a problem as

    CREATE TABLE #ReportResults (dateStamp dateTime NOT NULL, dataInterval int)

    would then fail.

    longshot : But ... maybe it is failing, raising an error but somehow?? processing is able to continue.

    Belt and Braces only, but might be worth checking (at top of the Sproc) that TEMP TABLE does not exist

    IF OBJECT_ID('tempdb..#ReportResults') IS NOT NULL
    BEGIN
    RAISERROR ('#ReportResults already exsists.', 11, 0, 'x')
    RETURN
    END

    Perhaps also add

    SET XACT_ABORT ON

    Doesn't feel like either of those are related to the cause though.

  • Kristen-173977 wrote:

    I can't see how presence of existing TEMP table would cause a problem as

    CREATE TABLE #ReportResults (dateStamp dateTime NOT NULL, dataInterval int)

    would then fail.

    longshot : But ... maybe it is failing, raising an error but somehow?? processing is able to continue.

    Belt and Braces only, but might be worth checking (at top of the Sproc) that TEMP TABLE does not exist

    IF OBJECT_ID('tempdb..#ReportResults') IS NOT NULL
    BEGIN
    RAISERROR ('#ReportResults already exsists.', 11, 0, 'x')
    RETURN
    END

    Perhaps also add

    SET XACT_ABORT ON

    Doesn't feel like either of those are related to the cause though.

    I added the check to see if the temp table exists, but that didn't change the behavior I'm seeing.

    I wasn't sure if the SET XACT_ABORT ON should be added before I run the EXEC statement or within the stored procedure.  I put it in the stored procedure and that didn't change anything either.

    So far I've only come across 2 things that got me to the behavior I expect:

    1. Adding option (recompile) to the select * from #reportResults statement OR
    2. Changing the select * from #reportResults to use the specific column names (using dynamic SQL)



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply