Optimiser requete SQL

Le
Bonjour j'ai une requete SQL qui prend +- 4 min à s'exécuter Comment puis
je l'optimiser ?

Merci


SELECT LittleTamina_DN_01_LastNav.ID_Unit,
LittleTamina_DN_01_LastNav.ID_Fund, LittleTamina_DN_01_LastNav.NPTF,
AMS_NAV_ARC_JNAV_DET.TPARTS, LittleTamina_DN_01_LastNav.CPDC,
LittleTamina_DN_01_LastNav.MaxOfNAV_DATE,
LittleTamina_DN_01_LastNav.Unit_Ccy, LittleTamina_DN_01_LastNav.Fund_Ccy,
Round(CDbl([QT_PART]),[Unit_Rounding_Outstanding]) AS Unit_Outstanding,
Round(CDbl([MNT_PART]),[Fund_Rounding_TNA]) AS Unit_TNA_Fund_Ccy,
Round(CDbl([MNT_PART]),[Unit_Rounding_TNA]) AS Unit_TNA_Unit_Ccy,
Round(CDbl([AMS_NAV_ARC_JNAV_DET]![PRICE_PART]),[Unit_Rounding_NAV]) AS
Unit_NAV, Round(CDbl([MNT_NET]),[Fund_Rounding_TNA]) AS Fund_TNA,
Round(CDbl(IIf([CGENGEST]<>"MMKT",IIf([ZWIST_PART_ARR] Is
Null,0,[ZWIST_PART_ARR]),IIf([ZWIST_PART_3_ARR] Is
Null,0,[ZWIST_PART_3_ARR]))),[Unit_Rounding_ZG]) AS ZG,
LittleTamina_DN_01_LastNav.subsc_fee, LittleTamina_DN_01_LastNav.red_fee,
LittleTamina_DN_01_LastNav.Add_subsc_fee,
LittleTamina_DN_01_LastNav.Unit_Rounding_NAV, AMS_NAV_ARC_IDX.DATE_TRT_FIN
AS Projection_Date, IIf([AG_ActivationDate] Is Null Or
DateValue([AG_ActivationDate])>ÚteValue([Forms]![Parameter]![Date]),Null,IIf([TOTAL_AKTIEN_PART]
Is
Null,0,Round([AMS_EGALISATION]![TOTAL_AKTIEN_PART]/[AMS_EGALISATION]![VNI_PART]*100+[AG_Amount],4)))
AS Unit_AG, LittleTamina_DN_01_LastNav.SubscriptionHaltedOn,
IIf([CODE_MULTI_CLASS]<>"1",Null,Round([CALCULATED_DDR],[Unit_Rounding_DDiv]))
AS Unit_Daily_Div, AMS_NAV_ARC_IDX.NECRITUR_PTF, CDbl(1) AS XRate_Multi,
IIf([Publish_TIS],IIf([TIS] Is
Null,Null,IIf(CDbl([TIS])<0,0,Round(CDbl([TIS]),[Unit_Rounding_TIS]))),Null)
AS Unit_TIS, 0 AS Unit_DE_ImmoGewinn,
LittleTamina_DN_01_LastNav.Unit_Rounding_Sub,
LittleTamina_DN_01_LastNav.Unit_Rounding_Sub_Type,
LittleTamina_DN_01_LastNav.Unit_Rounding_Red,
LittleTamina_DN_01_LastNav.Unit_Rounding_Red_Type,
LittleTamina_DN_01_LastNav.Fund_NAV_Calc_Days, IIf([QT_SUB] Is
Null,0,Round(CDbl([QT_SUB]),[Unit_Rounding_Outstanding])) AS MF_SUB,
IIf([QT_RED] Is Null,0,Round(CDbl([QT_RED]),[Unit_Rounding_Outstanding])) AS
MF_RED INTO LittleTamina_DN_02_Data IN 'c:accessLittleTamina.mdb'
FROM (((LittleTamina_DN_01_LastNav INNER JOIN AMS_NAV_ARC_JNAV_DET ON
(LittleTamina_DN_01_LastNav.NAV_TYPE = AMS_NAV_ARC_JNAV_DET.TYP_TRT) AND
(LittleTamina_DN_01_LastNav.Part_MF = AMS_NAV_ARC_JNAV_DET.TPARTS) AND
(LittleTamina_DN_01_LastNav.MaxOfNAV_DATE = AMS_NAV_ARC_JNAV_DET.DATE_NAV)
AND (LittleTamina_DN_01_LastNav.NPTF = AMS_NAV_ARC_JNAV_DET.NPTF)) INNER
JOIN AMS_NAV_ARC_IDX ON (LittleTamina_DN_01_LastNav.MaxOfNAV_DATE =
AMS_NAV_ARC_IDX.DATE_NAV) AND (LittleTamina_DN_01_LastNav.NAV_TYPE =
AMS_NAV_ARC_IDX.TYP_TRT) AND (LittleTamina_DN_01_LastNav.NPTF =
AMS_NAV_ARC_IDX.NPTF)) INNER JOIN AMS_EGALISATION ON
(LittleTamina_DN_01_LastNav.Part_MF = AMS_EGALISATION.TPARTS) AND
(LittleTamina_DN_01_LastNav.MaxOfNAV_DATE = AMS_EGALISATION.DATE_NAV) AND
(LittleTamina_DN_01_LastNav.NPTF = AMS_EGALISATION.NPTF)) INNER JOIN
AMS_NAV_ARC_MULTI_CLASS ON (AMS_NAV_ARC_JNAV_DET.TPARTS =
AMS_NAV_ARC_MULTI_CLASS.TPARTS) AND (AMS_NAV_ARC_JNAV_DET.DATE_NAV =
AMS_NAV_ARC_MULTI_CLASS.DATE_NAV) AND (AMS_NAV_ARC_JNAV_DET.TYP_TRT =
AMS_NAV_ARC_MULTI_CLASS.TYP_TRT) AND (AMS_NAV_ARC_JNAV_DET.NPTF =
AMS_NAV_ARC_MULTI_CLASS.NPTF)
WHERE (((LittleTamina_DN_01_LastNav.SameCcy)=-1) AND
((AMS_EGALISATION.CEGA)="576"));
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Michel_D
Le #6365341
Bonjour,

1) Déja interdire le plus possible la valleur nulle sur tes champs numériques.

2) Ensuite remplacer par exemple les formules du type suivant par
IIf([ZWIST_PART_ARR] Is Null,0,[ZWIST_PART_ARR])
par
Nz([ZWIST_PART_ARR],0)

3) Vérifier que la convertion en CDbl est nécessaire (essayer sans)

4) Explique pourquoi tu utilise tant la fonction Round vu que c'est pour créer
une nouvelle table.

5) A quoi te sert la jointure sur la table "AMS_NAV_ARC_IDX" ?

Voici en vrac les premières constatations.


Bonjour j'ai une requete SQL qui prend +- 4 min à s'exécuter... Comment puis
je l'optimiser ?

Merci


SELECT LittleTamina_DN_01_LastNav.ID_Unit,
LittleTamina_DN_01_LastNav.ID_Fund, LittleTamina_DN_01_LastNav.NPTF,
AMS_NAV_ARC_JNAV_DET.TPARTS, LittleTamina_DN_01_LastNav.CPDC,
LittleTamina_DN_01_LastNav.MaxOfNAV_DATE,
LittleTamina_DN_01_LastNav.Unit_Ccy, LittleTamina_DN_01_LastNav.Fund_Ccy,
Round(CDbl([QT_PART]),[Unit_Rounding_Outstanding]) AS Unit_Outstanding,
Round(CDbl([MNT_PART]),[Fund_Rounding_TNA]) AS Unit_TNA_Fund_Ccy,
Round(CDbl([MNT_PART]),[Unit_Rounding_TNA]) AS Unit_TNA_Unit_Ccy,
Round(CDbl([AMS_NAV_ARC_JNAV_DET]![PRICE_PART]),[Unit_Rounding_NAV]) AS
Unit_NAV, Round(CDbl([MNT_NET]),[Fund_Rounding_TNA]) AS Fund_TNA,
Round(CDbl(IIf([CGENGEST]<>"MMKT",
IIf([ZWIST_PART_ARR] Is Null,0,[ZWIST_PART_ARR]),IIf([ZWIST_PART_3_ARR] Is

Null,0,[ZWIST_PART_3_ARR]))),[Unit_Rounding_ZG]) AS ZG,
LittleTamina_DN_01_LastNav.subsc_fee, LittleTamina_DN_01_LastNav.red_fee,
LittleTamina_DN_01_LastNav.Add_subsc_fee,
LittleTamina_DN_01_LastNav.Unit_Rounding_NAV, AMS_NAV_ARC_IDX.DATE_TRT_FIN
AS Projection_Date, IIf([AG_ActivationDate] Is Null Or
DateValue([AG_ActivationDate])>ÚteValue([Forms]![Parameter]![Date]),Null,IIf([TOTAL_AKTIEN_PART]
Is
Null,0,Round([AMS_EGALISATION]![TOTAL_AKTIEN_PART]/[AMS_EGALISATION]![VNI_PART]*100+[AG_Amount],4)))
AS Unit_AG, LittleTamina_DN_01_LastNav.SubscriptionHaltedOn,
IIf([CODE_MULTI_CLASS]<>"1",Null,Round([CALCULATED_DDR],[Unit_Rounding_DDiv]))
AS Unit_Daily_Div, AMS_NAV_ARC_IDX.NECRITUR_PTF, CDbl(1) AS XRate_Multi,
IIf([Publish_TIS],IIf([TIS] Is
Null,Null,IIf(CDbl([TIS])<0,0,Round(CDbl([TIS]),[Unit_Rounding_TIS]))),Null)
AS Unit_TIS, 0 AS Unit_DE_ImmoGewinn,
LittleTamina_DN_01_LastNav.Unit_Rounding_Sub,
LittleTamina_DN_01_LastNav.Unit_Rounding_Sub_Type,
LittleTamina_DN_01_LastNav.Unit_Rounding_Red,
LittleTamina_DN_01_LastNav.Unit_Rounding_Red_Type,
LittleTamina_DN_01_LastNav.Fund_NAV_Calc_Days, IIf([QT_SUB] Is
Null,0,Round(CDbl([QT_SUB]),[Unit_Rounding_Outstanding])) AS MF_SUB,
IIf([QT_RED] Is Null,0,Round(CDbl([QT_RED]),[Unit_Rounding_Outstanding])) AS
MF_RED INTO LittleTamina_DN_02_Data IN 'c:accessLittleTamina.mdb'
FROM (((LittleTamina_DN_01_LastNav INNER JOIN AMS_NAV_ARC_JNAV_DET ON
(LittleTamina_DN_01_LastNav.NAV_TYPE = AMS_NAV_ARC_JNAV_DET.TYP_TRT) AND
(LittleTamina_DN_01_LastNav.Part_MF = AMS_NAV_ARC_JNAV_DET.TPARTS) AND
(LittleTamina_DN_01_LastNav.MaxOfNAV_DATE = AMS_NAV_ARC_JNAV_DET.DATE_NAV)
AND (LittleTamina_DN_01_LastNav.NPTF = AMS_NAV_ARC_JNAV_DET.NPTF)) INNER
JOIN AMS_NAV_ARC_IDX ON (LittleTamina_DN_01_LastNav.MaxOfNAV_DATE > AMS_NAV_ARC_IDX.DATE_NAV) AND (LittleTamina_DN_01_LastNav.NAV_TYPE > AMS_NAV_ARC_IDX.TYP_TRT) AND (LittleTamina_DN_01_LastNav.NPTF > AMS_NAV_ARC_IDX.NPTF)) INNER JOIN AMS_EGALISATION ON
(LittleTamina_DN_01_LastNav.Part_MF = AMS_EGALISATION.TPARTS) AND
(LittleTamina_DN_01_LastNav.MaxOfNAV_DATE = AMS_EGALISATION.DATE_NAV) AND
(LittleTamina_DN_01_LastNav.NPTF = AMS_EGALISATION.NPTF)) INNER JOIN
AMS_NAV_ARC_MULTI_CLASS ON (AMS_NAV_ARC_JNAV_DET.TPARTS > AMS_NAV_ARC_MULTI_CLASS.TPARTS) AND (AMS_NAV_ARC_JNAV_DET.DATE_NAV > AMS_NAV_ARC_MULTI_CLASS.DATE_NAV) AND (AMS_NAV_ARC_JNAV_DET.TYP_TRT > AMS_NAV_ARC_MULTI_CLASS.TYP_TRT) AND (AMS_NAV_ARC_JNAV_DET.NPTF > AMS_NAV_ARC_MULTI_CLASS.NPTF)
WHERE (((LittleTamina_DN_01_LastNav.SameCcy)=-1) AND
((AMS_EGALISATION.CEGA)="576"));




Publicité
Poster une réponse
Anonyme