6 Replies Latest reply: Feb 17, 2013 1:59 AM by 987536 RSS

    Avoiding to much of database hit in post query

    987536
      Hi all,
      I am now working oracle form 10g . i have one field name as customer number in my form.Based on the customer number i need to retrieve the name of the customer in the display field . i retrieve every time more than 2000 record. i wrote a post query to retrieve the customer name. my problem is, it take to much time.how to reduce the database call and retrieval time ?

      note : customer name is present in different database i use db link to retrieve the data.
      My post query trigger in block level
      BEGIN
           SELECT PARTY_NAME||'
           :'||PARTY_TYPE INTO :BLK_SEAGRS_CREDIT_PASSTHROUGH.DISP_CUST
      FROM HZ_PARTIES@DAP2.WORLD
      WHERE PARTY_ID=(SELECT PARTY_ID
      FROM HZ_PARTY_SITES@DAP2.WORLD
      WHERE PARTY_SITE_ID=(SELECT PARTY_SITE_ID
      FROM HZ_CUST_ACCT_SITES_ALL@DAP2.WORLD
      WHERE CUST_ACCT_SITE_ID = (SELECT CUST_ACCT_SITE_ID
      FROM HZ_CUST_SITE_USES_ALL@DAP2.WORLD where SITE_USE_CODE = :BLK_SEAGRS_CREDIT_PASSTHROUGH.TEXT_CUST_LEVEL AND
      location = :BLK_SEAGRS_CREDIT_PASSTHROUGH.TEXT_CUST_VALUE AND ORG_ID = 189)));

      EXCEPTION
           WHEN NO_DATA_FOUND THEN
                :BLK_SEAGRS_CREDIT_PASSTHROUGH.DISP_CUST :='No Customer Information Available';
           WHEN TOO_MANY_ROWS THEN
                     :BLK_SEAGRS_CREDIT_PASSTHROUGH.DISP_CUST :='Too Many Matches Are Available';
                WHEN OTHERS THEN
                     :BLK_SEAGRS_CREDIT_PASSTHROUGH.DISP_CUST :='Other Exception Occur';
                     
      END;

      BLK_SEAGRS_CREDIT_PASSTHROUGH.DISP_CUST -display field name

      Is any alternative for this post query?

      please help me to improve the performances of the form.

      Thanks in advance.

      Regards,
      Karthik M

      Edited by: 984533 on Feb 15, 2013 9:02 PM
        • 1. Re: Avoiding to much of database hit in post query
          CraigB
          First, you are clearly working in an Oracle Enterprise Business Suite (EBS) environment. This means you have a level of complication that non-EBS developers don't have to deal with. The first thing you need to do is to make sure you are including all of the necessary tables to reduce your result set.

          The second thing "I" would do is get rid of the Sub-Queries! I would join the tables instead. Sub-queries are more inefficient than a standard join. Try this instead:
           SELECT p.PARTY_NAME||':'||p.PARTY_TYPE 
             INTO :BLK_SEAGRS_CREDIT_PASSTHROUGH.DISP_CUST
             FROM HZ_PARTIES@DAP2.WORLD p, HZ_PARTY_SITES@DAP2.WORLD ps, HZ_CUST_SITE_USES_ALL@DAP2.WORLD cs
            WHERE p.party_id = ps.party_id
              AND ps.party_site_id = cs.party_site_id
              AND cs.site_use_code = :BLK_SEAGRS_CREDIT_PASSTHROUGH.TEXT_CUST_LEVEL
              AND cs.location = :BLK_SEAGRS_CREDIT_PASSTHROUGH.TEXT_CUST_VALUE 
              AND cs.ORG_ID = 189;
          This is a much more efficient query. Next step is to run your query through explain plan and make sure you don't have other inefficiencies or a cartisian. However, I think the above suggested changes to your query should improve its performance. Unfortunately, I don't have access to an EBS instance so I can't test this query myself.

          Hope this helps.
          Craig...
          • 2. Re: Avoiding to much of database hit in post query
            MLBrown
            This isn't really a forms question but more of a SQL tuning question. You should probably post this question out on {forum:id=75}, but before you do they are going to want to see statistics on the query. You might be able to make the query quicker with indices on the columns you are selecting against, but I would check with them and close this message.
            • 3. Re: Avoiding to much of database hit in post query
              Andreas Weiden
              regarding the use of the POST-QUERY-trigger, it is a forms-question.

              Instaed of using a POST-QUERY-trigger which reads additional data record by record, i would recommend to build view which already includes this additional data, then base your block in forms on that view. With that, there are no more additional database-calls.
              • 5. Re: Avoiding to much of database hit in post query
                user346369
                Another issue here is that you write:
                i retrieve every time more than 2000 record.
                No matter how fast your SQL query is, pulling that many rows of data out of the database will take some time, and you definitely cannot display that on your form in any meaningful way to the user. There is something wrong with the design of the form that requires you to retrieve over 2k rows.

                Maybe if you explained a bit more, people could make some suggestions on ways to query a smaller group of data.
                • 6. Re: Avoiding to much of database hit in post query
                  987536
                  Hi Steve Cosner,
                  In my case , there is a Query find form, in which user restricted the number of rows to be displayed in the main form(that contain the customer bill to,ship to information). whenever the customer bill_to or ship_to is information is present in the main form , i need to display customer name and description in bottom of the form(display field). for that i wrote post query trigger to display.
                  Most of the cases the user want to see bulk of customer information with high performances.

                  Thanks and Regards,
                  Karthik M