0

i used database engine tunning advisor for creating some index and statistics but still run slow

SELECT
            C.code_commande AS 'Order',
            C.code_client AS 'Cust&#46',
            CL.nom_fac_client AS 'Cust&#46 Name',
            LTRIM(RTRIM(C.code_commande_client)) AS 'Cust&#46 Ref&#46',
            C.total_quantite_commande AS 'Qty',
            C.code_devise AS 'Curr&#46',
            CONVERT(VARCHAR,sum(LC.quantite_commandee*LC.prix_net_unit_ligne_commande)) AS 'Price',
            CONVERT(VARCHAR,C.date_commande_client,23) AS 'Order Date',
            VCS.libelle_statut AS 'Status',
            'Seller' = (P.nom_personne +' '+ P.prenom_personne),
            ISNULL(EC.Vehicule,'') 'Container',
            CI.code_incoterm AS 'Incoterm',
            X.xlatlongname AS 'Shipment',
            VLF.code_facture AS 'inf_invoiceId', 
            CONVERT(VARCHAR,VLF.date_facture,23) AS 'inf_invoiceDate',
            CASE 
               WHEN (VCS.code_statut = 'A' AND VCS.code_type_traitement = 'D') THEN '1'
               ELSE '0'
            END AS 'inf_approvedButDelayed',
            CASE 
               WHEN (VCS.ordre between 4 and 13 and C.total_quantite_commande > 0)
                     OR (VCS.code_statut = 'O' and C.total_quantite_commande < 0 ) THEN '1'
                  ELSE '0'
            END AS 'inf_soRef',
            inf_company = 'G CORP',
            ISNULL(CL.bloque_client, '0') AS 'inf_bloqueCompta'
     FROM COMMANDE C
     LEFT JOIN CLIENT CL ON CL.code_client = C.code_client
     LEFT JOIN FILIALE_VENTE FV ON FV.code_filiale_vente = CL.code_filiale_vente
     LEFT JOIN REGION_VENTE RV ON RV.code_region_vente = FV.code_region_vente
     LEFT JOIN LIGNE_COMMANDE LC ON LC.code_commande = C.code_commande
     LEFT JOIN PERSONNE P ON P.code_vendeur = C.code_vendeur_commande
     LEFT JOIN 
        (SELECT 
           CS.code_commande,
           SC.libelle_statut,
           CS.code_statut,
           CS.code_type_traitement,
           SC.ordre 
        FROM STATUT_COMMANDE SC, COMMANDE_STATUT CS
        WHERE CS.CODE_STATUT=SC.CODE_STATUT ) VCS ON C.code_commande = VCS.code_commande
     LEFT JOIN 
        (SELECT DISTINCT 
           F.CODE_FACTURE, 
           F.DATE_FACTURE,
           LF.CODE_COMMANDE_FACTUREE 
        FROM FACTURE F, LIGNE_FACTURE LF
        WHERE F.CODE_FACTURE = LF.CODE_FACTURE
           AND F.DATE_FACTURE = LF.DATE_FACTURE
           AND F.CODE_CLIENT <>419124
           AND LF.CODE_COMMANDE_FACTUREE is not null ) AS vLF ON vLF.CODE_COMMANDE_FACTUREE=C.CODE_COMMANDE

     LEFT JOIN COMMANDE_INFOS CI ON CI.CODE_COMMANDE=C.CODE_COMMANDE
     LEFT JOIN MOD1_EXPEDITION_COMMANDE MEC ON MEC.code_commande = C.code_commande
     LEFT JOIN ENVOI_CAMION EC ON EC.num_envoi = MEC.numero_expedition AND EC.num_camion = 1
     LEFT JOIN XLATTABLE X ON X.FIELDNAME = 'Type_Livraison' and X.FIELDVALUE = isnull(CI.CODE_TYPE_LIVRAISON_ADV, CI.CODE_TYPE_LIVRAISON)
     LEFT JOIN PERSONNE_VENTE PV ON PV.code_personne = 817  
     LEFT JOIN VPFL_PERSONNE_PAYS VPFL ON VPFL.code_personne = PV.code_personne

     WHERE
        C.CODE_COMMANDE > 1000000
        AND EXISTS(SELECT LC.code_commande FROM LIGNE_COMMANDE LC WHERE LC.code_commande = C.code_commande)
           AND C.total_quantite_commande IS NOT NULL 
        AND C.CODE_VENDEUR_COMMANDE <>99
        AND C.CODE_VENDEUR_COMMANDE=P.CODE_VENDEUR
        AND C.CODE_COMMANDE=LC.CODE_COMMANDE
        AND C.CODE_CLIENT=CL.CODE_CLIENT
        AND PV.code_personne = 817
        /* ------------ Restriction filiale vente et zone géographique -------------*/
        AND ((CL.code_filiale_vente = PV.code_filiale_vente) OR (ISNULL(CL.code_filiale_vente, '0') = 0))
        AND CL.code_pays_fac = VPFL.code_pays

     GROUP BY 
        C.code_commande,
        C.code_client,
        CL.nom_fac_client,
        C.code_commande_client,
        C.total_quantite_commande,
        C.code_devise,
        C.date_commande_client,
        P.nom_personne,
              P.prenom_personne,
        VCS.libelle_statut,
        VLF.code_facture,
        VLF.date_facture,
        VCS.ordre,
              VCS.code_statut,
        VCS.code_type_traitement,
        EC.Vehicule,
        CI.code_incoterm,
        X.xlatlongname,
        CL.bloque_client
     ORDER BY C.code_commande desc,
              C.code_client desc
4
  • 1
    An actual execution plan would help a lot, so please see if you can add one to the question. Also add details: what kind of hardware you have? What's the original, current and desired performance?
    – vonPryz
    Sep 6 at 11:10
  • Why are you using the antiquated join syntax, but only in your subquery? Also, varchar with no length is a bad habit. The exists in your where clause indicates that very likely the LEFT JOIN to 'LIGNE_COMMANDE' should be an INNER JOIN. There are lots of things going on here and quite a few that have a code smell to them that indicates performance issues.
    – Sean Lange
    Sep 6 at 13:52
  • My simple advice will be to use temp table carefully. Your query is slow because lot of extra rows getting process.Also such big query is always hard to debug.
    – KumarHarsh
    Sep 8 at 5:30
  • Your query is not in a stage to add index ,execution plan wont advice you to write optimize and simpler query
    – KumarHarsh
    Sep 8 at 5:37

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.