| Slow performance when retriving data [message #684873] |
Thu, 16 September 2021 06:36  |
 |
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 #684883 is a reply to message #684882] |
Tue, 21 September 2021 04:46  |
 |
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 |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
