July 3, 2013 at 1:23 am
Steve
Take another look at this part of the query:
SELECT
SVPromoInteractions.RefKey
FROM SVPromoInteractions
INNER JOIN (
SELECT
RefKey,
MIN(ContactDate) ContactDate -- Finds the oldest interaction, in case there are more than one 321 rec
FROM SVPromoInteractions
WHERE INum = @INum
AND PromoOrRef = 'R'
AND ResultStatus = 321
AND DeleteFlg = 'N'
GROUP BY RefKey
) GroupedRecs
ON SVPromoInteractions.RefKey = GroupedRecs.RefKey
AND SVPromoInteractions.ContactDate = GroupedRecs.ContactDate
WHERE INum = @INum
AND OrgNum = @OrgNum -- Referrer
AND ResultStatus = 321 -- Referred
GROUP BY
SVPromoInteractions.RefKey
I'm sure there's scope for reducing this to a single more efficient query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 5, 2013 at 9:05 am
I would love to modify the entire SELECT statement to use an alternative, faster method than the aggregated query in play. I have attached the actual execution plan if anyone has any ideas.
July 6, 2013 at 2:48 am
If I understand the query correctly, the task is to find the RefKey for the interactions which INum is @INum, PromoOrRef is R, ResultStatus is 312 and DeleteFlg is N - but only if the first contact date has OrgNum = @OrgNum.
In SQL 2008, this can be written more effeciently thanks to the row_number function introduced in SQL 2005. This permits you to number the rows and then you can filter rows in an outer query. The query below also features a Common Table Expression (CTE), which is basically the same thing as a derived table as used in the original query, but since the CTE it has a name, it can be referred to in multiple places in the query. (They can also be recursive.)
Please test this query carefully, not only for performance, but also for correctness!
; WITH numbering AS (
SELECT RefKey, OrgNum,
row_number() OVER(PARTITION BY RefKey ORDER BY ContactDate) AS rowno
FROM SVPromoInteractions
WHERE INum = @INum
AND PromoOrRef = 'R'
AND ResultStatus = 321
AND DeleteFlg = 'N'
)
SELECT RefKey
FROM numbering
WHERE rowno = 1
AND OrgNum = @OrgNum
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 6, 2013 at 12:53 pm
Thanks for the CTE recommendation. I will have to try it out and compare performance to the old method.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply