This discussion is archived
6 Replies Latest reply: Feb 16, 2013 11:59 PM by 987536 RSS

Avoiding to much of database hit in post query

987536 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
  • 4. Re: Avoiding to much of database hit in post query
    987536 Newbie
    Currently Being Moderated
    Thanks.
  • 5. Re: Avoiding to much of database hit in post query
    user346369 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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