[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

optimize sql statement

Posted on 2011-09-20
7
Medium Priority
?
275 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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