Stored Procedure "Caching"

Question

Stored Procedure "Caching"

Viewing 15 posts - 16 through 30 (of 30 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

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.

It depends on the exact code. That's why I asked what it was. If one wanted to, one could code it like this:

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL

CREATE TABLE #temp ( ... )

That is, only create the table if it doesn't already exist, otherwise leave it alone (it's up to the caller to make sure the temp table is the correct structure). Again, I do that deliberately in some of my procs. For certain specific situations, it's very helpful.

Rather than assume what you are doing, I wanted to be sure, so I asked.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

LightVader Hall of Fame Points: 3810 More actions · Answer 2

ScottPletcher wrote:

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.
It depends on the exact code. That's why I asked what it was. If one wanted to, one could code it like this:
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
CREATE TABLE #temp ( ... )
That is, only create the table if it doesn't already exist, otherwise leave it alone (it's up to the caller to make sure the temp table is the correct structure). Again, I do that deliberately in some of my procs. For certain specific situations, it's very helpful.
Rather than assume what you are doing, I wanted to be sure, so I asked.

No worries, Scott. I'm out of my depth on this one, so I appreciate the help and ideas I'm getting here. I'm trying to provide as much information as I can without divulging anything company-specific.

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

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.

Actually they're often just renamed internally, although they're inaccessible, due to temp table caching. That makes it more efficient for SQL Server to create the table the next time thru the proc -- it doesn't have to fully recreate, just rename. If you want to, you can prevent that from happening by creating a named constraint on the temp table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

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.

Technically they're often just renamed internally, although they're inaccessible (and thus the equivalent of DROPped from the proc's viewpoint), to allow temp table caching, which makes it more efficient for SQL Server to create the table the next time thru the proc -- it doesn't have to fully recreate, just rename. If you want to, you can prevent that from happening by creating a named constraint on the temp table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

I just started looking at your proc and the first bit of code:

--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 JOIN fnSplitString(@pricePoint, '|') f 
         ON n.itemNum = f.itemNum

I can't see what this does or why it does it like that?

Why are you doing a full join?

The resulting tables from fnSplitString(@pricePoint, '|') n and fnSplitString(@pricePoint, '|') f are going to be identical, so couldn't you use an inner join?

Is that code any different from this with an inner join on the same table?:

;WITH CTE AS
(
    SELECT n.itemNum, 
           n.splitdata
      FROM fnSplitString(@pricePoint, '|') 
)
SELECT n.itemNum, 
       n.splitdata as nodeID, 
       f.splitdata as figureID
  INTO #Nodes2Process
  FROM CTE n 
 INNER JOIN CTE f 
         ON n.itemNum = f.itemNum

LightVader Hall of Fame Points: 3810 More actions · Answer 6

Jonathan AC Roberts wrote:

I just started looking at your proc and the first bit of code:
--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 JOIN fnSplitString(@pricePoint, '|') f 
         ON n.itemNum = f.itemNum
I can't see what this does or why it does it like that?
Why are you doing a full join?
The resulting tables from fnSplitString(@pricePoint, '|') n and fnSplitString(@pricePoint, '|') f are going to be identical, so couldn't you use an inner join?
Is that code any different from this with an inner join on the same table?:
;WITH CTE AS
(
    SELECT n.itemNum, 
           n.splitdata
      FROM fnSplitString(@pricePoint, '|') 
)
SELECT n.itemNum, 
       n.splitdata as nodeID, 
       f.splitdata as figureID
  INTO #Nodes2Process
  FROM CTE n 
 INNER JOIN CTE f 
         ON n.itemNum = f.itemNum

Partially because I mis-copied something when I was getting the code ready to post. That code should read:

--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 JOIN fnSplitString(@priceComponent, '|') f 
         ON n.itemNum = f.itemNum

But even with that change, I think you're still right that it could be changed to an INNER JOIN without affecting the results. If I use @pricePoint = '1|2|3' and @priceComponent = 'a|b|c' I expect the following results in the #nodes2Process table:

itemNum | nodeID | figureID

1 | 1 | a

2 | 2 | b

3 | 3 | c

The fact that the itemNum and nodeID match in this example is coincidental. ItemNum is just the order the values were in in the parameter.

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

LightVader wrote:

Jonathan AC Roberts wrote:
I just started looking at your proc and the first bit of code:
--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 JOIN fnSplitString(@pricePoint, '|') f 
         ON n.itemNum = f.itemNum
I can't see what this does or why it does it like that?
Why are you doing a full join?
The resulting tables from fnSplitString(@pricePoint, '|') n and fnSplitString(@pricePoint, '|') f are going to be identical, so couldn't you use an inner join?
Is that code any different from this with an inner join on the same table?:
;WITH CTE AS
(
    SELECT n.itemNum, 
           n.splitdata
      FROM fnSplitString(@pricePoint, '|') 
)
SELECT n.itemNum, 
       n.splitdata as nodeID, 
       f.splitdata as figureID
  INTO #Nodes2Process
  FROM CTE n 
 INNER JOIN CTE f 
         ON n.itemNum = f.itemNum
Partially because I mis-copied something when I was getting the code ready to post. That code should read:
--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 JOIN fnSplitString(@priceComponent, '|') f 
         ON n.itemNum = f.itemNum
But even with that change, I think you're still right that it could be changed to an INNER JOIN without affecting the results. If I use @pricePoint = '1|2|3' and @priceComponent = 'a|b|c' I expect the following results in the #nodes2Process table:
itemNum | nodeID | figureID
1 | 1 | a
2 | 2 | b
3 | 3 | c
The fact that the itemNum and nodeID match in this example is coincidental. ItemNum is just the order the values were in in the parameter.

I see, I think an inner join would do the same job.

I can't see how your stored procedure is reusing a temporary table from another call. It looks like a bug in SQL Server.

LightVader Hall of Fame Points: 3810 More actions · Answer 8

Jonathan AC Roberts wrote:

LightVader wrote:
Jonathan AC Roberts wrote:
I just started looking at your proc and the first bit of code:
--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 JOIN fnSplitString(@pricePoint, '|') f 
         ON n.itemNum = f.itemNum
I can't see what this does or why it does it like that?
Why are you doing a full join?
The resulting tables from fnSplitString(@pricePoint, '|') n and fnSplitString(@pricePoint, '|') f are going to be identical, so couldn't you use an inner join?
Is that code any different from this with an inner join on the same table?:
;WITH CTE AS
(
    SELECT n.itemNum, 
           n.splitdata
      FROM fnSplitString(@pricePoint, '|') 
)
SELECT n.itemNum, 
       n.splitdata as nodeID, 
       f.splitdata as figureID
  INTO #Nodes2Process
  FROM CTE n 
 INNER JOIN CTE f 
         ON n.itemNum = f.itemNum
Partially because I mis-copied something when I was getting the code ready to post. That code should read:
--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 JOIN fnSplitString(@priceComponent, '|') f 
         ON n.itemNum = f.itemNum
But even with that change, I think you're still right that it could be changed to an INNER JOIN without affecting the results. If I use @pricePoint = '1|2|3' and @priceComponent = 'a|b|c' I expect the following results in the #nodes2Process table:
itemNum | nodeID | figureID
1 | 1 | a
2 | 2 | b
3 | 3 | c
The fact that the itemNum and nodeID match in this example is coincidental. ItemNum is just the order the values were in in the parameter.
I see, I think an inner join would do the same job.
I can't see how your stored procedure is reusing a temporary table from another call. It looks like a bug in SQL Server.

That's annoying, but I feel better that it's not something obvious I'm missing.

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 9

It's more likely a bug in the app. When it gets the result set from the temp table, what does the app do with it?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

LightVader Hall of Fame Points: 3810 More actions · Answer 10

ScottPletcher wrote:

It's more likely a bug in the app. When it gets the result set from the temp table, what does the app do with it?

The UI displays the data in a table (.NET WPF DataGrid). Excel creates a query table in the worksheet. And in SSMS I'm just displaying the data in the results window. All three display the same behavior.

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 11

ScottPletcher wrote:

It's more likely a bug in the app. When it gets the result set from the temp table, what does the app do with it?

LightVader said in the first post "I was able to replicate the behaviour in SSMS".

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 12

Jonathan AC Roberts wrote:

I see, I think an inner join would do the same job.
I can't see how your stored procedure is reusing a temporary table from another call. It looks like a bug in SQL Server.

An INNER join will only work if both parameters *always* have the same number of items. If @pricePoint = '1' and @priceComponent = 'a|b|c' you want 3 rows returned. Joining on the ItemNumber - which is the return value from the split would only join the first value and the other price components would not be included.

If ItemNumber is not used anywhere - then it doesn't matter what value is used, but if the expectation is that the value increases for each row and you can have different numbers of items in each parameter - you would then have an issue. For example:

Declare @pricePoint varchar(30) = '1'
      , @priceComponent varchar(30) = 'a|b|c';

 Select *
   From dbo.DelimitedSplit8K(@pricePoint, '|')                  pp
   Full Join dbo.DelimitedSplit8K(@priceComponent, '|')         pc On pc.ItemNumber = pp.ItemNumber

Results in:

If you need an incrementing key value in this table - replace n.ItemNum with ItemNum = identity(int,1,1). This will ensure that you don't get any NULL values for the ItemNum column. If you do get NULL values here, then in your later code where you use ItemNum is going to 'skip' those rows:

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

/* other code */

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

I also noticed you use 'varchar' without a length - I would recommend always specifying the length and not relying on the default length to be enough.

To be safe - you can include a DROP TABLE IF EXISTS before the creation of any temp tables. Example:

DROP TABLE IF EXISTS #nodes2Process;

SELECT ...
  INTO #nodes2Process
  FROM ...
 WHERE ...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 13

Jeffrey Williams wrote:

Jonathan AC Roberts wrote:
I see, I think an inner join would do the same job.
I can't see how your stored procedure is reusing a temporary table from another call. It looks like a bug in SQL Server.
An INNER join will only work if both parameters *always* have the same number of items. If @pricePoint = '1' and @priceComponent = 'a|b|c' you want 3 rows returned. Joining on the ItemNumber - which is the return value from the split would only join the first value and the other price components would not be included.

The procedure raises an error and returns without doing anything if any nulls are found in the full join.

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

So clearly there aren't supposed to a different number of rows in each parameter.

LightVader Hall of Fame Points: 3810 More actions · Answer 14

Jonathan AC Roberts wrote:

Jeffrey Williams wrote:
Jonathan AC Roberts wrote:
I see, I think an inner join would do the same job.
I can't see how your stored procedure is reusing a temporary table from another call. It looks like a bug in SQL Server.
An INNER join will only work if both parameters *always* have the same number of items. If @pricePoint = '1' and @priceComponent = 'a|b|c' you want 3 rows returned. Joining on the ItemNumber - which is the return value from the split would only join the first value and the other price components would not be included.
The procedure raises an error and returns without doing anything if any nulls are found in the full join.
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
So clearly there aren't supposed to a different number of rows in each parameter.

Yeah, I started typing a response and then work interrupted. The expectation is that the two parameters would have the same number of pipe delimited items. A user could for example want to see the 5 components that make up the total price for pricePoint = 1. Or they could want to see the same priceComponent for 5 different pricePoints or any combination in between.

The DROP TABLE IF EXISTS is new to me though. I'll have to look into that statement a little more.

I also saw your point about using VARCHAR datatype without a length. I'm going to pass the buck on that one and blame my boss. 🙂 On a more serious note, I'll work on changing that. I know there's some upcoming work on this system so I might be able to fit in a little clean up then.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 15

Jonathan AC Roberts wrote:

Jeffrey Williams wrote:
Jonathan AC Roberts wrote:
I see, I think an inner join would do the same job.
I can't see how your stored procedure is reusing a temporary table from another call. It looks like a bug in SQL Server.
An INNER join will only work if both parameters *always* have the same number of items. If @pricePoint = '1' and @priceComponent = 'a|b|c' you want 3 rows returned. Joining on the ItemNumber - which is the return value from the split would only join the first value and the other price components would not be included.
The procedure raises an error and returns without doing anything if any nulls are found in the full join.
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
So clearly there aren't supposed to a different number of rows in each parameter.

That is why I asked if that was the case - based on the original statement the @pricePoint parameter is being passed, but the @priceComponent parameter is not being passed. If they were passing the same number of items in each list - then using an INNER JOIN eliminates the need to check for NULL in either column.

There could also be a problem with passing 'blank' values - if @pricePoint = '1||2' and @priceComponent = 'a|b|c' the checks will be passed but there is now a blank NodeID for row 2.

Maybe a better check would be to validate the number of delimiters at the beginning:

     IF (len(@pricePoint) - len(replace(@pricePoint, '|', '')) <> len(@priceComponent) - len(replace(@priceComponent, '|', '')))
  BEGIN
        RAISERROR('Invalid Number of parameters.  The number of node IDs must match the number of figure IDs.  Report processing halted.', 11, 0) WITH nowait;
        RETURN;
    END

And then - check if any of the passed in values are blank.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs