Yikes, why is my APEX app so huge?

I recently started having major issues with one of my applications. It had suddenly ballooned to over 75 Mb in size, and it took me a while to figure out why. A 75 Mb app means major headaches when it comes time to pushing things to production.

It would bomb out using the APEX front end (Export from TEST, Import and Install in PROD), and I also had major struggles using the SQL*Developer ‘Deploy Application’ feature. I think that 75 Mb is a challenge anywhere…

It quickly got to a point where I would literally be holding my breath and stressing about every move to production. Simple updates were causing me sleepless nights. I ended up updating my production application manually on several occasions just to avoid the whole process. Not cool.

What was making my app so big? It had a lot of pages, but not THAT many. Translations? I exported it without the French language version, and this had very little impact on the final file size.

I finally ended up opening the SQL file in a text editor (please don’t ask me why I didn’t do that much sooner, but there you go), and it was very apparent what the cause of all my woes was.

My BI Publisher Report Layouts! And then the proverbial lightbulb moment, when I remembered that those were actually fairly recent, and although tiny in size when weighed as RTF files, they generated huge output in my SQL files. Megs and megs of this:

Ok, so now what? The reports were key to my app’s functionality, I definitely could not remove them.

My stop gap method was not very elegant, but worked. I deleted all the Report Layouts from my Shared Components, exported my app from TEST, (<10 Mb, yay!!!), and imported into Production without any issues (obviously!). I then manually re-uploaded the 10 or so BI Publisher reports, and re-assigned them to the correct Report Queries, problem solved. The whole process took less than an hour. Not bad, but still a TON of overhead every time…

I needed a better solution.

Here is what I ended up doing, and I highly recommend it to anybody that plans on having more than a (small) handful of BI Publisher Report Layouts in their apps…

I basically broke out all my Report Queries and Report Layouts into a completely separate app. Updates to the reports happen very rarely, and when they do, tweaking the Report Query or uploading a new Report Template into the Production App is no big deal.

All my reports were originally called in a PL/SQL region on a page 172, which displayed a report description and included a link to print the report, which submitted the page and set that value of the REQUEST to be the Report Name.

The link looked something like this:

f?p=&APP_ID..:0:&APP_SESSION.:PRINT_REPORT=&REQUEST.

Here are the changes I had to make:

1. I created a new application whose only purpose was to house the reports. I used the same cookie name for both applications.
2. I copied page 172 in my main application to page 172 in my reporting application, ensuring I got all my items and parameters for the reports.
3. I then added a snippet to the Initialization PL/SQL code of my reporting app to read the values of my page 172 items in my original app (:P_ORIG_APP)

   begin
   
   :P172_USER_ID:=apex_util.fetch_app_item('P_172_USER_ID',:P_ORIG_APP,:APP_SESSION);
   :P172_DATE_FROM:=apex_util.fetch_app_item('P_172_DATE_FROM',:P_ORIG_APP,:APP_SESSION);
   :P172_DATE_TO:=apex_util.fetch_app_item('P_172_DATE_TO',:P_ORIG_APP,:APP_SESSION);
   
   end;

   I am not sure why I didn’t just pass the items through the link, I am hoping at the time there was a good reason for that… But in any case, this worked. :-)  I think there are probably better ways of reading session state in other apps, but as soon as I saw this worked I stopped looking.

4. I then simply changed the link in my original app to call the reports in my new reporting app (:P_REPORT_APP)

   f?p=&P_REPORT_APP.:0:&APP_SESSION.:PRINT_REPORT=&REQUEST.

Voilà! I hardly ever have to touch my reporting app, and the updates to production are back to being the smooth and painless process we all know and ‘love’, since my export file is less than 10 Mb in size, translations included.