Home » Developer & Programmer » Forms » Slow performance when retriving data
Slow performance when retriving data [message #684873] Thu, 16 September 2021 06:36 Go to next message
jury68000
Messages: 33
Registered: October 2019
Member
I got one fmb to work on. And first what got my attention was that its main data block query is taking about 40 seconds to grab and show the data.
40 seconds is way long, way too long so I decided to look under the hood.
First I took the query to Toad and was surprised that it takes about 1-2 seconds (instead of those 40)
So I begun a search for finding a trigger (like post-query, post-change, ...) that fills up the other 39 seconds.
I did not find anything what looked like it could take this long, so I started slowly deleting the objects from this fmb and running it to see if the query time takes shorter.
This way I took off almost everyting from this fmb and now I just have one data block with just one database column and only one trigger which is when-new-form-instance where default_where is set and execute_query done and nothing more. And it still takes 40 seconds.
So I created a completely new fmb where I put just one data block with one column and its based on the same view with the same pl/sql code in when-new-form-instance. And when I run this new fmb it takes 1-2 seconds to retrive and show the data.
So now I started to compare all the properties from the new fmb with the same object properties of the original fmb (data block, canvas ...) but I have not found any significant differences (besides width, height and this kind)
I'm lost. What else can I check to find out why the original module takes so long to retrive and show the data?
Running debugger is unfortunaltely out of question in our case.

Forms 10g, Windows 7
Re: Slow performance when retriving data [message #684879 is a reply to message #684873] Mon, 20 September 2021 04:04 Go to previous messageGo to next message
afraid of 9
Messages: 4
Registered: September 2021
Junior Member
Hello jury. Hm, sounds strange. Did you take a look at the Number of Records Displayed and Query All Records properties of your block? Perhaps that's helpful.
Re: Slow performance when retriving data [message #684882 is a reply to message #684879] Tue, 21 September 2021 03:56 Go to previous messageGo to next message
jury68000
Messages: 33
Registered: October 2019
Member
Thanks for suggestion, but yes, I have checked those properties (and done some changes on both forms) but it did not help.
I still do not know why this happens and will probobly give up and recreate this module sometime.
Re: Slow performance when retriving data [message #684883 is a reply to message #684882] Tue, 21 September 2021 04:46 Go to previous messageGo to next message
afraid of 9
Messages: 4
Registered: September 2021
Junior Member
We could try one more thing before giving up. Try to store in a variable the last query executed in your data block. You can do it in the POST-QUERY trigger of your data block using the GET_BLOCK_PROPERTY('yourblock', LAST_QUERY). Then, you could insert the contents of that variable into a temporary table with the purpose to see what is actually being executed in your form.

For example:

DECLARE
	l_sql VARCHAR2(32767) := get_block_property('mydbblock', LAST_QUERY);
BEGIN
	-- If you already have a procedure to log messages you could use that.
	INSERT INTO log_tbl VALUES (l_sql);
	COMMIT;
	/*
	** You could also use:
	**
	MESSAGE(l_sql);
	PAUSE;
	**
	** But the problem is that MESSAGE is limited up to 200 characters long.
	*/
END;

[Updated on: Tue, 21 September 2021 05:16]

Report message to a moderator

Re: Slow performance when retriving data [message #684891 is a reply to message #684883] Wed, 22 September 2021 02:20 Go to previous messageGo to next message
jury68000
Messages: 33
Registered: October 2019
Member
Thanks for ideas, but this is the first thing I checked Smile
And as mentioned, this query run in Toad takes at most 1 second, but through the mentioned Forms module it takes uncomparably way much longer.
And when I tried to set this query in a completely new fmb it also takes 1 second to retrive and show all the data.
Weird this is.
Re: Slow performance when retriving data [message #684892 is a reply to message #684891] Wed, 22 September 2021 02:34 Go to previous messageGo to next message
afraid of 9
Messages: 4
Registered: September 2021
Junior Member
Oh, I'm sorry I couldn't help, jury. Well, if creating a new fmb solves it that sounds like the way to go, it can't be helped. Hopefully someone else comes with a new idea. Good luck!
Re: Slow performance when retriving data [message #686252 is a reply to message #684892] Wed, 13 July 2022 03:37 Go to previous message
jury68000
Messages: 33
Registered: October 2019
Member
Answering Michel's question from here:

http://www.orafaq.com/forum/mv/msg/208069/686250/#msg_686250

No, I have not found the solution to this. But lately I found out some other module which also is so slow beyond my understanding so I will be doing the investigation again so maybe this time I will come up with something more.
Previous Topic: FRM-18122: Oracle Forms debugger failed to initialize
Next Topic: Run with a single Code
Goto Forum:
  


Current Time: Thu Mar 28 16:31:02 CDT 2024