Solved

optimize sql statement

Posted on 2011-09-20
7
249 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Split one comma delimited string in a column into 7 (SQL server 2008) 13 85
SQL 2008 R2 syntax 11 29
MS SQL Delete Query 9 33
ms sql + get number in list out of total 7 29
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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