Home » Developer & Programmer » Forms » Procedure Execution Taking Too Long (Forms 6i, Database 11g)
Procedure Execution Taking Too Long [message #565251] Sat, 01 September 2012 10:04 Go to next message
elmousa68
Messages: 7
Registered: September 2012
Junior Member
We have a form with a master block and 6 detail blocks. The master block contains a button that populates 4 of the details blocks from a large table (table contains about 8 million records) (let's call it Big_Table). What the button essentially does is this:

delete from Detail_Table1 where Detail_Table_Key=Master_Block_Key;
insert into Detail_Table1 (field1,field2,..,fieldn) as select field1, field2,...,fieldn from Big_Table where cond1 and cond2 and cond3;
go_block('detail_block1');
execute_query;

The above 3 statements are repeated for the remaining 3 blocks.

This form is used by about 15 users who have the same database user. These users have been given different "users" to use as login users through a custom-built login screen.

The problem we are facing is that sometimes the button in question populates the detail records instantly and at other times takes a long time (5 to 10 minutes, maybe longer) to execute.

This is causing an unacceptable delay for the data entry operation.

Big_Table is used by other applications using the same database user without problems.

Can anyone point out a possible cause for this problem?

Thank you in advance.

Note: We are using Forms 6i with Database 11g.
Re: Procedure Execution Taking Too Long [message #565252 is a reply to message #565251] Sat, 01 September 2012 11:16 Go to previous message
cookiemonster
Messages: 11089
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want to know what's taking the time then trace the sessions.
However I really don't see the point of having a procedure that populates separate tables from the main table.
Why not just have each block query the main table directly? It'll be more efficient.
Previous Topic: CLIENT_OLE2 NOT WORKING IN Forms Version 10.1.2.0.2
Next Topic: Top down organizational structure
Goto Forum:
  


Current Time: Fri Oct 31 15:59:56 CDT 2014

Total time taken to generate the page: 0.14608 seconds