Home » Developer & Programmer » Forms » Forms taking longer time to execute query / Fetch Data to another block (oracle 10g and windows 12)
Forms taking longer time to execute query / Fetch Data to another block [message #657576] Tue, 15 November 2016 01:27 Go to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Dear All Concerned:

Recently, I have got a problem with my Invoice form, where fetching data from one block to another taking additional time where as it was very fast earlier. I have analyzed the table and found OK. The table has got 7 triggers linked with other tables as well. Kindly suggest me what else I can do to solve the problem. or do you need furthermore information regarding this issue kindly let me know as well. Waiting for your kind advice in this regards.

Thanks and best regards,
-Mahatab.
Re: Forms taking longer time to execute query / Fetch Data to another block [message #657583 is a reply to message #657576] Tue, 15 November 2016 03:44 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do you mean by "fetching data from one block to another"?
That's not a standard forms thing.
Re: Forms taking longer time to execute query / Fetch Data to another block [message #657588 is a reply to message #657583] Tue, 15 November 2016 05:27 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mahatab

fetching data (...) taking additional time where as it was very fast earlier
What changed since "earlier"?
Re: Forms taking longer time to execute query / Fetch Data to another block [message #657592 is a reply to message #657588] Tue, 15 November 2016 07:41 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks for the reply, nothing has changed, only inserting data to the block above is taking much longer time than as usual. Please see the attached file for your better understanding. In addition i am also sending you the code associated under the FACTURER button:

DECLARE
	V_GLOBAL_STOCK NUMBER(10):=0;
	V_FACT_LIMIT NUMBER(5):=0;
BEGIN
	V_GLOBAL_STOCK:=GET_STOCK(:CTRL_DETAILS.P_ID);
	V_FACT_LIMIT:= GET_LIMIT(:CTRL_DETAILS.P_ID);
IF	:CTRL_DETAILS.QTY>V_GLOBAL_STOCK OR V_FACT_LIMIT>:CTRL_DETAILS.QTY THEN
	CLEAR_BLOCK;

ELSE	
--	++++-----------------
IF :DELIVERY_MST.CF='N' THEN
	 		:SYSTEM.MESSAGE_LEVEL:= 10;
	 		COMMIT_FORM;
	 		:SYSTEM.MESSAGE_LEVEL:=0;
	 :DELIVERY_MST.CF:='Y';
ELSE
	NULL;
END IF;

DECLARE    
	V_SL_NO NUMBER;
	V_DUPLICATE NUMBER:=NULL;
  BEGIN
	SELECT NVL(max(to_number(SL_NO)),0)+1 INTO V_SL_NO 	FROM DELIVERY_DETAILS;

--++++++++++
		SELECT NVL(MAX(PROD_ID),0) INTO V_DUPLICATE FROM DELIVERY_DETAILS
		WHERE  		PROD_ID =:CTRL_DETAILS.P_ID
					AND INV_NO  = :DELIVERY_MST.INV_NO
					AND INV_BY  =:GLOBAL.G_USER 
					AND DELIVERY_DATE=TRUNC(SYSDATE);
					--AND ROWNUM=1;
					
	IF V_DUPLICATE = 0 THEN
	-->>>>
		INSERT INTO DELIVERY_DETAILS (
		SL_NO, INV_NO, INV_BY, 
		DELIVERY_DATE, PROD_ID, DELIVERY_RATE, 
		TOTAL_PRICE, 
		PROD_QTY, DELIVERY_RATE_FRANC) 
	VALUES (
		V_SL_NO ,:DELIVERY_MST.INV_NO ,:GLOBAL.G_USER,
		TRUNC(SYSDATE) ,:CTRL_DETAILS.P_ID ,:CTRL_DETAILS.UNIT_PRICE$,
		:CTRL_DETAILS.UNIT_PRICE_FC*:CTRL_DETAILS.QTY ,
		:CTRL_DETAILS.QTY,:CTRL_DETAILS.UNIT_PRICE_FC );
		forms_ddl('COMMIT');
		:SYSTEM.MESSAGE_LEVEL:=25;
		:SYSTEM.MESSAGE_LEVEL:=15;
		
		GO_BLOCK('DELIVERY_DETAILS');
		EXECUTE_QUERY;
	--	LAST_RECORD;
		GO_BLOCK('CTRL_DETAILS');
		CLEAR_BLOCK;
		GO_ITEM('CTRL_DETAILS.P_ID');

	ELSE
	CLEAR_BLOCK;
	END IF;
--+++++++++++++++++++++++++++++++++

EXCEPTION
	WHEN NO_DATA_FOUND THEN MESSAGE('No Data');
	WHEN OTHERS THEN MESSAGE(SQLERRM);
end;

Set_Item_Property( 'CTRL_DETAILS.P_NAME'			, VISIBLE,PROPERTY_FALSE);
Set_Item_Property( 'CTRL_DETAILS.UNIT_PRICE$'	, VISIBLE,PROPERTY_FALSE);
Set_Item_Property( 'CTRL_DETAILS.UNIT_PRICE_FC', VISIBLE,PROPERTY_FALSE);
Set_Item_Property( 'CTRL_DETAILS.QTY'					, VISIBLE,PROPERTY_FALSE);
Set_Item_Property( 'CTRL_DETAILS.INSERT'			, VISIBLE,PROPERTY_FALSE);
GO_ITEM('CTRL_DETAILS.P_ID');
-------+++++++++++++
END IF;
END;
Re: Forms taking longer time to execute query / Fetch Data to another block [message #657594 is a reply to message #657592] Tue, 15 November 2016 07:58 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
How many rows are in DELIVERY_DETAILS?
What are the indexes on DELIVERY_DETAILS?
What to GET_STOCK and GET_LIMIT do?

selecting max + 1 is sure to fail in a multi-user environment.
Re: Forms taking longer time to execute query / Fetch Data to another block [message #657597 is a reply to message #657594] Tue, 15 November 2016 08:04 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

23498 Rows under the Delivery_details
Dear Joy Division,
Thanks for your reply.

There are 4 Indexes
Get_stock and Get_limit function return the global stock and limit quantity to invoice for.

Any furthermore suggestion or hints please? Waiting for your kind reply.

Regards,
-Mahatab.
Re: Forms taking longer time to execute query / Fetch Data to another block [message #657617 is a reply to message #657597] Wed, 16 November 2016 00:18 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
  • I suppose that "INSERT INTO DELIVERY_DETAILS" itself runs fast.
  • As Joy Division said, "MAX + 1" is a time bomb; why wouldn't you, rather, use a sequence?
  • There are two functions: GET_STOCK and GET_LIMIT. How "fast" do they return the result?
  • Extract all SELECTs (as well as those functions) and "simulate" their execution in SQL*Plus (or any other tool you use). Observe their execution, check explain plan. Find the bottleneck.
  • Alternatively, put MESSAGE calls into the code, between every (meaningful) command, so that you could "visually" see which part of code takes long to execute
  • Once you find the culprit, we'll see if it can be improved
Previous Topic: How to receive user input in oracle form at run-time?
Next Topic: Application setup on logon
Goto Forum:
  


Current Time: Thu Mar 28 07:36:29 CDT 2024