May 27, 2009 at 12:11 pm
Folks,
I work for the entertainment industry and there is a requirement to produce a daily report showing the total customer token usage so far and the total token usage in the last 24 hours.
A token is a unique 12 digit code that the customers redeems in order to watch a movie.
To implement this report, i have written a stored procedure that sums up the total customer token count and then inserts into a staging table.
In the same stored procedure i have a written a update statement that basically does a update to the staging table for finding the customer token usage in the last 24 hours.
This report has been in Production since last 5 months and usually takes about 3 minutes to run.
Last week i introduced an additional clause in a CASE statement and since then it has been taking an hour and half to complete instead of 3 minutes.
I noticed that the execution of the stored procedure hungs at the UPDATE statement for the entire duration.
I am surprised to see this change. What could be the cause? I tried recompiling the stored procedure but still takes an hour and half.
Any help will be greatly appreciated.
Thanks,
Amol
Amol Naik
May 27, 2009 at 12:24 pm
Can't help fix something if we don't have the code, the DDL for the tables involved, sample data (in a readily consummable format), expected results based on the sample data.
Please read the first article I reference below in my signature block to see how to best post questions for the best response.
Mind readers we are not.
May 27, 2009 at 12:45 pm
Forgot to mention one important thing:-
The execution of the stored procedure hungs at the UPDATE statement for the entire duration.
--Staging table script:
CREATE TABLE [dbo].[TokenUsageStatistics](
[ReportDate] [datetime] NULL,
[DiscDescription] [varchar](100) NULL,
[StreetDate] [datetime] NULL,
[TokensUsedInLast24Hours] [int] NULL,
[TotalTokensUsed] [int] NULL,
[LastTokenUsedDateTime] [datetime] NULL,
[DiscGUID] [uniqueidentifier] NULL,
[AffiliateGUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TokenUsageStatistics] ADD CONSTRAINT [DF_TokenUsageStatistics_TokensUsedInLast24Hours] DEFAULT ((0)) FOR [TokensUsedInLast24Hours]
GO
--Stored Procedure
SET NOCOUNT ON
BEGIN
DECLARE @Today DATETIME
DECLARE @YesterdayFrom DATETIME
DECLARE @YesterdayTo DATETIME
DECLARE @Date VARCHAR(10)
SET @Date = CONVERT(VARCHAR(10), GETDATE() - 1, 101)
SET @YesterdayFrom = CAST(@Date + ' 00:00:00.000' AS DATETIME)
SET @YesterdayTo = CAST(@Date + ' 23:59:59.997' AS DATETIME)
SET @Today = GETDATE()
--Discs that do not have AffiliateGUIDs
INSERT dbo.TokenUsageStatistics
(
ReportDate,
DiscDescription,
StreetDate,
TokensUsedInLast24Hours,
TotalTokensUsed,
LastTokenUsedDateTime,
DiscGUID
)
SELECT @Today,
[DiscDescription],
[StreetDate],
ISNULL([TokensUsedInLast24Hours], 0) AS TokensUsedInLast24Hours,
ISNULL([TotalTokensUsed], 0) AS TotalTokensUsed,
[LastTokenUsedDateTime],
DiscGUID
FROM dbo.[StageTokenUsageXMLData] WITH ( NOLOCK )
WHERE StreetDate 0
AND UpdatedDateTime <= @YesterdayTo
AND DiscGUID NOT IN (
'052E8AC7-233F-4D1A-BE24-3058EB7BDEB9' )
GROUP BY DiscGUID,
AffiliateGUID
) AS D
ON A.AffiliateGUID = D.AffiliateGUID
INNER JOIN dbo.StageTokenUsageXMLData S
ON S.DiscGUID = D.DiscGUID
AND S.ContentProviderID = 78
WHERE S.StreetDate 0
AND (
UpdatedDateTime >= @YesterdayFrom
AND UpdatedDateTime <= @YesterdayTo
)
AND DiscGUID NOT IN (
'052E8AC7-233F-4D1A-BE24-3058EB7BDEB9' )
GROUP BY DiscGUID,
AffiliateGUID
) AS tmp
GROUP BY DiscGUID,
AffiliateGUID
) AS T
ON S.DiscGUID = T.DiscGUID
AND S.AffiliateGUID = T.AffiliateGUID
Hope this helps.
Thanks,
Amol
Amol Naik
May 27, 2009 at 1:33 pm
Nope. No sample data or expected results.
Please read the first article I have referenced below in my signature block.
May 27, 2009 at 1:49 pm
And, seeing as it is a performance problem, execution plan (saved as a .sqlplan file, zipped and attached) and index definitions (if there are any indexes)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2009 at 3:03 pm
I just edited my post which probably would have gone unnoticed, i am adding that here again:-
The Stored Procedure hangs indefinitely at the UPDATE statement.
Just to re-iterate, all i did was extend the CASE statement to include an additional condition, the resulted in the Stored procedure taking an hour and half to complete and the execution hangs at the UPDATE statement.
Thanks again,
Amol
Amol Naik
May 27, 2009 at 3:07 pm
Amol.Naik (5/27/2009)
I just edited my post which probably would have gone unnoticed, i am adding that here again:-The Stored Procedure hangs indefinitely at the UPDATE statement.
Just to re-iterate, all i did was extend the CASE statement to include an additional condition, the resulted in the Stored procedure taking an hour and half to complete and the execution hangs at the UPDATE statement.
Thanks again,
Amol
Still don't have the other items requested by myself or Gail.
June 8, 2009 at 1:06 pm
Strange but the Stored Procedure got fixed on its own since last week, meaning it's back to 3 minutes now. I didn't change anything.
Thanks anyways.
Amol
Amol Naik
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply