11 Replies Latest reply: Mar 4, 2013 7:48 AM by Selim_Rahman RSS

    minimise sorting time in form 6i

    Selim_Rahman
      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çois Degrelle
          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
            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çois Degrelle
              Show the code in your button.

              Francois
              • 4. Re: minimise sorting time in form 6i
                Selim_Rahman
                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çois Degrelle
                  I don't see where you order the query in that code.

                  Francois
                  • 6. Re: minimise sorting time in form 6i
                    Selim_Rahman
                    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
                      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
                        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
                          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
                            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
                              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...