Toad World® Forums

Improve performance of Schema Browser Script tab

For quite some time I have noticed that the Script tab on the Schema Browser takes a very long time (30 seconds at times).  I found a few suggestions on the net but none seemed to significantly improve performance.

I ran Toad Advisor and made the changes that were suggested but still no relief.

I believe I finally found what was causing the slowness.  There is an option in the Schema Browser Script tab that seems to be the culprit.  If you navigate as follows...

Schema Browser/Script Tab/View-Edit Script Options/Tables Tab and then uncheck "Rebuild FK's Referencing Selected Table" the performance improvement is very noticeable.

It seems that the more FK's your selected table has, the worse your performance when that option is checked.  It seems that building the FK constraints for child tables when you are displaying the parent table is very costly.

You might want to consider adding this option as a performance suggestion in the Toad Advisor results.

I am using Toad Version 12.10.0.30 against an Oracle 12.1.0.2 database.

For me, it doesn't make any noticeable difference in performance with this option checked or not.    I tested with a table that had two FKs of its own and two FKs pointing back to it (the HR.EMPLOYEES sample table).   For me, the script was generated in a couple of seconds.   Will you turn on spool SQL (Database -> Spool SQL -> Spool to Screen) and then generate the script?    This will capture all of the queries that Toad is executing and I can use it to determine the timings for each.   Then maybe we can speed it up.  Please send the spool sql output to me at john.dorlon@quest.com.   Thanks.