Hi,
We’re facing several issues regarding the main Jobs of our daily etl. Our DI version is 17.0.0.1
They’re big jobs (more than a thousand objects inside). The problems are related to design, checking in and out, export/import and execution.
Beside memory problems with Designer, we have problems with the execution (more than 10 minutes for compiling in a 4 dual core server) and now with imports and export, to the point that is not possible to import from Designer, so we have no option but to import via al_engine command, and in certain conditions thats is not working either.
We’re thinking in splitting the jobs in several small ones and then link then in a “main” job which launch each one and do the workflow control. In fact this smaller jobs are nowadays DI workflows that groups logical parts of the daily loading of the warehouse.
Beside general suggestions or alternatives, I need to know if in recent versions some of this issues have been faced/solved and if there are another ways to design this bigs jobs. In other words, if we have to re design the jobs altogether with the version upgrade (or not).
Regards,
Andrés
PD: We’re assuming that the version upgrade is a “painful” process in which we need to thoughtfully test all the jobs due to changes in behavior between releases.
aidelia 
(BOB member since 2006-02-02)
We ran into the same problems. Opening the full job would consume 1+ Gb of memory (in Designer, or in the initial al_engine at execute-time). Starting the job would take 10 minutes to “compile”. And we could no longer export either to a file or direct database-to-database.
Upgrading to 11.7.3 helped, and 12.x even more so. You really do need to get off of 11.7.0.1, it’s not worth the ongoing pain. 
Some advice, this may be more lore than truth, but it’s worth a shot:
To make the initial run-time faster:
-
Be sure to compact the production local repository
-
Ensure you’re doing regular DBA maintenance on the repo database (rebuilding indexes and stats and such)
-
Try creating a new (empty) repository, and start migrating to it, using the piece-by-piece technique. Sometimes starting with a clean repo seems like it helps.
To reduce or deal with the overall size:
-
Export your jobs in pieces, if you can (just certain high-level workflows, one at a time.)
-
Remove unnecessary levels of workflows in the job
dnewton 
(BOB member since 2004-01-30)
Thanks a lot for your advises.
So, splitting the jobs is not the right path. At least, not the first thing to do, right?
We have 11.7.0.1 because 2 years ago when we tried to upgrade to 11.7.2.3, very nasty things happens with enormous impact in the jobs performance (from minutes to days). So, we rolled back the upgrade and stayed in 11.7.0. Then, we haven’t had the time and HW to do a complete test before upgrading. But I know we have to do that someday.
The importing-exporting is because our deployment to production is done in that way because we don’t want the operators to learn the checking in/out process just for that. And operators are already complaining for the sudden “hangs” after the import, when designer is recalculating dependencies.
Regards,
Andrés
aidelia (BOB member since 2006-02-02)
You can try the things I suggested, but ultimately, you’ll probably have to split up the jobs.
dnewton (BOB member since 2004-01-30)