Solved

optimize sql statement

Posted on 2011-09-20
7
252 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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