?
Solved

optimize sql statement

Posted on 2011-09-20
7
Medium Priority
?
267 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 2000 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
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…

770 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