Toad World® Forums

Nonlinear performance of editor with package with lots of functions

Disclaimer: I don’t usually code packages with that much functions :wink:

Use the following code to generate a package source:

SELECT ‘CREATE OR REPLACE PACKAGE toad_test AS’ FROM dual UNION ALL
SELECT ’ FUNCTION f’ || ROWNUM || ’ RETURN NUMBER; – comment’ FROM dual CONNECT BY ROWNUM <= 5000 UNION ALL
SELECT ‘END;’ FROM dual

Executing the generated package with F9 seems to take approx. 10 seconds before actually compiling and another 5 seconds after compiling - the real compilation takes 2 seconds.
Reducing the package to “only” 1000 functions seems to reduce the total time to 1 second.

Should not be too relevant for packages with a sane size, but I thought I’d let you know in case there was a quick win that might also affect smaller packages.

BTW: This only seems to happen when the package already exists and a modified version is compiled.

BTW: This only seems to happen when the package already exists and a modified version is compiled.

Compiling awaits parsing if required. If you make a modification to the source and press F9 then we wait until parsing is complete before sending to Oracle. To get a feel for how long a full parse of that code takes click the refresh button on the Navigator. That does a parse and waits for results. When you make your modifications wait for ~20 secs or so for background parsing to complete and then without making any edits use F9. Is compiling fast then? When I compile your 5000 function test package and I take parsing out of the equation I’m seeing a 1-2 second compile followed by a fast select looking for errors followed by a 1-2 second alter package compile w/ debug. If I simulate an edit followed by compile then I see at least 10 second delay before compiling which is expected.

Michael

Thanks a for the detailed description, this explains a lot.

The remaining interesting point for me is that 1000 lines seem to take approximately one second, 5000 lines up to 15 seconds and 10.000 lines (too much to be compiled) take more than 60 seconds.

But as I said: “Should not be too relevant for packages with a sane size, but I thought I’d let you know in case there was a quick win that might also affect smaller packages.” :slight_smile: