0 Replies Latest reply on Jun 14, 2010 4:56 AM by jfb00

    cfquery performance

    jfb00 Level 3

      Hi ALL

      I have to summary data from few tables base on different conditions. Here is a sample structure of my cf query:

       

      select id, C01, C02, C03, C04, C05, C06
            ,SUM(C07) as "C07"
            ,SUM(C08) as "C08"
            ,SUM(C09) as "C09"
            ,SUM(C10) as "C10"
            ,SUM(SU) as "SU"
            ,SUM(C12) as "C12"
            ,SUM(C13) as "C13"
          from (
           seleC12 mmr.id, mm.C01, mm.C02, mm.C03, mm.C04, mm.mission_module_type as C05, hC12.C06
           ,CASE WHEN mmr.fy = #y# and mmr.si_id = 2 and mmt.total_mm_units > 0 then ((mmr.mm_rate / mmt.total_mm_units) * mmu.units) else 0  END as "C07"
           ,CASE WHEN mmr.fy = #y# and mmr.si_id = 1 and mmt.total_mm_units > 0 then ((mmr.mm_rate / mmt.total_mm_units) * mmu.units) else 0  END as "C08"
           ,CASE WHEN mmr.fy = #y# and mmr.si_id = 3 and mmt.total_mm_units > 0 then ((mmr.mm_rate / mmt.total_mm_units) * mmu.units) else 0  END as "C09"
           ,CASE WHEN mmr.fy = #y# and mmr.si_id = 9 and mmt.total_mm_units > 0 then ((mmr.mm_rate / mmt.total_mm_units) * mmu.units) else 0  END as "C10"
           ,CASE WHEN mmr.fy = #y# and mmr.si_id = 4 and mmt.total_mm_units > 0 then ((mmr.mm_rate / mmt.total_mm_units) * mmu.units) else 0  END as "C11"
           ,CASE WHEN mmr.fy = #y# and mmr.si_id = 5 and mmt.total_mm_units > 0 then ((mmr.mm_rate / mmt.total_mm_units) * mmu.units) else 0  END as "C12"
           ,0 as "C13"
          from table01 mm, table02 mmr, table03 mmu,
          view2 mmt, table7 hC12
          where mm.id = mmr.id
          and mm.table01_id = mmu.table01_id
          and mmr.id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#">
          and mmr.fy = mmu.fy
          and mmt.id = mmr.id
          and mmt.fy = mmu.fy
          and mm.hull_class_tyC03 = hC12.hull_class_tyC03
          UNION ALL   

           Another select statement

           case statements

           from table4

           join table5, join table6

          UNION ALL   

           Another select statement

           case  statements

           from table7

           join table8, join table9

        UNION ALL   

           Another select statement

           case  statements

           from table10

           join table11, join table12

          )

         Group by id, C01, C02, C03, C04, C05, C06
         ORDER BY id, C02, C03, C01, C05, C06, C04

       

      I was reading this article:

      http://kb2.adobe.com/cps/170/tn_17081.html

      How can I improve the performance?

      Thanks

       

      Johnny