4 Replies Latest reply on Dec 12, 2018 3:03 PM by 3850169

    SQL Question -Tunning- ODI Procedures

    mmoayed

      Hello All,

      I am trying to create Procedure in ODI and one task is executing this SQL:

       

      select cust.department,count(cust_id)

      from customer

      where C_date between Start_date and End_date

            and status in (Status1,status2)

            and version = SELECT MIN(cus2.version )

                                         FROM customer cus2

                                         WHERE cust.Cust_id = cust2.Cust_id AND cust2.status  = cust.status )

      group by cust.department

      union

      select cust.department, count(cust_id)

      from customer cust

      where created_date < End_date

            and exists ( select 'x'

                         from customer cust1

                         where cust1.cust_id = cust1.cust_id

                                AND cust.version  < cust1.version

                                and cust1.C_date between Start_date and End_date

                                and cust1.version = ( SELECT MIN(cust2.VERSION)                                                                            

                                                      FROM customer cust2                                                                      

                                                         WHERE cust2.cust_id = cust1.cust_id AND cust2.department != cust.department  )

                         )

      group by cust.department.

       

      Can any one help me to speed this SQL, it take very long time to be executed.

      the table contains more than 100 million records .

       

      What ideas you can suggest  ?

       

      Best regards,

      Mohammed