This discussion is archived
11 Replies Latest reply: Mar 4, 2013 5:48 AM by Selim_Rahman RSS

minimise sorting time in form 6i

Selim_Rahman Newbie
Currently Being Moderated
form 6i
database 10g xe

i have a form name payment. there have 30k data in that table. i have a search button by using a lov to search data. My query is fine,Execution time is faster.
but when i want to put a value for sorting purposes it takes too time(cursor shows busy for 10-12 sec) .
my question is that, is there any possible way to minimise sorting time in that search button...
thanks in advance..
  • 1. Re: minimise sorting time in form 6i
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,

    I don't know how we can help you as you don't give a single line of code you use.
    Without any information, all I can say is: "your SQL query or the database table is not well tuned" !

    Francois
  • 2. Re: minimise sorting time in form 6i
    Selim_Rahman Newbie
    Currently Being Moderated
    as i said before , SQL query is fine with database.
    i run this SQL query in SQL developer.
    execution time was faster(around 0.1 second)..
    when i put this query in a search button ..and after click this button it comes faster as i said ..
    but the problem is that,,when i want to put a value for sorting purposes it takes too time(cursor shows busy for 10-12 sec) .
    example:

    suppose PR.12.011577 is a code that i want to search from 30 thousand this type of code
    when i enter PR.12.01 to search the above code it shows busy sign for 10-12 sec in form 6i..
    wether my SQL query takes only below .1 second..

    thanks in advance..
  • 3. Re: minimise sorting time in form 6i
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Show the code in your button.

    Francois
  • 4. Re: minimise sorting time in form 6i
    Selim_Rahman Newbie
    Currently Being Moderated
    François Degrelle wrote:
    Show the code in your button.

    Francois
    i use this code for two different kinds of user.
    one is administrator user and the other is rest of the user
    so, i use this code in WHEN-MOUSE-CLICK triger...


    /*

    DECLARE
         B NUMBER;
         LOV_ID LOV;
    BEGIN
         select COUNT(ISP_USER.USR_ID) INTO B
         from ISP_USER_GROUP,ISP_GROUP,ISP_USER
         where ISP_USER_GROUP.USGR_USER_ID=ISP_USER.USR_ID
         and ISP_GROUP.GRP_ID =ISP_USER_GROUP.USGR_GROUP_ID
         and (ISP_GROUP.GRP_NAME ='ADMINISTRATOR')
         AND ISP_USER.USR_NAME=:GLOBAL.USER_ID;

         LOV_ID :=FIND_LOV('PAYMENT_SEARCH');
         
         IF B=0 THEN
              
              IF GET_LOV_PROPERTY(lov_id,GROUP_NAME) = 'PAYMENT_SEARCH_ADMIN' THEN
                   SET_LOV_PROPERTY(LOV_ID,GROUP_NAME,'PAYMENT_SEARCH');
              END IF;
         ELSE
              IF GET_LOV_PROPERTY(lov_id,GROUP_NAME) = 'PAYMENT_SEARCH' THEN
                   SET_LOV_PROPERTY(LOV_ID,GROUP_NAME,'PAYMENT_SEARCH_ADMIN');
              END IF;          
         END IF;
         
         IF :SYSTEM.MODE = 'ENTER-QUERY' THEN
              IF SHOW_LOV('PAYMENT_SEARCH') THEN
                   EXECUTE_QUERY;
              END IF;
         END IF;
         IF :System.Record_Status = 'NEW' THEN
              Exit_Form(No_Validate);
         END IF;
    END;
    */
  • 5. Re: minimise sorting time in form 6i
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    I don't see where you order the query in that code.

    Francois
  • 6. Re: minimise sorting time in form 6i
    Selim_Rahman Newbie
    Currently Being Moderated
    François Degrelle wrote:
    I don't see where you order the query in that code.

    Francois
    i have 2 record group that i call from the above code

    one record group is PAYMENT_SEARCH and the code is below:

    SELECT DISTINCT PAYMENT_RECEIVE_MST.PRM_CODE, PAYMENT_RECEIVE_MST.PRM_DATE, PATIENT_HISTORY.PH_SUB_CODE,
    PATIENT_HISTORY.PH_PATIENT_CAT, PATIENT_INFO.PAT_NAME, PATIENT_INFO.PAT_CODE,
    PATIENT_INFO.PAT_AGE, PATIENT_INFO.PAT_PHONE, PATIENT_INFO.PAT_VILLAGE,
    DISTRICT_DTL.DD_THANA_NAME, PAYMENT_RECEIVE_MST.PRM_ID
    FROM PAYMENT_RECEIVE_MST, PATIENT_HISTORY, PATIENT_INFO, DISTRICT_DTL
    WHERE ((PAYMENT_RECEIVE_MST.PRM_PH_ID = PATIENT_HISTORY.PH_ID)
    AND (PATIENT_HISTORY.PH_PAT_ID = PATIENT_INFO.PAT_ID)
    AND (PATIENT_INFO.PAT_DD_ID = DISTRICT_DTL.DD_ID))
    AND PAYMENT_RECEIVE_MST.PRM_USER=:GLOBAL.USER_ID


    and the otherrecord group is PAYMENT_SEARCH_ADMIN and the code is below:


    SELECT DISTINCT PAYMENT_RECEIVE_MST.PRM_CODE, PAYMENT_RECEIVE_MST.PRM_DATE, PATIENT_HISTORY.PH_SUB_CODE,
    PATIENT_HISTORY.PH_PATIENT_CAT, PATIENT_INFO.PAT_NAME, PATIENT_INFO.PAT_CODE,
    PATIENT_INFO.PAT_AGE, PATIENT_INFO.PAT_PHONE, PATIENT_INFO.PAT_VILLAGE,
    DISTRICT_DTL.DD_THANA_NAME, PAYMENT_RECEIVE_MST.PRM_ID
    FROM PAYMENT_RECEIVE_MST, PATIENT_HISTORY, PATIENT_INFO, DISTRICT_DTL
    WHERE ((PAYMENT_RECEIVE_MST.PRM_PH_ID = PATIENT_HISTORY.PH_ID)
    AND (PATIENT_HISTORY.PH_PAT_ID = PATIENT_INFO.PAT_ID)
    AND (PATIENT_INFO.PAT_DD_ID = DISTRICT_DTL.DD_ID))
  • 7. Re: minimise sorting time in form 6i
    Andreas Weiden Guru
    Currently Being Moderated
    When you execute an sql-query in a tool like SQL-developer, the query returns after having retrieved the first set of records, whereas when you open a LOV in forms, the query returns after having queried ALL records. So your statement that the query only lasts .1 seconds in SQl-developer might be wrong. Scroll to the last record in the SQl-developers result-window, the. you have the time the query lasts in SQL-developer. You might also have a look at the execution plan of your query.
  • 8. Re: minimise sorting time in form 6i
    Selim_Rahman Newbie
    Currently Being Moderated
    Andreas Weiden wrote:
    whereas when you open a LOV in forms, the query returns after having queried ALL records.
    yes it is true. when i open this lov in my form it takes not enough time,i mean it's faster..it shows 30 thousand data(queried ALL records.) in the lov..
    yes..i understand about sql developer,,thanks for that


    but the problem is that,,when i want to put a value for sorting purposes it takes too time(cursor shows busy for 10-12 sec) .
    example:

    suppose PR.12.011577 is a code that i want to search from 30 thousand this type of code
    when i enter PR.12.01 to search the above code it shows busy sign for 10-12 sec in form 6i..

    thanks in advance..
  • 9. Re: minimise sorting time in form 6i
    user346369 Expert
    Currently Being Moderated
    suppose PR.12.011577 is a code that i want to search from 30 thousand this type of code
    when i enter PR.12.01 to search the above code it shows busy sign for 10-12 sec in form 6i..

    Are you trying to search 30k rows in an LOV???

    If so, it is no wonder it takes your form 10-12 seconds. The form has to retrieve ALL that data into the record group before it can find the rows.

    This is NOT a good application for a record group. You should set things up so the user enters the value, PR.12.01 into a field in the form, than presses a search button. The search button should then copy the value PR.12.01 to a hidden field, and add a % to it, so it is: PR.12.01%
    Then, populate the record group using a where clause that includes
    where column name like :Bx.Hidden_ColumnX

    THAT will be fast.
  • 10. Re: minimise sorting time in form 6i
    Andreas Weiden Guru
    Currently Being Moderated
    There is a property Filter before display for the Lov. if set to Yes, you can enter a search criteria before the lov opens, maybe this helps you.
  • 11. Re: minimise sorting time in form 6i
    Selim_Rahman Newbie
    Currently Being Moderated
    You give me a good idea...
    can u give me the details code that i need to write to search button in when-press-button trigger
    i mean copy the text field data and place it to the lov window (find label)..
    thanks in advance...

Legend

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