Home » Developer & Programmer » Forms » Oracle Forms 6i Query Performance Issue and Oracle forms 6i performance issue (merged-djm)
Oracle Forms 6i Query Performance Issue and Oracle forms 6i performance issue (merged-djm) [message #289933] Wed, 26 December 2007 13:15 Go to next message
oracle1001
Messages: 4
Registered: December 2007
Location: chennnai
Junior Member
Hi All,


I'm using oracle forms6i and Oracle DB 9i.
I'm facing the performance issue in query forms.
In detail block form taking long time to load the data.

Form contains 2 non data blocks
1.HDR - 3 input parameters
2.DETAILS - Grid - Details


HDR input fields
1.Company Code
2.Company ACccount No
3.Customer Name

Details Grid is displayed the details.


Here there are 2 tables involved

1.Table1 - 1 crore records
2.Table2 - 4 crore records

In form procedure one cursor bulid and fetch is done directly and assign the values to form block fields.
Below i've pasted the query

-----------
SELECT

t1.entry_dt,

t2.authoriser_code,

t1.company_code,

t1.company_ac_no

initcap(t1.customer_name) cust_name,

t2.agreement_no

t1.customer_id

FROM

table1 t1,

table2 t2

WHERE

(t2.trans_no = t1.trans_no or t2.temp_trans_no = t1.trans_no)

AND t1.company_code = nvl(:hdr.l_company_code,t1.company_code)

AND t1.company_ac_no = nvl(:hdr.l_company_ac_no,t1.company_ac_no)

AND lower(t1.customer_name) LIKE lower(nvl('%'||:hdr.l_customer_name||'%' ,t1.customer_name))

GROUP BY

t2.authoriser_code,

t1.company_code,

t1.company_ac_no,

t1.customer_name,

t2.agreement_no,

t1.customer_id;
-----------


Where Clause Analysis
---------------------

1.Condition 1 OR operator (In table2 two different columbs are compared with one column in table)
2.Like Operator
3.All the columns has index but not used properly always full table scan
4.NVL chk
5.If i run the qry in backend means coming little fast,front end very slow

Input Parameter - Query retrival data - limit
-------------------------------------------------------------------

Only compnay code means record count will be 50 - 500 records -
Only compnay code and comp ac number means record count will be 1-5
Only compnay code,omp ac number and customer name means record count will be 1 - 5 records


I have tried following ways

1.Split the query using UNIOIN (OR clause seaparted) - Nested loops COST 850 , Nested loops COST 750 - index by row id - cost

is 160 ,index by row id - cost is 152 full table access.................................

2.Dynamic SQL build - 'DBMS_SQL.DEFINE COLUMN .....

3.Given onlu one input parameter - Nested loops COST 780 , Nested loops COST 780 - index by row id - cost is 148 ,index by

row id - cost is 152 full table access.................................


Still im facing the same issue.

Please help me out on this.


Thanks and Regards,

Oracle1001
Oracle forms 6i performance issue [message #290207 is a reply to message #289933] Thu, 27 December 2007 23:42 Go to previous messageGo to next message
oracle1001
Messages: 4
Registered: December 2007
Location: chennnai
Junior Member
Hi All,

Tools - Oracle DB 9i Oracle forms 6i

I have one query form.

Im using cursor in form and fetch the data directly and assign to the form block values.


There are two table involved here.
Table1 has 1 crore record.
Table2 has 4 crore record.

The query fetch the data 50-500 records.

In backend the select query is fast.
In front end ,it is taking time to load the data in grid.

How can i fetch the data quickly in front end.

Please help me out on this.


Regards,
Oracle1001
Re: Oracle forms 6i performance issue [message #290209 is a reply to message #290207] Fri, 28 December 2007 00:02 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
Why are you using a cursor?
Can you not set the table as the Query Data Source of the block and list directly?

Minto
Re: Oracle forms 6i performance issue [message #290223 is a reply to message #290209] Fri, 28 December 2007 01:29 Go to previous messageGo to next message
oracle1001
Messages: 4
Registered: December 2007
Location: chennnai
Junior Member
Hi,

I'm facing the performance issue in query forms.
In detail block form taking long time to load the data.

Form contains 2 non data blocks
1.HDR - 3 input parameters
2.DETAILS - Grid - Details


HDR input fields
1.Company Code
2.Company ACccount No
3.Customer Name

Details Grid is displayed the details.


Here there are 2 tables involved

1.Table1 - 1 crore records
2.Table2 - 4 crore records

In form procedure one cursor bulid and fetch is done directly and assign the values to form block fields.
Below i've pasted the query
SELECT   t1.entry_dt, 
         t2.authoriser_code, 
         t1.company_code, 
         t1.company_ac_no,
         INITCAP (t1.customer_name) cust_name, 
         t2.agreement_no,
         t1.customer_id
    FROM table1 t1, table2 t2
   WHERE (   t2.trans_no = t1.trans_no
          OR t2.temp_trans_no = t1.trans_no)
     AND t1.company_code = NVL (:hdr.l_company_code, t1.company_code)
     AND t1.company_ac_no = NVL (:hdr.l_company_ac_no, t1.company_ac_no)
     AND LOWER (t1.customer_name) LIKE
             LOWER (NVL ('%' || :hdr.l_customer_name || '%', t1.customer_name))
GROUP BY t2.authoriser_code,
         t1.company_code,
         t1.company_ac_no,
         t1.customer_name,
         t2.agreement_no,
         t1.customer_id;

Backend query returning data quickly.
In Front End assign the data to form is very slow.

Front end i have tried dynamic sql.

Thanks
Oracle1001

[EDITED by LF: reformatted and added [code] tags]

[Updated on: Fri, 28 December 2007 01:46] by Moderator

Report message to a moderator

Re: Oracle forms 6i performance issue [message #290227 is a reply to message #290223] Fri, 28 December 2007 01:43 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the purpose of a GROUP BY clause? You'd do the same with SELECT DISTINCT.

By the way, you didn't answer Mintomohan's questions.

Next time you post code lines, please, format it and use [code] tags to improve readability. If you don't know how to do that, read the "How to format your post" section of the OraFAQ Forum Guide. This time, I'll do it for you.
Re: Oracle forms 6i performance issue [message #290283 is a reply to message #290223] Fri, 28 December 2007 04:52 Go to previous message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
hi oracle1001,

what i asked was why you are using a cursor to populate the data ?

Why can't to use EXECUTE_QUERY built-in to list the records ?

Minto
Previous Topic: THE ATTACHED FILE DIDN'T WORK WITH ME !!
Next Topic: Item Type property of an item
Goto Forum:
  


Current Time: Mon Nov 04 05:46:33 CST 2024