5 Replies Latest reply: Sep 12, 2013 4:28 PM by Jeff E RSS

    Query running long in APEX

    Pam

      Hi ,

      I am using Apex Version 4.1.1.00.23.  I am running an Interactive report in Apex that is running about15- 20 seconds . I take the query out of the report and run the query  in sqldeveloper and it runs in 4 seconds. Why does it run so much slower in APEX. It is a basic

      Interactive report with one query.  I will send query. Is there a way I can tune through APEX and see why it is taking so MUCH longer ?

       

       

      select c.rcn

        ,case when logical_level- (select logical_level from cd_customer where rcn = :P132_RCN) = 1 then '. '

             when logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN) = 2 then '. . '

             when logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN) = 3 then '. . . '

             when logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN) = 4 then '. . . . '

             when logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN) = 5 then '. . . . . '

         end || (logical_level - (select logical_level from cd_customer where  rcn = :P132_RCN)) ||

            ' ' || get_name(c.rcn,'D','1') DName

      ,PHYSICAL_LEVEL - (select physical_level from cd_customer where  RCN = :P132_RCN) "LEVEL"

      , nvl(sumpgpv(c.rcn, :P132_START_PERIOD, :P132_END_PERIOD,c.rank),0) PGPV

      , countd(c.rcn,1, :P132_START_PERIOD, :P132_END_PERIOD)   DistCnt

      , countd(c.rcn,5, :P132_START_PERIOD, :P132_END_PERIOD) MACnt,  logical_lbound,c.rank,

      (select wr.abbreviation from  wd_ranknames wr where wr.rank = c.rank and wr.status=c.status) "rnk_abbrv"

      ,&P132_START_PERIOD,&P132_END_PERIOD ,&P132_RCN

      from cd_customer c

        where :P132_END_PERIOD > (select commission_closed from cd_parameters)  and logical_lbound > 0

      and logical_lbound between (select logical_lbound from cd_customer where rcn = :P132_RCN)

                              and (select logical_rbound from cd_customer where  rcn = :P132_RCN)

      and (logical_level - (select logical_level from cd_customer where rcn = :P132_RCN)) <=:P132_LEVELS                 

      union all

      select c.rcn

        ,case when logical_level- (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 1 then '. '

             when logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 2 then '. . '

             when logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 3 then '. . . '

             when logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 4 then '. . . . '

             when logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN) = 5 then '. . . . . '

         end || (logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN)) ||

            ' ' || get_name(c.rcn,'D','1') DName

      ,PHYSICAL_LEVEL - (select physical_level from wd_customer where pvperiod = :P132_END_PERIOD AND RCN = :P132_RCN) "LEVEL"

      , sumpgpv(c.rcn, :P132_START_PERIOD, :P132_END_PERIOD,c.rank) PGPV

      ,countd(c.rcn,1, :P132_START_PERIOD, :P132_END_PERIOD) DistCnt

      ,countd(c.rcn,5, :P132_START_PERIOD, :P132_END_PERIOD) MACnt

      ,logical_lbound,c.rank,(select wr.abbreviation from

      wd_ranknames wr where wr.rank = c.rank and wr.status=c.status) "rnk_abbrv"

      ,&P132_START_PERIOD,&P132_END_PERIOD ,&p132_RCN

      from wd_customer c

        where pvperiod = :P132_END_PERIOD and logical_lbound > 0

      and logical_lbound between (select logical_lbound from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN)

                              and (select logical_rbound from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN)

      and (logical_level - (select logical_level from wd_customer where pvperiod = :P132_END_PERIOD and rcn = :P132_RCN)) <=:P132_LEVELS   

        • 1. Re: Query running long in APEX
          Howard (... in Training)

          Hi,

           

          Please run your query in APEX's SQL Workshop SQL Commands window.  Does it run longer or faster than in the report?  You got a few different WHERE clauses / CASE WHEN statements -- try removing some of them and see which ones are taking a lot of time.   It should be easier / faster to do this in the SQL Commands window.   As a first go, run each part of the query -- the parts before / after the UNION ALL -- to see if one is taking significantly more time than the other.

           

          Regards,

          Howard

          • 2. Re: Query running long in APEX
            Pam

            Hi Howard,

            When I run the query in SQL Command window it runs within 4 seconds.  Why does it take the Interactive Report longer?

             

            Thanks,

            Pam

            • 3. Re: Query running long in APEX
              Howard (... in Training)

              The query surely gets "wrapped" in some other SQL / PL/SQL as a part of APEX running the report.  I conjecture this wrapping changes the execution plan causing it to take longer.  What is the size (# of rows) of the data?  Perhaps some index(es) could help.

               

              Howard

              • 4. Re: Query running long in APEX
                InoL

                I had a situation like yours too. In the end, I skipped some functionality I wanted in the query (without loosing the complete functionality of the report, of course).

                 

                The query ran fast in SQLWorkshop and tools like TOAD, but not in my application.

                Apex indeed wraps your query (something like a "select * from (select ...)), but it still didn't explain why it got so much slower.

                I could see the query being executed via v$session. Using TOAD, I copied this query, ran an explain plan, ran the query in SQLWorkshop and in TOAD. Everything was fast, except in the actual application.

                 

                So, there are some tools to use when trying to get to the bottom of the problem

                - Use SQLDeveloper or TOAD to see which query is actually executing in the database (this is your query, transformed by Apex into something a bit different).

                - Run a trace on the session if that still doesn't give you an idea. I never got this far, and just altered the query a bit

                • 5. Re: Query running long in APEX
                  Jeff E

                  Sorry, my OCD must have kicked in.  Try removing all those in-line queries, although not an answer to why the report takes longer than elsewhere, it might help.

                   

                   

                  SELECT c.rcn ,
                    CASE
                      WHEN c.logical_level - cv.logical_level = 1  THEN '. '
                      WHEN c.logical_level - cv.logical_level = 2  THEN '. . '
                      WHEN c.logical_level - cv.logical_level = 3  THEN '. . . '
                      WHEN c.logical_level - cv.logical_level = 4  THEN '. . . . '
                      WHEN c.logical_level - cv.logical_level = 5  THEN '. . . . . '
                    END
                    || (c.logical_level - cv.logical_level)
                    || ' '
                    || get_name(c.rcn,'D','1') DName ,
                    c.PHYSICAL_LEVEL - cv.physical_level "LEVEL" ,
                    NVL(sumpgpv(c.rcn, :P132_START_PERIOD, :P132_END_PERIOD,c.rank),0) PGPV ,
                    countd(c.rcn,1, :P132_START_PERIOD, :P132_END_PERIOD) DistCnt ,
                    countd(c.rcn,5, :P132_START_PERIOD, :P132_END_PERIOD) MACnt,
                    c.logical_lbound,
                    c.rank,
                    (SELECT wr.abbreviation
                      FROM wd_ranknames wr
                      WHERE wr.rank = c.rank
                        AND wr.status =c.status ) "rnk_abbrv" ,
                    &P132_START_PERIOD,
                    &P132_END_PERIOD ,
                    &P132_RCN
                  FROM cd_customer c,
                      cd_customer cv
                  WHERE cv.rcn = :P132_RCN
                    AND :P132_END_PERIOD > (SELECT commission_closed FROM cd_parameters )
                    AND c.logical_lbound > 0
                    AND c.logical_lbound BETWEEN cv.logical_lbound AND cv.logical_rbound
                    AND (clogical_level - cv.logical_level) <=:P132_LEVELS
                  UNION ALL
                  SELECT c.rcn ,
                    CASE
                      WHEN c.logical_level - cv.logical_level = 1 THEN '. '
                      WHEN c.logical_level - cv.logical_level = 2 THEN '. . '
                      WHEN c.logical_level - cv.logical_level = 3 THEN '. . . '
                      WHEN c.logical_level - cv.logical_level = 4 THEN '. . . . '
                      WHEN c.logical_level - cv.logical_level = 5 THEN '. . . . . '
                    END
                    || (c.logical_level - cv.logical_level )
                    || ' '
                    || get_name(c.rcn,'D','1') DName ,
                    PHYSICAL_LEVEL - cv.physical_level "LEVEL" ,
                    sumpgpv(c.rcn, :P132_START_PERIOD, :P132_END_PERIOD,c.rank) PGPV ,
                    countd(c.rcn,1, :P132_START_PERIOD, :P132_END_PERIOD) DistCnt ,
                    countd(c.rcn,5, :P132_START_PERIOD, :P132_END_PERIOD) MACnt ,
                    c.logical_lbound,
                    c.rank,
                    (SELECT wr.abbreviation
                    FROM wd_ranknames wr
                    WHERE wr.rank = c.rank
                    AND wr.status =c.status
                    ) "rnk_abbrv" ,
                    &P132_START_PERIOD,
                    &P132_END_PERIOD ,
                    &p132_RCN
                  FROM wd_customer c,
                    wd_customer cv
                  WHERE cv.pvperiod    = :P132_END_PERIOD
                  AND cv.rcn          = :P132_RCN
                  AND c.pvperiod      = :P132_END_PERIOD
                  AND c.logical_lbound > 0
                  AND c.logical_lbound BETWEEN cv.logical_lbound AND cv.logical_rbound
                  AND (logical_level - cv.logical_level ) <= :P132_LEVELS