May 1, 2008 at 9:56 am
I have a long-running stored procedure that I'd like to speed up. I displayed the estimated execution plan, and found a step that uses 30% of the batch, whereas all of the other (more than 40) steps use no more than 2% each, so I figured that was a good place to start. That step inserts into a temp table, so I've provided the DDL for it, another temp table the insert relies on, the insert itself, and the output from the SET SHOWPLAN_TEXT ON for that step.
I realize that by not providing the DDL for all of the tables involved, I'm shortchanging anyone who might want to do some heavy-duty tuning of this query. I could do that, but what I'd like is just some guidance about anything that screams out 'WHAT WERE YOU THINKING WHEN YOU WROTE IT THIS WAY?' (The answer to that is, I was thinking I need to get the data correct, and then once the flaws appear, at least have a baseline for accuracy when I start to play around with it.)
I also realize the following is just about impossible to read, but what I'm hoping someone will do is point me to something like 'Address the Bookmark Lookup on ucode', or 'that Hash Match is killing performance, try to separate it into another step'. I can't do anything about indexes or keys on the non-temp tables.
Again, thanks for whatever you can suggest. And my apologies for the fact that including the execution plan makes this post annoyingly wider than the screen.
Mattie
CREATE TABLE #FeeInstPaymentPlans (
FeeInstanceID INT,
PaymentPlanID INT,
CollectionStatusCodeIDINT,
CollectionStatusCodeVARCHAR(20),
CollectionStatusDescVARCHAR(60),
CollectionStatusDateDATETIME,
CollectionCompletedDateDATETIME)
CREATE TABLE #TransactionData(
CaseIDINT,
CaseNumberVARCHAR(30),
TransactionIDINT,
TransactionTypeKeyVARCHAR(5),
TransactionDateDATETIME,
NodeIDINT,
TransactionDetailIDBIGINT,
ChargeAmountMONEY,
PaymentAmountMONEY,
CreditAmountMONEY,
fiFeeInstanceIDINT,
fcFincChargeIDINT,
PartyIDINT,
FullNameVARCHAR(200),
TransactionTypeKeyDescVARCHAR(60),
NameSourceVARCHAR(03),
PaymentPlanIDINT,
FeeIDINT,
FeeCategoryIDINT,
FeeCategoryCodeVARCHAR(20),
FeeCategoryDescVARCHAR(60),
FinancialCategoryIDINT,
FinancialCategoryCodeVARCHAR(20),
FinancialCategoryDescVARCHAR(60),
CollectionStatusCodeIDINT,
CollectionStatusCodeVARCHAR(20),
CollectionStatusTextVARCHAR(60),
CollectionStatusDateDATETIME,
CollectionCompletedDateDATETIME,
MaxChargeDateDATETIME,
MaxPaymentDateDATETIME,
MaxCreditDateDATETIME)
INSERT INTO #FeeInstPaymentPlans(
FeeInstanceID ,
PaymentPlanID ,
CollectionStatusCodeID,
CollectionStatusCode,
CollectionStatusDesc,
CollectionStatusDate,
CollectionCompletedDate)
SELECTxPSFI.FeeInstanceIDAS FeeInstanceID,
pp.PaymentPlanIDAS PaymentPlanID,
cs.StatusIDAS CollectionStatusCodeID,
uc.CodeAS CollectionStatusCode,
uc.DescriptionAS CollectionStatusDesc,
cs.StatusDateAS CollectionStatusDate,
cs.CompletedDateAS CollectionCompletedDate
FROMFinancial.dbo.xPmtSchdFeeInstAS xPSFI
INNER JOIN Financial.dbo.PmtSchd AS ps
ON ps.PaymentScheduleID = xPSFI.PaymentScheduleID
INNER JOIN Financial.dbo.PmtPlan AS pp
ON pp.PaymentPlanID = ps.PaymentPlanID
INNER JOIN #TransactionDataAS trd
ON xPSFI.FeeInstanceID = trd.fiFeeInstanceID
LEFT OUTER JOIN (
SELECTMAX(cs.StatusDate)AS MaxStatusDate,
cs.PaymentPlanIDAS PaymentPlanID
FROMFinancial.dbo.CollectionStatusAS cs
WHEREISNULL(cs.CompletedDate, @AsOfDate)
>= @AsOfDate
ANDcs.StatusDate <= @AsOfDate
GROUP BY
cs.PaymentPlanID)AS MaxDate
ONpp.PaymentPlanID = MaxDate.PaymentPlanID
LEFT OUTER JOIN(
SELECTcs.StatusDateAS StatusDate,
cs.PaymentPlanIDAS PaymentPlanID,
MAX(cs.CollectionStatusID)
AS MaxCollectionStatusID
FROMFinancial.dbo.CollectionStatusAS cs
WHEREISNULL(cs.CompletedDate, @AsOfDate)
>= @AsOfDate
--future dates not ok
ANDcs.StatusDate <= @AsOfDate
GROUP BY
cs.PaymentPlanID,
cs.StatusDate)AS MaxID
ONMaxDate.PaymentPlanID = MaxID.PaymentPlanID
ANDMaxDate.MaxStatusDate = MaxID.StatusDate
LEFT OUTER JOINFinancial.dbo.CollectionStatusas cs
ONMaxID.MaxCollectionStatusID = cs.CollectionStatusID
LEFT OUTER JOINFinancial.dbo.uCodeAS uc
ONcs.StatusID = uc.CodeID
WHERECAST(CONVERT(VARCHAR(10), pp.TimestampCreate, 101) AS DATETIME) --PlanStartedDate
<= @AsOfDate
AND CASE
WHEN pp.Stopped = 1THEN CAST(CONVERT(VARCHAR(10), pp.TimestampChange, 101) AS DATETIME)
WHEN pp.PaidOffDate IS NOT NULL THEN CAST(CONVERT(VARCHAR(10), pp.PaidOffDate, 101) AS DATETIME)
ELSE @AsOfDate
END >= @AsOfDate--PlanEndedDate
(1 row(s) affected)
|--Table Insert(OBJECT:([tempdb].[dbo].[#FeeInstPaymentPlans________________________________________________________________________________________________0000000E8762]), SET:([#FeeInstPaymentPlans].[CollectionCompletedDate]=[cs].[CompletedDate], [#FeeInstPaymentPlans].[CollectionStatusDate]=[cs].[StatusDate], [#FeeInstPaymentPlans].[CollectionStatusCodeID]=[cs].[StatusID], [#FeeInstPaymentPlans].[PaymentPlanID]=[pp].[PaymentPlanID], [#FeeInstPaymentPlans].[FeeInstanceID]=[xPSFI].[FeeInstanceID], [#FeeInstPaymentPlans].[CollectionStatusCode]=[uc].[Code], [#FeeInstPaymentPlans].[CollectionStatusDesc]=[uc].[Description]))
|--Top(ROWCOUNT est 0)
|--Bookmark Lookup(BOOKMARK:([Bmk1009]), OBJECT:([Financial].[dbo].[uCode] AS [uc]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([cs].[StatusID]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([Expr1003]))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([cs].[PaymentPlanID], [Expr1001]))
| | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([pp].[PaymentPlanID]))
| | | |--Filter(WHERE:(Convert(Convert([pp].[TimestampCreate])) =[@AsOfDate]))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJECT:([Financial].[dbo].[PmtPlan] AS [pp]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ps].[PaymentPlanID]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([xPSFI].[PaymentScheduleID]))
| | | | | |--Hash Match(Inner Join, HASH:([trd].[fiFeeInstanceID])=([xPSFI].[FeeInstanceID]), RESIDUAL:([xPSFI].[FeeInstanceID]=[trd].[fiFeeInstanceID]))
| | | | | | |--Table Scan(OBJECT:([tempdb].[dbo].[#TransactionData____________________________________________________________________________________________________0000000E8762] AS [trd]))
| | | | | | |--Index Scan(OBJECT:([Financial].[dbo].[xPmtSchdFeeInst].[IXxPmtSchdFeeInst1] AS [xPSFI]))
| | | | | |--Index Seek(OBJECT:([Financial].[dbo].[PmtSchd].[PK_PmtSchd] AS [ps]), SEEK:([ps].[PaymentScheduleID]=[xPSFI].[PaymentScheduleID]) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([Financial].[dbo].[PmtPlan].[PK_PmtPlan] AS [pp]), SEEK:([pp].[PaymentPlanID]=[ps].[PaymentPlanID]) ORDERED FORWARD)
| | | |--Hash Match(Cache, HASH:([pp].[PaymentPlanID]), RESIDUAL:([pp].[PaymentPlanID]=[pp].[PaymentPlanID]))
| | | |--Stream Aggregate(DEFINE:([Expr1001]=MAX([cs].[StatusDate]), [cs].[PaymentPlanID]=ANY([cs].[PaymentPlanID])))
| | | |--Filter(WHERE:(isnull([cs].[CompletedDate], [@AsOfDate])>=[@AsOfDate] AND [cs].[StatusDate]<=[@AsOfDate]))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Financial].[dbo].[CollectionStatus] AS [cs]) WITH PREFETCH)
| | | |--Index Seek(OBJECT:([Financial].[dbo].[CollectionStatus].[IXCollectionStatus3] AS [cs]), SEEK:([cs].[PaymentPlanID]=[pp].[PaymentPlanID]) ORDERED FORWARD)
| | |--Hash Match(Cache, HASH:([cs].[PaymentPlanID], [Expr1001]), RESIDUAL:([cs].[PaymentPlanID]=[cs].[PaymentPlanID] AND [Expr1001]=[Expr1001]))
| | |--Stream Aggregate(DEFINE:([Expr1003]=MAX([cs].[CollectionStatusID])))
| | |--Filter(WHERE:(([Expr1001]=[cs].[StatusDate] AND [cs].[StatusDate] =[@AsOfDate]))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([Financial].[dbo].[CollectionStatus] AS [cs]) WITH PREFETCH)
| | |--Index Seek(OBJECT:([Financial].[dbo].[CollectionStatus].[IXCollectionStatus3] AS [cs]), SEEK:([cs].[PaymentPlanID]=[cs].[PaymentPlanID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([Financial].[dbo].[CollectionStatus].[PK_CollectionStatus] AS [cs]), SEEK:([cs].[CollectionStatusID]=[Expr1003]) ORDERED FORWARD)
|--Index Seek(OBJECT:([Financial].[dbo].[uCode].[PK_uCode] AS [uc]), SEEK:([uc].[CodeID]=[cs].[StatusID]) ORDERED FORWARD)
May 1, 2008 at 1:43 pm
1. Tables scans are bad. Maybe some indexes on the temp tables depending on how large they are.
2. Joining to table valued functions can also significantly impact performance.
We went down the TVF route to encapsulate common joining logic in one project, and we regretted it every step of the way. The only way to get performance back was to undo the TVF.
May 14, 2008 at 9:52 am
Jeremy,
Thanks so much for responding; my apologies for taking so long to get back to this. (Long response time: there's a trend developing here:))
I'm going to take a look at the table scan, and see if I can figure out how to index my temp table. I'm curious what you're referring to by TVF. I would have thought that was where you had a function that returned a table variable, and you tried to join to that resultset. I don't have any of those, because I long ago gave up using table variables, because of miserable response time. (If I were going to use a function like that, I'd toss the results into a temp table before I used it.) What I do have are some derived tables. Are you referring to them, or was the comment just a general observation about table variables?
The other thing I absolutely hate about my code is the necessity to strip the time off my dates for comparison purposes. I can't help but think there's a better way to handle this, especially in the WHERE clause.
Thanks again,
Mattie
May 14, 2008 at 10:24 am
MattieNH (5/1/2008)
The other thing I absolutely hate about my code is the necessity to strip the time off my dates for comparison purposes. I can't help but think there's a better way to handle this, especially in the WHERE clause.
Does this work regarding the date conversion problem?
WHERE
pp.TimestampCreate --PlanStartedDate
between dateadd(dd, datediff(dd,0,@AsOfDate),0) --midnight of @AsOfDate
and dateAdd(ss,-1,dateAdd(dd,1,dateadd(dd, datediff(dd,0,@AsOfDate),0))) --23:59:59 of @AsOfDate
AND CASE
WHEN pp.Stopped = 1 THEN pp.TimestampChange
WHEN pp.PaidOffDate IS NOT NULL THEN pp.PaidOffDate
ELSE @AsOfDate
END between dateadd(dd, datediff(dd,0,@AsOfDate),0) --midnight of @AsOfDate
and dateAdd(ss,-1,dateAdd(dd,1,dateadd(dd, datediff(dd,0,@AsOfDate),0))) --23:59:59 of @AsOfDate
HTH
Dave J
May 14, 2008 at 10:38 am
Dave,
It can't hurt to try. Thanks for putting this together, and I'll let you know how it works. I'm working on the table scan issue right now, and verifying that this step is actually the bottleneck.
Thanks,
Mattie
May 15, 2008 at 11:47 am
Dave,
The logic you provided was actually more elaborate than I needed, but it made me look more closely at my comparisons to get it to work. I ended up adding a new variable
SET@AsOfDateEndOfDay = DATEADD(ss,-1,DATEADD(dd,1,DATEADD(dd, DATEDIFF(dd,0,@AsOfDate),0)))--23:59:59 of @AsOfDate
and then changed the WHERE clause to this
WHEREpp.TimestampCreate --PlanStartedDate
<= @AsOfDateEndOfDay
ANDCASE
WHEN pp.Stopped = 1 THEN pp.TimestampChange
WHEN pp.PaidOffDate IS NOT NULL THEN pp.PaidOffDate
ELSE @AsOfDate--PlanEndedDate
END >= @AsOfDate
It's hard to measure exactly, but it looks like removing the converts cut the run time down on this step from 39 minutes to 8 minutes. Not bad for a day's work, thanks so much for the somewhat less than intuitive code.
Mattie
May 16, 2008 at 1:54 am
Glad to help 🙂
It is now using your indexes, which it couldn't do before as it has to convert every value to see if it matched your criteria.
Declare @AsOfDate datetime
set @AsOfDate = '22 May 2008' -- My Birthday ;0)
--To try and explain, lets break it down.
Select DATEADD(ss,-1,DATEADD(dd,1,DATEADD(dd, DATEDIFF(dd,0,@AsOfDate),0))) --23:59:59 of @AsOfDate
--Change @AsOfDate to GetDate()
Select DATEADD(ss,-1,DATEADD(dd,1,DATEADD(dd, DATEDIFF(dd,0, GetDate()),0))) --23:59:59 of Today
--Take off the outer date add
Select DATEADD(dd,1,DATEADD(dd, DATEDIFF(dd,0, GetDate()),0)) --Midnight Tomorrow
--Take off the outer date add
Select DATEADD(dd, DATEDIFF(dd,0, GetDate()),0) --Midnight Today
--Take off the outer date add
Select DATEDIFF(dd,0, GetDate()) --Number of days since 1 Jan 1900
--The key thing in the lines above is the 0 (zero). It is shorthand for 1 jan 1900 because SQL stores
-- dates as two 4 byte integers and as the base date is 1 Jan 1900, it is represented by 0
HTH
Dave J
May 16, 2008 at 6:42 am
Dave,
All of this has been very helpful. I rearranged your explanation, and added some pseudocode, and it makes perfect sense.
--Number of days since 1 Jan 1900
Select DATEDIFF(dd,0, GetDate())
--Midnight Today
Select DATEADD(dd, 'Number of days since 1 Jan 1900',0)
--Midnight Tomorrow
Select DATEADD(dd,1,'Midnight Today')
--23:59:59 of Today
Select DATEADD(ss,-1,'Midnight Tomorrow')
Thanks again for all your help.
Mattie
May 16, 2008 at 7:31 am
Grand 🙂
I'd like to thank you for taking the time to post a much clearer explanation of what's going on than mine. :blush: 😀
Dave J
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply