March 31, 2015 at 11:56 am
Hi can someone help me with optimizing this query
--------------------------------------------------------------------------------------------------------------
/* BA_ImmPen_General_Mapping.sql
2014JUL25 RLEGARE:AJOUT PK & indexes, car table sera utilisée suffisamment pour justifier cet ajout.
2014jul24 rlegare:Code Révisé et resimplifié quand possible, même fonctionalité cependant.
Peut-être un peu de fine tuning à venir. Sinon est fonctionel.
*/
/*-----------------------------------------------------------------------------------------------------
VARIABLES pour SSIS:À activer que pour tester le présent document de façon individuelle.
*/
--DECLARE @MAPPING_BD nvarchar(Max)= 'ICBC_IT1_Mapping'
--,@ARIEL_BD nvarchar(Max)= 'ICBC_DataTst1'
--,@CLIENT_BD nvarchar(Max)= 'ICBC_IT1_ClientData'
--,@ConversionDate DATE= '2015-jan-08' -- SYSDATETIME()
--USE [ICBC_IT1_Mapping];
/*-----------------------------------------------------------------------------------------------------
VARIABLES pour ce script.
*/
SET NOCOUNT ON
/*-----------------------------------------------------------------------------------------------------
Tab_ImmPen_General
*/
PRINT CAST(sysdatetime()as varchar(max))+' START.' -- OPTIONEL ça donne une idée timing des steps...
BEGIN TRY DROP TABLE dbo.Tab_ImmPen_General END TRY BEGIN CATCH END CATCH; -- old name was TEMP_ImmPen_100_MainWork
SELECT
I1.Immediate_Pension_Data_UID,
I1.MS_UniquePenID,
I1.PARTICIPANT_ID,
I1.PLAN_ID,
B1.PersonType,
B1.MemberID,
I1.CLIENT_PIN,
M1.PersonID,
M1.MembershipID,
B1.ParticipationID,
B1.ParticipationType,
-- CL 2015-02-26 Pour charger les rentes de Marital Breakdown dans la table Tab_PayablePensions_MaritalBreakdowns_FINAL
-- TCI1.Ariel_Table,
CASE
WHEN I1.[EVENT] IN ('Marriage Breakdown')
THEN 'MaritalBreakdowns'
ELSE TCI1.Ariel_Table
END AS Ariel_Table,
I1.[EVENT],
-- CL 20150121 Suite au commentaire de Ratiba, modification du script pour que l'on réajuste la date de calcul en fonction des réindexations.
--CASE
--WHEN I1.[START_DATE] >= I1.EFFECTIVE_DATE THEN I1.EFFECTIVE_DATE
--ELSE I1.[START_DATE]
--END AS CalculationDate,
I1.EFFECTIVE_DATE AS CalculationDate,
R1.MinNormCalcDate AS RetirementDate,
0 AS RetirementType, -- 2014jul24 rlegare: 0=Undefined as long as 6=Deferred is properly updated later.
CASE
WHEN I1.[EVENT] IN ('Marriage Breakdown') OR TCI1.Ariel_Table IN ('MaritalBreakdowns')
THEN 'PensionToTransfer' -- 7=MaritalBreak. see TCVPensionSource
ELSE TCI1.Source_Code
END AS PensionSource_Symbol,
CASE
WHEN B1.PersonType = 'Member'THEN 1 -- SELECT * FROM ARIEL.TCVHolderType
WHEN B1.PersonType IN ('Spouse','ExSpouse')THEN 2
WHEN B1.PersonType IN ('Beneficiary')THEN 3
ELSE NULL
END AS HolderType,
-- CL 20150323 Correction defect 35 section 3
-- TCI1.Renom_Code as PensionName_Symbol,
CASE
WHEN I1.LEVEL_INCOME_OPTION_FLAG = 'Y' AND Pl.ARIEL_CODE like '%ICBC_MGMT%' AND I1.PENSION_TYPE like '%Post 2009 Life Pension%'
AND DATEDIFF(DAY, I1.START_DATE, DATEADD(YEAR, 65, DATEADD(MONTH, DATEDIFF(MONTH, 0, B1.DATE_OF_BIRTH)+1, 0)) ) > 0
THEN 'PensionBef65YearsOldPost09'
WHEN I1.LEVEL_INCOME_OPTION_FLAG = 'Y' AND Pl.ARIEL_CODE like '%ICBC_MGMT%' AND I1.PENSION_TYPE like '%Post 2009 Life Pension%'
AND DATEDIFF(DAY, I1.START_DATE, DATEADD(YEAR, 65, DATEADD(MONTH, DATEDIFF(MONTH, 0, B1.DATE_OF_BIRTH)+1, 0)) ) <= 0
THEN 'PensionAft65YearsOldPost09'
WHEN I1.LEVEL_INCOME_OPTION_FLAG = 'Y' AND I1.PENSION_TYPE like 'Life Pension'
AND DATEDIFF(DAY, I1.START_DATE, DATEADD(YEAR, 65, DATEADD(MONTH, DATEDIFF(MONTH, 0, B1.DATE_OF_BIRTH)+1, 0)) ) > 0
THEN 'PensionBefore65YearsOld'
WHEN I1.LEVEL_INCOME_OPTION_FLAG = 'Y' AND I1.PENSION_TYPE like 'Life Pension'
AND DATEDIFF(DAY, I1.START_DATE, DATEADD(YEAR, 65, DATEADD(MONTH, DATEDIFF(MONTH, 0, B1.DATE_OF_BIRTH)+1, 0)) ) <= 0
THEN 'PensionAfter65YearsOld'
ELSE TCI1.Renom_Code
END AS PensionName_Symbol,
CASE
WHEN I1.[START_DATE] >= I1.EFFECTIVE_DATETHEN I1.[START_DATE]
ELSE I1.EFFECTIVE_DATE
END AS PaymentStartDate,
CASE
-- CL 20150323 Pour corriger le defect 35
-- Pour réajuter les dates de fins des rentes nivelées
WHEN I1.LEVEL_INCOME_OPTION_FLAG = 'Y' AND I1.END_DATE IS NOT NULL AND I3.PARTICIPANT_ID IS NOT NULL
THEN DATEADD(DAY,-1,I3.EFFECTIVE_DATE)
WHEN I1.END_DATE IS NULL AND I2.PARTICIPANT_ID IS NOT NULL
AND DATEDIFF(DAY,I2.EFFECTIVE_DATE,I2.[START_DATE]) >= 0
THEN DATEADD(DAY,-1,I2.[START_DATE])
WHEN I1.END_DATE IS NULL AND I2.PARTICIPANT_ID IS NOT NULL
AND DATEDIFF(DAY,I2.EFFECTIVE_DATE,I2.[START_DATE]) < 0
THEN DATEADD(DAY,-1,I2.EFFECTIVE_DATE)
WHEN I1.END_DATE IS NULL AND I2.PARTICIPANT_ID IS NULL
AND B1.DATE_OF_DEATH IS NOT NULL
THEN B1.DATE_OF_DEATH
WHEN (I1.END_DATE IS NULL)AND (I2.PARTICIPANT_ID IS NULL)
AND (I1.OPTION_STATUS IN ('Expired') OR SL1.Ariel_Status IN ('TerminatedWithoutBenefits') )
-- 2014jul24 rlegare. select * from ariel.TCVMembershipStatus
THEN DATEADD(DAY,-1,CAST(@ConversionDate AS datetime))
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NOT NULL
AND B1.DATE_OF_DEATH IS NOT NULL AND DATEDIFF(DAY,B1.DATE_OF_DEATH,I1.END_DATE) > 0
THEN B1.DATE_OF_DEATH
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NOT NULL
AND DATEDIFF(DAY,I2.EFFECTIVE_DATE,I2.[START_DATE]) >= 0
AND DATEDIFF(DAY,DATEADD(DAY,-1,I2.[START_DATE]),I1.END_DATE)>=0
THEN DATEADD(DAY,-1,I2.[START_DATE])
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NOT NULL
AND DATEDIFF(DAY,I2.EFFECTIVE_DATE,I2.[START_DATE]) >= 0
AND DATEDIFF(DAY,DATEADD(DAY,-1,I2.[START_DATE]),I1.END_DATE)<0
THEN I1.END_DATE
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NOT NULL
AND DATEDIFF(DAY,I2.EFFECTIVE_DATE,I2.[START_DATE]) < 0
AND DATEDIFF(DAY,DATEADD(DAY,-1,I2.EFFECTIVE_DATE),I1.END_DATE)>=0
THEN DATEADD(DAY,-1,I2.EFFECTIVE_DATE)
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NOT NULL
AND DATEDIFF(DAY,I2.EFFECTIVE_DATE,I2.[START_DATE]) < 0
AND DATEDIFF(DAY,DATEADD(DAY,-1,I2.EFFECTIVE_DATE),I1.END_DATE)<0
THEN I1.END_DATE
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NULL AND B1.DATE_OF_DEATH IS NOT NULL
THEN B1.DATE_OF_DEATH
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NULL AND B1.DATE_OF_DEATH IS NULL
AND SL1.Ariel_Status IN ('Pensioner') -- 2014jul24 rlegare. select * from ariel.TCVMembershipStatus
THEN I1.END_DATE
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NULL AND B1.DATE_OF_DEATH IS NULL
AND SL1.Ariel_Status IN ('TerminatedWithoutBenefits') -- 2014jul24 rlegare. select * from ariel.TCVMembershipStatus
AND DATEDIFF(DAY,I1.END_DATE, SL1.[START_DATE]) >= 0
THEN I1.END_DATE
WHEN I1.END_DATE IS NOT NULL AND I2.PARTICIPANT_ID IS NULL AND B1.DATE_OF_DEATH IS NULL
AND SL1.Ariel_Status IN ('TerminatedWithoutBenefits') -- 2014jul24 rlegare. select * from ariel.TCVMembershipStatus
AND DATEDIFF(DAY,I1.END_DATE, SL1.[START_DATE]) < 0
THEN SL1.[START_DATE]
-- MALHEUREUSEMENT PAS DE ELSE. ET BESIDES QUOI METTRE? ON VERRA À L'USAGE.
END AS PaymentEndDate,
0 AS AdvancePayPension,
SL1.Ariel_Status,
2 AS PaymentOption, -- 2=Pension, SEE SELECT * FROM ARIEL.TCVPaymentOptionType
ISNULL(PF1.GUAR_PERIOD,0) AS GuaranteedPeriod,
ISNULL(PF1.REV_PERCENTAGE,0) / 100.0 AS PercentageOfReversion,
-- SELECT * FROM [Ariel].[TCVReversionCause]
CASE
WHEN PF1.SURVIVOR_OPTION_CODE IN ('A')THEN 1 -- MemberDeath=1
ELSE NULL
END AS OnFirstDeathReversion,
-- SELECT * FROM ARIEL.[TCVLevelIncomeOption]
CASE
WHEN ISNULL(I1.LEVEL_INCOME_OPTION_FLAG,'N') = 'N' THEN 1 -- NoLevelling
WHEN I1.LEVEL_INCOME_OPTION_FLAG = 'Y'THEN 3-- WithOas
ELSE 1
END AS LevelIncomeOption,
-- CL 20150130 Pour récupérer le montant avant option pour les rentes nivelées
ROUND(ISNULL(I1.BEFORE_LEVEL_INCOME_AMOUNT,0) * COALESCE(F1.Annual_Factor,0),2) AS AmountBeforeOption,
ROUND(I1.PAYMENT_AMOUNT * COALESCE(F1.Annual_Factor,0),2) AS AmountAfterOption,
0 AS DeathBenefit,
1 AS Indicator,
1 AS PensionFormDefinitionType
INTO dbo.Tab_ImmPen_General
--FROM dbo.[Immediate_Pension_Data_MS] I1
FROM dbo.Tab_Immediate_Pension_Data I1 -- 2014jul24 rlegare.
INNER JOIN dbo.Tab_ImmPen_RetDate R1 -- 2014jul24 rlegare.
ON ((I1.PARTICIPANT_ID = R1.PARTICIPANT_ID)
AND (I1.PLAN_ID = R1.PLAN_ID)
AND (I1.[EVENT] = R1.[EVENT]))
INNER JOIN dbo.[Basis_MS] B1
ON ((I1.PARTICIPANT_ID = B1.PARTICIPANT_ID)
AND (I1.PLAN_ID = B1.PLAN_ID))
--LEFT JOIN dbo.[Immediate_Pension_Data_MS] I2
LEFT JOIN dbo.Tab_Immediate_Pension_Data I2 -- 2014jul24 rlegare.
ON ((I1.PARTICIPANT_ID = I2.PARTICIPANT_ID)
AND (I1.PLAN_ID = I2.PLAN_ID)
AND (I1.EVENT = I2.EVENT)
AND (I1.OPTION_TYPE = I2.OPTION_TYPE)
AND (I1.PENSION_TYPE = I2.PENSION_TYPE)
AND (I1.COMP_NO = I2.COMP_NO)
AND ((I1.MS_UniquePenID + 1) = I2.MS_UniquePenID))
-- CL 20150323 Pour corriger le defect 35
-- Pour réajuter les dates de fins des rentes nivelées
LEFT JOIN dbo.Tab_Immediate_Pension_Data I3
ON I1.PARTICIPANT_ID = I3.PARTICIPANT_ID
AND I1.PLAN_ID = I3.PLAN_ID
AND I1.EVENT = I3.EVENT
AND I1.OPTION_TYPE = I3.OPTION_TYPE
AND I1.PENSION_TYPE = I3.PENSION_TYPE
AND I1.COMP_NO = I3.COMP_NO
AND I1.END_DATE = I3.END_DATE
AND I3.END_DATE IS NOT NULL
AND I3.EFFECTIVE_DATE = (SELECT MIN(EFFECTIVE_DATE) FROM Tab_Immediate_Pension_Data
WHERE PARTICIPANT_ID = I1.PARTICIPANT_ID
AND PLAN_ID = I1.PLAN_ID
AND EVENT = I1.EVENT
AND OPTION_TYPE = I1.OPTION_TYPE
AND PENSION_TYPE = I1.PENSION_TYPE
AND COMP_NO = I1.COMP_NO
AND END_DATE = I1.END_DATE
AND EFFECTIVE_DATE > I1.EFFECTIVE_DATE
)
LEFT JOIN [dbo].[MS_Status_Last] SL1
ON ((I1.PARTICIPANT_ID = SL1.PARTICIPANT_ID)
AND (I1.PLAN_ID = SL1.PLAN_ID))
left join Ariel.TCVMembershipStatus arlMS -- 2014jul24 rlegare.
ON arlMS.Symbol = SL1.Ariel_Status
LEFT JOIN [dbo].[Tab_Memberships_Final] M1 -- on laisse left join, car on dealera séparément avec Marriage Breakdowns later.
ON (B1.ParticipationID = M1.ParticipationID)
LEFT JOIN [dbo].[TC_ImmediatePensionsAriel$] TCI1
ON ((I1.PLAN_ID = TCI1.PLAN_ID)
AND (I1.OPTION_TYPE = TCI1.OPTION_TYPE)
AND (I1.PENSION_TYPE = TCI1.PENSION_TYPE)
AND (I1.COMP_NO = TCI1.COMP_NO))
LEFT JOIN client.[_PENSION_FORM] PF1
ON ((I1.PLAN_ID = PF1.PLAN_ID)
AND (I1.PENSION_FORM = PF1.TEXT))
LEFT JOIN [dbo].[TCS_Tractus_PaymentFrequency] F1 -- 2014JUL25 RLEGARE, corrn left join
ON (I1.PAYMENT_FREQUENCY = F1.PAYMENT_FREQUENCY)
LEFT JOIN [dbo].[TC_PlansAriel$] AS Pl ON I1.PLAN_ID = Pl.PLAN_ID
WHERE I1.OPTION_STATUS <> 'Default'
AND TCI1.Ariel_Table IN ('PayablePensions','MaritalBreakdowns')
PRINT CAST(sysdatetime()as varchar(max))+' Tab_ImmPen_General: Nb Records: ' + CAST(@@rowcount as varchar(max))
/*-----------------------------------------------------------------------------------------------------
ajout de colonnes pour le prochain SQL
*/
ALTER TABLE dbo.Tab_ImmPen_General
ADD
PRIMARY KEY (Immediate_Pension_Data_UID)
,BeneficiaryID INT NULL
,SpouseID INT NULL
CREATE INDEX NDX_PARTICIPANT_IDON dbo.Tab_ImmPen_General (PARTICIPANT_ID)
CREATE INDEX NDX_MembershipIDON dbo.Tab_ImmPen_General (MembershipID)
PRINT CAST(sysdatetime()as varchar(max))+' Tab_ImmPen_General: PK & Index créés.'
/*-----------------------------------------------------------------------------------------------------
10:08 AM 7/25/2014 BENCHMARK:
Tab_ImmPen_General: Nb Records: 86864
Le résultat est combiné rentes immédiates (MembershipID) ou (MaritalBreakdownID qui sera updaté plus tard)
*/
--exec dbo.LogMessage 'BA_ImmPen_General_Mapping.sql','Invalid column name Source_Code'
--exec dbo.LogMessage 'BA_ImmPen_General_Mapping.sql','Invalid column name Renom_Code'
-- 2014jul24 rlegare. ok under control now
--select top 3 * from dbo.Tab_ImmPen_General
-- eof
-------------------------------------------------------------------------------------------------------------
Thanks,
Stan
March 31, 2015 at 12:22 pm
Quick question, can you provide the table structures (DDL), some sample data and the actual execution plan?
😎
March 31, 2015 at 1:59 pm
Here the structure I will provide the sample data soon.
Can you tell me if that will be Ok some script with insert statement, for the sample data that you required?
Thanks,
Stan
March 31, 2015 at 3:36 pm
Could you capture the execution plan? There's just too much to walk through there. If we can see what the optimizer is doing, it'll be easier.
"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
April 1, 2015 at 6:51 am
The execution plan it's already in the zip file
Thanks,
Stan
April 1, 2015 at 7:21 am
Quick suggestion, add a clustered index to those heaps and look into indexing them, the table scans are more than 80% of the cost.
😎
April 1, 2015 at 11:12 am
Yeah, all those table scans are not doing you any favors whatsoever.
The query plan is timing out too, which means it's unstable. Run it on another day when there's a little more memory or CPU cycles available and you may see a different plan.
You're returning an estimated 600 rows, but some of the table scans are for 45000.
Personally, this query is too complex. I would tear it down into it's most basic parts and rebuild it slowly. There's way too much formatting logic in the SELECT criteria. That's the kind of thing best done on the front end. You have calculations running on columns, ((I1.MS_UniquePenID + 1), in your JOIN criteria. Even if you have good indexes, and indexing 9 columns (if I counted correctly on the dbo.Tab_Immediate_Pension_Data table) for some of the JOIN criteria is going to be a bit problematic, that's going to lead to scans all by itself. Personally, this is a place where an artificial key is likely to be preferable to natural keys, just to get the performance needed. Also, the sub-select is using MIN without any other grouping or ordering. A TOP 1 with an ORDER BY desc might work better there, assuming you can get an 8 column index to work.
To tune the query, ditch the SELECT criteria for now. Concentrate on the JOIN and WHERE clauses to ensure you can eliminate all those table scans. You'll have to put indexes in place. Once all that is done, slowly rebuild the SELECT criteria to ensure you're still able to retrieve the data you need.
"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
April 2, 2015 at 6:51 am
Thanks to both of you. I've used tuning advisor and apply the recommendations and I've gain more speed now.
April 2, 2015 at 7:05 am
Stanley Pagenel (4/2/2015)
Thanks to both of you. I've used tuning advisor and apply the recommendations and I've gain more speed now.
That's good. What did it advise you to do?
For what it's worth since you're seeing benefit, I don't trust the Tuning Advisor. I'm sure you have additional tuning that can be done to the query that the Advisor missed.
"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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply