July 9, 2008 at 1:14 am
Grant Fritchey (7/8/2008)
Jeff will beat me for saying this, but according to the plan that is an added cost that is not present in the SELECT * plan. I'd just be curious to see if it makes a difference.
To quote a really good person who wrote a book on this stuff... "Its a good place to start"... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2008 at 1:56 am
Why the five almost identical LEFT JOINs?
SELECTLName,
FName,
Address1,
Address2,
City,
[State],
Zip,
Phone,
SSN,
0 AS [Basic],
x.EL AS Optional,
x.BAD AS EmployeeVADD,
x.VAD AS FamilyVADD,
x.SPL AS Spouse,
x.CHL AS Child,
DOB
FROMPersonalData
INNER JOINCensus ON Census.TrackingID = PersonalData.TrackingID
INNER JOINClientPlan ON ClientPlan.PlanID = Census.PlanID
INNER JOIN(
SELECTTrackingID,
MAX(CASE WHEN BenefitType = 'EL' THEN Amount3 ELSE NULL END) AS EL,
MAX(CASE WHEN BenefitType = 'BAD' THEN Amount3 ELSE NULL END) AS BAD,
MAX(CASE WHEN BenefitType = 'VAD' THEN Amount3 ELSE NULL END) AS VAD,
MAX(CASE WHEN BenefitType = 'SPL' THEN Amount3 ELSE NULL END) AS SPL,
MAX(CASE WHEN BenefitType = 'CHL' THEN Amount3 ELSE NULL END) AS CHL
FROM(
SELECTTrackingID,
Amount3,
BenefitType,
ROW_NUMBER() OVER (PARTITION BY BenefitType ORDER BY EmpBenefitID DESC) AS RecID
FROMEmpBenefits
WHEREPYBegin = '7/1/2008'
AND CoverageTier <> 'N'
EndDate IS NOT NULL
AND BenefitType IN ('EL', 'BAD', 'VAD', 'SPL', 'CHL')
) AS w
WHERERecID = 1
GROUP BYTrackingID
) AS x ON x.TrackingID = Census.TrackingID
WHEREClientPlan.ClientID = 'CSMC'
N 56°04'39.16"
E 12°55'05.25"
July 9, 2008 at 6:26 am
Brian Tax (7/8/2008)
I have solved this, but I am an not sure why. If I add the column EmpBen.TrackingID it runs fast and the plan is differnent, it uses a bunch more parallelism. I have attached the two plans (remove .txt). Why are the plans so different and the run time so much faster?Thanks, Brian
It's really hard to compare a plan with parallelism to one without. They just behave differently. Probably, by including that column, the optimizer was able to recognize a path similar to what it had available when you did SELECT *. After all, both would include that column.
I was looking at the query some more. Have you tried using a TOP (1) instead of performaning an aggregate to the get the EmpBenefitId? It looks like the ORDER BY would be with an index. I've generally, but not always, found that to perform faster.
Also, instead of joining to this:
INNER JOIN
(SELECT EmpBenefits.TrackingID
FROM EmpBenefits INNER JOIN
(SELECT MAX(EmpBenefitID) EmpBenefitID
FROM EmpBenefits
WHERE CoverageTier <> 'N'
AND PYBegin = '7/1/2008'
AND BenefitType IN ('EL', 'BAD', 'VAD', 'SPL', 'CHL')
GROUP BY TrackingID, PYBegin, BenefitType) A
ON EmpBenefits.EmpBenefitID = A.EmpBenefitID
WHERE EndDate IS NOT NULL
GROUP BY EmpBenefits.TrackingID) EmpBen
ON Census.TrackingID = EmpBen.TrackingID
and then joining to the other values, you might consider eliminating this join and trying an OR clause to look for IS NOT NULL values from the other derived tables. The IN clause is just an OR clause anyway and you'll reduce the number of derived tables you're dealing with, reducing the complexity of the plan.
These are just ideas for experimentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2008 at 7:39 am
Just for the sake of curiosity about performance on this whole thing, can I ask you to try out this:
;WITH
TopBene (BenefitType, BenefitID) as -- Selects top ID by type
(select BenefitType, max(EmpBenefitID)
from dbo.EmpBenefits
where PYBegin = '7/1/2008'
and BenefitType in ('EL', 'BAD', 'VAD', 'SPL', 'CHL')
and CoverageTier != 'N'
group by BenefitType),
Benefits (TrackingID, BenefitType, Amount3, EndDate, CoverageTier) as
(select trackingid, empbenefits.benefittype, amount3, enddate, coveragetier
from dbo.EmpBenefits
inner join TopBene
on EmpBenefits.BenefitID = TopBene.BenefitID
and EmpBenefits.BenefitType = TopBene.BenefitType)
SELECT LName, FName, Address1, Address2, City, [State], Zip, Phone, SSN, 0 [Basic],
EmpBen_EL.Amount3 Optional,
EmpBen_VAD.Amount3 EmployeeVADD,
EmpBen_VAD.Amount3 FamilyVADD,
EmpBen_SPL.Amount3 Spouse,
EmpBen_CHL.Amount3 Child, DOB
-- Select all columns
--SELECT *
FROM PersonalData
INNER JOIN Census
ON PersonalData.TrackingID = Census.TrackingID
AND exists
(SELECT *
FROM EmpBenefits
WHERE CoverageTier != 'N'
AND PYBegin = '7/1/2008'
AND BenefitType IN ('EL', 'BAD', 'VAD', 'SPL', 'CHL')
AND Census.TrackingID = EmpBenefits.TrackingID)
INNER JOIN ClientPlan
ON Census.PlanID = ClientPlan.PlanID
-- get individual benefit data if available
LEFT OUTER JOIN Benefits EmpBen_EL
on Census.TrackingID = EmpBen_EL.TrackingID
and EmpBen_EL.BenefitType = 'EL'
-- get individual benefit data if available
LEFT OUTER JOIN Benefits EmpBen_BAD
on Census.TrackingID = EmpBen_BAD.TrackingID
and EmpBen_BAD.BenefitType = 'BAD'
-- get individual benefit data if available
LEFT OUTER JOIN Benefits EmpBen_VAD
on Census.TrackingID = EmpBen_VAD.TrackingID
and EmpBen_VAD.BenefitType = 'VAD'
-- get individual benefit data if available
LEFT OUTER JOIN Benefits EmpBen_SPL
on Census.TrackingID = EmpBen_SPL.TrackingID
and EmpBen_SPL.BenefitType = 'SPL'
-- get individual benefit data if available
LEFT OUTER JOIN Benefits EmpBen_CHL
on Census.TrackingID = EmpBen_CHL.TrackingID
and EmpBen_CHL.BenefitType = 'CHL'
WHERE ClientPlan.ClientID = 'CSMC'
Another option that might be better would be to move the data from a CTE to a temp table, and index that temp table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 10:02 am
Just a little more background on this, the EmpBenefits table has many records for many different people. I need to look for the highest level record for a each person that was selected based on having at least one of the specified benefits that has an end date. The problem is that I need to find all people that have at least one of those benefits and have them shown all on one line. (Reporting is always a pain in the $).
So for Peso’s suggestion, that is just pulling the Max record for each benefit type, but I need one record for each person that has at leas one of those benefits.
For GSquared, yes using Temp tables is very fast also.
Thanks for all of your help, as has always been my experience, SQL Server tuning is more art that science. Sometimes the smallest changes make a huge difference and we can’t always explain why.
July 9, 2008 at 10:31 am
Peso, I think that I was able to modify your code to get what I need. We were using SQL 7, so long that I forget about the new features of 2005 sometimes.
SELECT LName,
FName,
Address1,
Address2,
City,
[State],
Zip,
Phone,
SSN,
0 AS [Basic],
x.EL AS Optional,
x.BAD AS EmployeeVADD,
x.VAD AS FamilyVADD,
x.SPL AS Spouse,
x.CHL AS Child,
DOB
FROM PersonalData
INNER JOIN Census
ON Census.TrackingID = PersonalData.TrackingID
INNER JOIN ClientPlan
ON ClientPlan.PlanID = Census.PlanID
INNER JOIN
(SELECT TrackingID,
MAX(CASE WHEN BenefitType = 'EL' THEN Amount3 ELSE NULL END) AS EL,
MAX(CASE WHEN BenefitType = 'BAD' THEN Amount3 ELSE NULL END) AS BAD,
MAX(CASE WHEN BenefitType = 'VAD' THEN Amount3 ELSE NULL END) AS VAD,
MAX(CASE WHEN BenefitType = 'SPL' THEN Amount3 ELSE NULL END) AS SPL,
MAX(CASE WHEN BenefitType = 'CHL' THEN Amount3 ELSE NULL END) AS CHL,
MIN(EndDate) AS EndDate
FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY TrackingID, BenefitType ORDER BY EmpBenefitID DESC) AS RecID
FROM EmpBenefits
WHERE PYBegin = '7/1/2008'
AND CoverageTier <> 'N'
AND BenefitType IN ('EL', 'BAD', 'VAD', 'SPL', 'CHL')) AS w
WHERE RecID = 1
AND EndDate IS NOT NULL
GROUP BY TrackingID) AS x
ON x.TrackingID = Census.TrackingID
WHERE ClientPlan.ClientID = 'CSMC'
July 9, 2008 at 11:51 am
How do the query plan look like now?
N 56°04'39.16"
E 12°55'05.25"
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply