This discussion is archived
5 Replies Latest reply: Dec 13, 2013 1:00 PM by Mike Kutz RSS

Ajax call performance problem in list item

Sruthi Tamiri Newbie
Currently Being Moderated


Hi All,

 

In our application we have created two items i.,e one is Date Picker and List item. Based on selection of date picker the list items will populate based on Ajax call implemnation.

When we have limited values in list items it populates in fraction of seconds, but same time when data grows i.,e in list we have nearly 10,000+ records it is taking nearly 15 min to pouplate list items, how we can ove this performance issue to resolve the problem.

 

Much appericate if we can provide a solution to resolve the same.

 

Regards,

Sruthitamiri!!

  • 1. Re: Ajax call performance problem in list item
    Joe Upshaw Journeyer
    Currently Being Moderated

    Sruthitamiri!!,


    Can you give us a little more information? Are you calling an On Demand PL/SQL process via AJAX. Are you using Partial Page Refresh from a Cascade? Are you calling the AJAX from JavaScript?


    How, specifically, are you returning the list?


    -Joe

  • 2. Re: Ajax call performance problem in list item
    Sruthi Tamiri Newbie
    Currently Being Moderated

    Hi Joe,

     

      We are uisng On On Demand PL/SQL process via AJAX i., we wrote application process and calling that in our java script function.

     

    Regards,

    Sruthitamiri!!

  • 3. Re: Ajax call performance problem in list item
    Joe Upshaw Journeyer
    Currently Being Moderated

    Thanks.

     

    So, when the PL/SQL process finishes, I assume you are using HTP.PRN to return the value to your JavaScript, correct? What is the datatype value being returned? A clob?


    Is your PL/SQL using a cursor of some kind to build the list or are you calling a backend function that returns a clob (or varchar or char)?

     

    Also, have you been able to determine *where* the delay is occurring? Is it your PL/SQL block that is actually taking 15 minutes to run before it returns? If so, it isn't really related to the fact that AJAX is being employed. It's just a plain, old PL/SQL tuning effort.

     

    -Joe

  • 4. Re: Ajax call performance problem in list item
    Sruthi Tamiri Newbie
    Currently Being Moderated

    Hi Joe,

     

    We are not uisng any clob we are using varchar, yes i have kept alert message to display the count when we select the trade date, below is code whiah i am uisng currently which is taking time to execute in ajax call it selft, when we ran same query in our back end it executes very fastely.

     

    Yes we are uisng htp.prn as return value to display things.

     

    1. Java script function

     

    function setMarketMarker(pThis,pSelect){
    var l_Return = null;  
    var l_Select = $x(pSelect);
    var val=pThis.value;

    if(val.indexOf('null') == -1){
      var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=getMarketMaker',0); 
      get.add('F400_TRADEDATE',document.getElementById('P2__TRADE_DATE').value); 
      gReturn = get.get('XML');


      if(gReturn && l_Select){  
          var l_Count = gReturn.getElementsByTagName("option").length;
    alert(l_Count);
          l_Select.length = 0;  
          for(var i=0;i<l_Count;i++){  
            var l_Opt_Xml = gReturn.getElementsByTagName("option")[i];
                appendToSelect(l_Select, l_Opt_Xml.getAttribute('value'),
                l_Opt_Xml.firstChild.nodeValue)


          }
      
       }
    }
    else{
    l_Select.length = 0;
    alert('Please choose Market Maker');

    }
          get = null;

    }  

    function appendToSelect(pSelect, pValue, pContent) {  
        var l_Opt = document.createElement("option");  
        l_Opt.value = pValue;  
        if(document.all){ 
            pSelect.options.add(l_Opt);  
            l_Opt.innerText = pContent;  
         }else{  
            l_Opt.appendChild(document.createTextNode(pContent));  
            pSelect.appendChild(l_Opt);  
        }  
     
    }

     

    2. Ajax call process

     

     

    declare

     

    l_counter number;

     

    l_o_name varchar2(2000);

     

    begin

     

    owa_util.mime_header('text/xml', FALSE );

     

    htp.p('Cache-Control: no-cache');

     

    htp.p('Pragma: no-cache');

     

    owa_util.http_header_close;

     

    htp.prn('<select>');

     

    HTP.prn ('<option value=" ">-Select a Company -</option>');

     

    for rec in (SELECT distinct CompanyDetails.d, CompanyDetails.r

     

    FROM (SELECT HTF.ESCAPE_SC(companyname ||'( '||COMPANYMNEMONIC||' )') d,

     

    HTF.ESCAPE_SC(companyid ||'~'|| companyname ||'( '||COMPANYMNEMONIC||' )') r

     

    FROM d_demotabled

     

    where /*d.active_flag='Y'

     

    and*/ NOT EXISTS (SELECT 1

     

    FROM d_demotable_jndj

     

    WHERE dj.companyid = d.companyid

     

    and jn_operation = 'UPD')

     

    UNION

     

    SELECT HTF.ESCAPE_SC(companyname || '( ' || companymnemonic || ' )') d,

     

    HTF.ESCAPE_SC(companyid || '~' || companyname || '( '|| companymnemonic|| ' )') r

     

    FROM d_demotabled

     

    WHERE TO_DATE (modified_date, 'DD-MON-RRRR') <= :F400_TRADEDATE

     

    AND EXISTS (SELECT 1

     

    FROM d_demotable_jndj

     

    WHERE dj.companyid = d.companyid

     

    and jn_operation = 'UPD')

     

    UNION

     

    SELECT HTF.ESCAPE_SC(companyname ||'( '||COMPANYMNEMONIC||' )') d,

     

    HTF.ESCAPE_SC(companyid ||'~'|| companyname ||'( '||COMPANYMNEMONIC||' )') r

     

    FROM ewindow.d_company

     

    WHERE /*active_flag = 'Y'

     

    AND*/ :F400_TRADEDATE =

     

    TO_DATE (modified_date, 'DD-MON-RRRR')

     

    UNION

     

    SELECT HTF.ESCAPE_SC(companyname ||'( '||COMPANYMNEMONIC||' )') d,

     

    HTF.ESCAPE_SC(companyid ||'~'|| companyname ||'( '||COMPANYMNEMONIC||' )') r

     

    FROM d_demotable_jndj

     

    where

     

    :F400_TRADEDATE >= to_date(modified_date,'DD-MON-RRRR')

     

    and :F400_TRADEDATE < to_date(JN_DATETIME,'DD-MON-RRRR')

     

    AND EXISTS (SELECT 1

     

    FROM d_demotabled

     

    WHERE d.companyid = d.companyid

     

    /*AND d.active_flag='Y'*/)

     

    and jn_operation = 'UPD')CompanyDetails

     

    )

     

    loop

     

    htp.prn('<option value="' || rec.r || '">' || rec.d || '</option>');

     

    end loop;

     

    htp.prn('</select>');

     

    end;

     

    Regards,

    Sruthitamiri!!

  • 5. Re: Ajax call performance problem in list item
    Mike Kutz Expert
    Currently Being Moderated

    Why do you think that a list with 10,000 entries is a good GUI design?????

     

    TESTING NOTES

    I don't think you tested the exact SELECT statement under the same conditions as APEX.

    You really need to run the SQL statement as the 'parsing schema' in SQL*Plus/SQL Developer.

    If you did, you may have only looked at how long it took to execute the query and not how long it took to transfer all the data back to SQL*Plus/SQL Developer.

     

    HTP.PRN NOTES

    You need to know how long it runs as-is and how long it takes to run as a no-op.

    ie your loop becomes:

    LOOP

      NULL;

    END LOOP;

     

    If a majority of your time is spent within that loop, then you need to manually buffer your output.

    PS -  you need to add A LOT of APEX_DEBUG.MESSAGE() code

     

    QUERY NOTES

    You're using UNION which implies DISTINCT.

    Are you sure you don't mean UNION ALL???

     

    Your WHERE clause is wrong.

    :F400_TRADEDATE >= to_date(modified_date,'DD-MON-RRRR')

    and :F400_TRADEDATE < to_date(JN_DATETIME,'DD-MON-RRRR')

     

    APEX bind variables are of type VARCHAR2.  Your columns (modified_date, etc.) should be DATE data types.

    The right-hand sides are converting a DATE to a VARCHAR2 back to a DATE and possibly back to a VARCHAR2 (to match the left-hand-side).

    Nothing on the right-hand side can take advantage of an index which can cause FULL TABLE SCANs

    Just change it to:   to_date(:F500_TRADEDATE, 'DD-MON-YYYY') >= modified_date

     

    CODING NOTES

    Personally, you should add a chr(10) to your <option> lines.

    The HTML standard has defined a limit of how long a single line can be.

    10,000 is above that limit.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points