July 26, 2010 at 9:21 am
Can anyone explain to me why table variables in a stored procedure can cause issues in SSRS whereas switching them to temp tables works fine.
My stored procedure created a table variable and then inserted under 100 rows to it. When I ran the stored procedure in Visual Studio to check the report, it never completed and I had to stop the execution (20 minutes). When I double-checked the SP in SSMS, it ran in under 10 seconds.
I decided to change the SP to use temp tables instead of the table variable, and this fixed the issue in Visual Studio. Can anyone explain why this occurs? The SP is shown below:
USE [DATABASE_NAME]
GO
/****** Object: StoredProcedure [dbo].[DBSP_AMP_COMMISSION_POS] Script Date: 07/26/2010 09:48:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jared Karney
-- Create date: 7/26/2010
-- Description: Assembles Commission POS Report
-- =============================================
ALTER PROCEDURE [dbo].[DBSP_AMP_COMMISSION_POS]
@START DATETIME,
@END DATETIME
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #COMMISSION_POS_REPORT
(
PAYMENT_DATE DATE,
EmpId VARCHAR(5),
CONTRACT_NUMBER BIGINT,
BOOK_CODE VARCHAR(10),
MAIN_NAME VARCHAR(255),
CONTRACT_AMOUNT DECIMAL(18,2),
POS DECIMAL(18,2),
ARC DECIMAL(18,2),
SLOW DECIMAL(18,2),
FC DECIMAL(18,2),
NOTES VARCHAR(MAX)
)
INSERT INTO #COMMISSION_POS_REPORT
--Begin POS
SELECT CAST(p.PAYMENT_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, p.AMOUNT AS POS,
0.00 AS ARC, 0 AS SLOW, 0 AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND c.PUB_DATE > CAST(p.PAYMENT_DATE AS DATE)
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'PRINCIPAL'
AND p.OBSOLETE_DATE = '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.PAYMENT_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin SLOW
SELECT CAST(p.PAYMENT_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, 0 AS POS,
0 AS ARC, p.AMOUNT AS SLOW, 0 AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND c.PUB_DATE <= CAST(p.PAYMENT_DATE AS DATE)
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'PRINCIPAL'
AND p.OBSOLETE_DATE = '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.PAYMENT_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin FC (Interest)
SELECT CAST(p.PAYMENT_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, 0 AS POS,
0 AS ARC, 0 AS SLOW, p.AMOUNT AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'INTEREST'
AND p.OBSOLETE_DATE = '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.PAYMENT_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin Reversals
--Begin Reversal POS
SELECT CAST(p.OBSOLETE_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, -p.AMOUNT AS POS,
0.00 AS ARC, 0 AS SLOW, 0 AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND c.PUB_DATE > CAST(p.PAYMENT_DATE AS DATE)
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'PRINCIPAL'
AND p.OBSOLETE_DATE <> '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.OBSOLETE_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin Reversal SLOW
SELECT CAST(p.OBSOLETE_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, 0 AS POS,
0 AS ARC, -p.AMOUNT AS SLOW, 0 AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND c.PUB_DATE <= CAST(p.PAYMENT_DATE AS DATE)
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'PRINCIPAL'
AND p.OBSOLETE_DATE <> '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.OBSOLETE_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin Reversal FC (Interest)
SELECT CAST(p.OBSOLETE_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, 0 AS POS,
0 AS ARC, 0 AS SLOW, -p.AMOUNT AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'INTEREST'
AND p.OBSOLETE_DATE <> '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.OBSOLETE_DATE AS DATE) BETWEEN @START AND @END
SELECT PAYMENT_DATE, EmpId, CONTRACT_NUMBER, BOOK_CODE, MAIN_NAME, CONTRACT_AMOUNT,
SUM(POS) AS POS, SUM(ARC) AS ARC, SUM(SLOW) AS SLOW, SUM(FC) AS FC, NOTES
FROM #COMMISSION_POS_REPORT
GROUP BY PAYMENT_DATE, EmpId, CONTRACT_NUMBER, BOOK_CODE, MAIN_NAME, CONTRACT_AMOUNT, NOTES
END
The only difference between this SP and the previous one is the table variable instead of the temp table.
Thanks,
Jared Karney
Jared
CE - Microsoft
July 26, 2010 at 9:35 am
The main difference is that temporary tables can store statistics, but if the result set is returning less than 100 rows anyway and it's from a single statement, you may be better off replacing it with a CTE.
The main thing that's jumping out at me however is that you have dates that are not stored in a date type! If you can change your schema so you don't have to cast these to a date type and index the date columns then it will become SARGable and should run significantly faster
July 26, 2010 at 9:43 am
Thanks for the quick response!
Unfortunately I am not sure what you mean by CTE... I'm sure I will feel like an idiot when you tell me.
In terms of the dates, the table has to store these as a datetime type because of the application that loads the data. These times are also important for us in some other reports. If I cannot change the schema, what would you suggest?
Thanks,
Jared Karney
Jared
CE - Microsoft
July 26, 2010 at 9:47 am
CTE is common Table expression. It works like a temptable
http://msdn.microsoft.com/en-us/library/ms190766.aspx
-Roy
July 26, 2010 at 9:50 am
Sorry, are you saying they're datetime's and you're casting to remove the time part? If so, I'm not sure if this is SARGable, but you could possibly change your method slightly to improve performance - I'll have a play around and get back if no-one else comes back with a definitive answer.
By CTE I mean a Common Table Expression - e.g.:
ALTER PROCEDURE [dbo].[DBSP_AMP_COMMISSION_POS]
@START DATETIME,
@END DATETIME
AS
BEGIN
SET NOCOUNT ON;
;WITH MyCTEName AS (
SELECT CAST(p.PAYMENT_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, p.AMOUNT AS POS,
0.00 AS ARC, 0 AS SLOW, 0 AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND c.PUB_DATE > CAST(p.PAYMENT_DATE AS DATE)
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'PRINCIPAL'
AND p.OBSOLETE_DATE = '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.PAYMENT_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin SLOW
SELECT CAST(p.PAYMENT_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, 0 AS POS,
0 AS ARC, p.AMOUNT AS SLOW, 0 AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND c.PUB_DATE <= CAST(p.PAYMENT_DATE AS DATE)
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'PRINCIPAL'
AND p.OBSOLETE_DATE = '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.PAYMENT_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin FC (Interest)
SELECT CAST(p.PAYMENT_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, 0 AS POS,
0 AS ARC, 0 AS SLOW, p.AMOUNT AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'INTEREST'
AND p.OBSOLETE_DATE = '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.PAYMENT_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin Reversals
--Begin Reversal POS
SELECT CAST(p.OBSOLETE_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, -p.AMOUNT AS POS,
0.00 AS ARC, 0 AS SLOW, 0 AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND c.PUB_DATE > CAST(p.PAYMENT_DATE AS DATE)
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'PRINCIPAL'
AND p.OBSOLETE_DATE <> '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.OBSOLETE_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin Reversal SLOW
SELECT CAST(p.OBSOLETE_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, 0 AS POS,
0 AS ARC, -p.AMOUNT AS SLOW, 0 AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND c.PUB_DATE <= CAST(p.PAYMENT_DATE AS DATE)
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'PRINCIPAL'
AND p.OBSOLETE_DATE <> '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.OBSOLETE_DATE AS DATE) BETWEEN @START AND @END
UNION ALL
--Begin Reversal FC (Interest)
SELECT CAST(p.OBSOLETE_DATE AS DATE), e.EmpId, p.CONTRACT_NUMBER, c.BOOK_CODE, c.MAIN_NAME, c.CONTRACT_AMOUNT, 0 AS POS,
0 AS ARC, 0 AS SLOW, -p.AMOUNT AS FC, '' AS NOTES
FROM AMP_AH_PAYMENTS p
INNER JOIN AMP_AH_CONTRACTS c
ON p.CONTRACT_NUMBER = c.CONTRACT_NUMBER
AND p.AMOUNT <> 0
AND PAYMENT_TYPE = 'INTEREST'
AND p.OBSOLETE_DATE <> '1904-01-01'
INNER JOIN AMPWEBAPP_DB..Employee e
ON p.CHECK_NAME = e.EmpId
WHERE CAST(p.OBSOLETE_DATE AS DATE) BETWEEN @START AND @END
)
SELECT PAYMENT_DATE, EmpId, CONTRACT_NUMBER, BOOK_CODE, MAIN_NAME, CONTRACT_AMOUNT,
SUM(POS) AS POS, SUM(ARC) AS ARC, SUM(SLOW) AS SLOW, SUM(FC) AS FC, NOTES
FROM MyCTEName
GROUP BY PAYMENT_DATE, EmpId, CONTRACT_NUMBER, BOOK_CODE, MAIN_NAME, CONTRACT_AMOUNT, NOTES
END
This way, it will produce an execution plan for the whole statement including the final aggregation which could be more efficient
July 26, 2010 at 9:54 am
Thanks a lot! I was not aware of this and I think it will help me and my team to create more efficient queries.
Thanks,
Jared Karney
Jared
CE - Microsoft
July 26, 2010 at 9:56 am
Yes, the only reason I am casting these is to make my between clause work for the last day. This data must be stored in the table as datetime because for other queries we do utilize the time portion of the field.
I see what you did with the CTE and I am interested to see how this may improve some of my other queries that I use temp tables and table variables for. Is there a best practices on when to use this over temp or table variables?
Thanks,
Jared Karney
Jared
CE - Microsoft
July 26, 2010 at 10:09 am
I'm not sure there's a hard and fast rule - I tend to use CTE's first and only use temp tables if I need store intermediate results and re-use them in more than one query.
The answer is 'it depends' though. Sometimes the optimiser can't find a good plan for a complex query and dividing it into a couple of queries with a temp table is orders of magnitude quicker.
FYI - just checked and casting as a date doesn't prevent an index seek
July 26, 2010 at 10:25 am
I wanted to thank you both again for the information given. I am planning on researching the CTE a bit more to gain a more comprehensive understanding of the differences between that, temp tables, and table variables.
However, in the answers to help me optimize the query, I think we have skipped over my original question. Maybe if I rephrase it a bit... When executing the stored procedures in SSMS (1 with table variable and the other with temp table) the execution time is basically the same for each. When I try to execute a simple report in SSRS with the 2 different SPs, one will not complete its rendering and the other pops up right away. So, with the execution time in SSMS being similar for both SPs, why is there such a marked difference in the way Visual Studio (or SSRS) is rendering the returned data?
Thanks,
Jared Karney
Jared
CE - Microsoft
July 26, 2010 at 10:31 am
Sounds like Parameter Sniffing. Try changing the procedure to not store an execution plan to confirm - e.g. ALTER PROCEDURE ... WITH RECOMPILE AS
It's a big subject so best to do some googling and independent reading but effectively the execution plan is cached for the first set of parameters that are fed to the stored procedure, which can result in a poor plan for other parameters.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply