One of our reasons to use toad was the possibility to automate model design and ddl creation with scripting. Now working intensive with toad scripting we found some problems which are very strange.
When script is running, program’s memory consumption is increasing to sometimes 1,8Gbyte (!!!) and then leads to a crash. We splitted the whole task into separate macro calls to avoid this problem. Nevertheless, also the single macros raise up to 1.2Gbytes which is sometimes a problem, if the program does not release that memory after execution (Toad restart will then be required). Info: memory is never released if a script coding failure is detected and script is stopped.
So far we are permanentely struggling with that type of problems, clean work is not possible.
Is there any experience with that subject or any solutions ?
Some background information:
We have one base model used as our “single source”.
As our product supports many dbs, this base model is converted to models for each of our supported dbs (only as “temporary” models, as that models are only generated from base). Before creating ddl scripts from that temporary models, we use scripting for adjusting our requiremnts in the model per mouse click:
- creating audit tables
- creating triggers for the audit tables
- creating triggers for our “ChangedDate” columns in several tables
- adapt some properties depending on the physical model
- create “AfterScript” for DDL generation
- set generator default properties (selectedt otps, …) for ddl generation
The script code is located in the “MyPackage” and is splitted into diffrent script files:
- lg_core (Java Script extension, File IO,…)
- lg_common (db independent helpers for toad model access and modification)
- lg_ (db specific scripts for each supported db, files name is like lg_mssql, lg_postgres, lg_oracle…)
Functions from one script files can call functions from the other files.
- lg_common and lg_ make calls to lg_core.
- lg_common makes calls to lg_ to call db specific extensions.
- lg_ can also make calls to lg_common to use basic methods.
The scripts seems to be very simple.
Basically, they iterate through model objects like entities, views, triggers, … and optionally create some objects or set some properties. Even if no objects will be created, memory will increase only by iterating through that objects.
It seems that a call to a method in an other (already used) script file increases the memory much more than if a call to a local method, this is the same for accessing simple constants. It seems that the same external script will always completely be load into memory with every method call, even it was called before ?!?!.