Solved

optimize sql statement

Posted on 2011-09-20
7
263 Views
Last Modified: 2012-05-12
Hello,

I try to optimize this query :
SELECT arr.cd_type_arrete, 
       102, 
       evl.cd_source, 
       5, 
       evl.id_porteur, 
       per.cd_isin, 
       'Inconnu'                                cd_type_ope, 
       'Inconnu'                                cd_col_com_gest, 
       prt.cd_aff_cpte, 
       per.dt_debut, 
       per.dt_fin, 
       SUM(evl.nb_quantite_var)                 nb_quantite, 
       SUM(evl.mt_effet_collecte)               mt_effet_collecte, 
       NULL                                     id_port_pere_rg5, 
       CASE 
         WHEN evl.cd_source = 18 THEN prt2.id_porteur 
         ELSE Isnull(prt5.id_porteur, Isnull(prt3.id_porteur, prt4.id_porteur)) 
       END                                      id_port_pere_rg4, 
       CASE 
         WHEN evl.cd_source = 18 THEN prt2.id_porteur 
         ELSE Isnull(prt5.id_porteur, Isnull(prt3.id_porteur, prt4.id_porteur)) 
       END                                      id_port_pere_rg3, 
       Isnull(prt3.id_porteur, prt4.id_porteur) id_port_pere_rg2, 
       Isnull(prt3.id_porteur, prt4.id_porteur) id_port_pere_rg1, 
       'Positions Teneurs Comptes'   nm_rang 
FROM   encours evl 
       INNER JOIN perimetre_isin per 
         ON per.cd_isin = evl.cd_isin 
            AND evl.dt_position BETWEEN per.dt_debut AND per.dt_fin 
            AND evl.cd_niv_systeme = 3 
       INNER JOIN arg_api_ptf ptf 
         ON ptf.id_ptf_cdcam = per.id_ptf_cdcam 
            AND ptf.id_tiers_centralisateur = 30655 
       INNER JOIN arg_porteur prt 
         ON evl.id_porteur = prt.id_porteur 
       INNER JOIN arg_param_arrete arr 
         ON arr.id_arrete = 102 
       LEFT JOIN arg_porteur prt2 
         ON prt2.cd_plage_cpte = '00000044' 
            AND prt2.cd_cpte_tit = '00000044' 
       LEFT JOIN arg_porteur prt3 
         ON prt3.cd_plage_cpte = prt.cd_aff_cpte 
            AND prt3.cd_cpte_tit = prt.cd_aff_cpte 
       LEFT JOIN arg_porteur prt4 
         ON prt4.cd_plage_cpte IS NULL 
            AND prt4.cd_cpte_tit = evl.cd_isin 
       LEFT JOIN arg_porteur prt5 
         ON prt5.cd_bic_distrib = prt.cd_bic_distrib 
            AND prt5.cd_aff_cpte = prt.cd_aff_cpte 
       LEFT JOIN arg_collecte col 
         ON col.cd_isin = evl.cd_isin 
            AND ( col.cd_source = 30655 
                   OR col.cd_source = 15 
                   OR ( col.cd_source = 20 
                        AND col.cd_source_origine = 30655 ) ) 
            AND col.dt_impact_valo BETWEEN per.dt_debut AND per.dt_fin 
            AND col.id_porteur = prt5.id_porteur 
            AND col.id_porteur IS NOT NULL 
            AND evl.cd_source = 23 
GROUP  BY arr.cd_type_arrete, 
          evl.cd_source, 
          evl.id_porteur, 
          per.cd_isin, 
          prt.cd_aff_cpte, 
          per.dt_debut, 
          per.dt_fin, 
          prt2.id_porteur, 
          prt5.id_porteur, 
          prt3.id_porteur, 
          prt4.id_porteur, 
          prt2.id_porteur 

Open in new window


Thanks
bibi
plan.txt
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36566462
DOES this make any difference?

can you tell us the relationships in terms of 1:1 or 1:m between the tables...
SELECT cd_type_arrete, 
       102, 
       cd_source, 
       5, 
       id_porteur, 
       cd_isin, 
       'Inconnu'                                cd_type_ope, 
       'Inconnu'                                cd_col_com_gest, 
       cd_aff_cpte, 
       dt_debut, 
       dt_fin, 
       nb_quantite, 
       mt_effet_collecte, 
       NULL                                     id_port_pere_rg5, 
        id_port_pere_rg4, 
       ID_PORT_PERE_RG4 AS         id_port_pere_rg3, 
        id_port_pere_rg2, 
       id_port_pere_rg2 AS id_port_pere_rg1, 
       'Positions Teneurs Comptes'   nm_rang 
FROM (
SELECT arr.cd_type_arrete, 
       evl.cd_source, 
       evl.id_porteur, 
       per.cd_isin, 
       prt.cd_aff_cpte, 
       per.dt_debut, 
       per.dt_fin, 
       SUM(nb_quantite)                 nb_quantite, 
       SUM(mt_effet_collecte)               mt_effet_collecte,  
 id_port_pere_rg4, 
 id_port_pere_rg2

   FROM (
SELECT arr.cd_type_arrete, 
       evl.cd_source, 
       evl.id_porteur, 
       evl.cd_isin, --< changed to evl from per
       prt.cd_aff_cpte, 
       per.dt_debut, 
       per.dt_fin, 
       evl.nb_quantite_var * COALESCE(COL.NUM,1)  * ARR.NUM            nb_quantite, 
       evl.mt_effet_collecte * COALESCE(COL.NUM,1) * ARR.NUM             mt_effet_collecte, 
       CASE 
         WHEN evl.cd_source = 18 THEN prt2.id_porteur 
         ELSE coalesce(prt5.id_porteur, prt3.id_porteur, prt4.id_porteur) 
       END                                      id_port_pere_rg4, 
       coalesce(prt3.id_porteur, prt4.id_porteur) id_port_pere_rg2 

FROM   encours evl 
       INNER JOIN perimetre_isin per 
         ON per.cd_isin = evl.cd_isin 
            AND evl.dt_position BETWEEN per.dt_debut AND per.dt_fin 
            AND evl.cd_niv_systeme = 3 
       INNER JOIN arg_api_ptf ptf 
         ON ptf.id_ptf_cdcam = per.id_ptf_cdcam 
            AND ptf.id_tiers_centralisateur = 30655 
       INNER JOIN arg_porteur prt 
         ON evl.id_porteur = prt.id_porteur 
       cross JOIN (SELECT CD_TYPE_ARRETE,COUNT(*) AS NUM
                     FROM arg_param_arrete 
                     wHERE id_arrete = 102
                     GROUP BY CD_TYPE_ARRETE
                  ) AS arr           
       LEFT JOIN arg_porteur prt2 
         ON prt2.cd_plage_cpte = '00000044' 
            AND prt2.cd_cpte_tit = '00000044' 
       LEFT JOIN arg_porteur prt3 
         ON prt3.cd_plage_cpte = prt.cd_aff_cpte 
            AND prt3.cd_cpte_tit = prt.cd_aff_cpte 
       LEFT JOIN arg_porteur prt4 
         ON prt4.cd_plage_cpte IS NULL 
            AND prt4.cd_cpte_tit = evl.cd_isin 
       LEFT JOIN arg_porteur prt5 
         ON prt5.cd_bic_distrib = prt.cd_bic_distrib 
            AND prt5.cd_aff_cpte = prt.cd_aff_cpte 
       LEFT JOIN (SELECT CD_ISIN,ID_PORTEUR,DT_IMPACT_VALO,COUNT(*) AS nUM 
                    FROM arg_collecte 
                   WHERE ( col.cd_source in ( 30655 , 15 )
                           OR ( col.cd_source = 20 
                                 AND col.cd_source_origine = 30655 ) 
                         ) 
                    AND id_porteur IS NOT NULL 
                 GROUP BY CD_ISIN,ID_PORTEUR,DT_IMPACT_VALO
                 ) AS  col
         ON col.cd_isin = evl.cd_isin 
            AND col.dt_impact_valo BETWEEN per.dt_debut AND per.dt_fin 
            AND col.id_porteur = prt5.id_porteur 
            AND evl.cd_source = 23 
) AS X

GROUP  BY cd_type_arrete, 
          cd_source, 
          id_porteur, 
          cd_isin, 
          cd_aff_cpte, 
          dt_debut, 
          dt_fin, 
          id_port_pere_rg4,
          id_port_pere_rg2

) AS y

ORDER BY cd_type_arrete, 
          cd_source, 
          id_porteur, 
          cd_isin, 
          cd_aff_cpte, 
          dt_debut, 
          dt_fin, 
          id_port_pere_rg4,
          id_port_pere_rg2

Open in new window

0
 

Author Comment

by:bibi92
ID: 36566630
Thanks but :

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "col.cd_source" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "col.cd_source" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "col.cd_source" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "col.cd_source_origine" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "arr.cd_type_arrete" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "evl.cd_source" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "evl.id_porteur" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "per.cd_isin" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "prt.cd_aff_cpte" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "per.dt_debut" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "per.dt_fin" could not be bound.

Bibi
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36566730
ok try this...
SELECT cd_type_arrete, 
       102, 
       cd_source, 
       5, 
       id_porteur, 
       cd_isin, 
       'Inconnu'                                cd_type_ope, 
       'Inconnu'                                cd_col_com_gest, 
       cd_aff_cpte, 
       dt_debut, 
       dt_fin, 
       nb_quantite, 
       mt_effet_collecte, 
       NULL                                     id_port_pere_rg5, 
        id_port_pere_rg4, 
       ID_PORT_PERE_RG4 AS         id_port_pere_rg3, 
        id_port_pere_rg2, 
       id_port_pere_rg2 AS id_port_pere_rg1, 
       'Positions Teneurs Comptes'   nm_rang 
FROM (
SELECT cd_type_arrete, 
       cd_source, 
       id_porteur, 
       cd_isin, 
       cd_aff_cpte, 
       dt_debut, 
       dt_fin, 
       SUM(nb_quantite)                 nb_quantite, 
       SUM(mt_effet_collecte)               mt_effet_collecte,  
 id_port_pere_rg4, 
 id_port_pere_rg2

   FROM (
SELECT arr.cd_type_arrete, 
       evl.cd_source, 
       evl.id_porteur, 
       evl.cd_isin, --< changed to evl from per
       prt.cd_aff_cpte, 
       per.dt_debut, 
       per.dt_fin, 
       evl.nb_quantite_var * COALESCE(COL.NUM,1)  * ARR.NUM            nb_quantite, 
       evl.mt_effet_collecte * COALESCE(COL.NUM,1) * ARR.NUM             mt_effet_collecte, 
       CASE 
         WHEN evl.cd_source = 18 THEN prt2.id_porteur 
         ELSE coalesce(prt5.id_porteur, prt3.id_porteur, prt4.id_porteur) 
       END                                      id_port_pere_rg4, 
       coalesce(prt3.id_porteur, prt4.id_porteur) id_port_pere_rg2 

FROM   encours evl 
       INNER JOIN perimetre_isin per 
         ON per.cd_isin = evl.cd_isin 
            AND evl.dt_position BETWEEN per.dt_debut AND per.dt_fin 
            AND evl.cd_niv_systeme = 3 
       INNER JOIN arg_api_ptf ptf 
         ON ptf.id_ptf_cdcam = per.id_ptf_cdcam 
            AND ptf.id_tiers_centralisateur = 30655 
       INNER JOIN arg_porteur prt 
         ON evl.id_porteur = prt.id_porteur 
       cross JOIN (SELECT CD_TYPE_ARRETE,COUNT(*) AS NUM
                     FROM arg_param_arrete 
                     wHERE id_arrete = 102
                     GROUP BY CD_TYPE_ARRETE
                  ) AS arr           
       LEFT JOIN arg_porteur prt2 
         ON prt2.cd_plage_cpte = '00000044' 
            AND prt2.cd_cpte_tit = '00000044' 
       LEFT JOIN arg_porteur prt3 
         ON prt3.cd_plage_cpte = prt.cd_aff_cpte 
            AND prt3.cd_cpte_tit = prt.cd_aff_cpte 
       LEFT JOIN arg_porteur prt4 
         ON prt4.cd_plage_cpte IS NULL 
            AND prt4.cd_cpte_tit = evl.cd_isin 
       LEFT JOIN arg_porteur prt5 
         ON prt5.cd_bic_distrib = prt.cd_bic_distrib 
            AND prt5.cd_aff_cpte = prt.cd_aff_cpte 
       LEFT JOIN (SELECT CD_ISIN,ID_PORTEUR,DT_IMPACT_VALO,COUNT(*) AS nUM 
                    FROM arg_collecte 
                   WHERE ( cd_source in ( 30655 , 15 )
                           OR ( cd_source = 20 
                                 AND cd_source_origine = 30655 ) 
                         ) 
                    AND id_porteur IS NOT NULL 
                 GROUP BY CD_ISIN,ID_PORTEUR,DT_IMPACT_VALO
                 ) AS  col
         ON col.cd_isin = evl.cd_isin 
            AND col.dt_impact_valo BETWEEN per.dt_debut AND per.dt_fin 
            AND col.id_porteur = prt5.id_porteur 
            AND evl.cd_source = 23 
) AS X

GROUP  BY cd_type_arrete, 
          cd_source, 
          id_porteur, 
          cd_isin, 
          cd_aff_cpte, 
          dt_debut, 
          dt_fin, 
          id_port_pere_rg4,
          id_port_pere_rg2

) AS y

ORDER BY cd_type_arrete, 
          cd_source, 
          id_porteur, 
          cd_isin, 
          cd_aff_cpte, 
          dt_debut, 
          dt_fin, 
          id_port_pere_rg4,
          id_port_pere_rg2

Open in new window

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:bibi92
ID: 36566816
Herewith the plan.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36566920
can you tell us the relationships in terms of 1:1 or 1:m between the tables...

what plan?

how many rows on the tables?
what time is the query taking?
0
 

Author Comment

by:bibi92
ID: 36567479
relationships in terms of 1:1 or 1:m between the tables : i don't know, it's for help a developer.
Herewith the plan.
The tables have millions rows.
Thanks
bibi
Execution-plan.txt
0
 

Author Comment

by:bibi92
ID: 36567578
the query time taking several hours and doesn't return result.
If I had I   EVL.cd_source = 18 in
INNER JOIN PERIMETRE_ISIN PER ON
The query takes 20s It does not correspond to rules businesses.
Thanks
bibi          
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question