cancel
Showing results for 
Search instead for 
Did you mean: 

How to deal with a huge report

Former Member
0 Kudos

PB 12.5.1

Looking for some suggestions on how to deal with a report in a DW that is very large (around 500 MB).  Without going into all the details they need these reports because they have to show them to the auditors.  I have a report threading program that will allow these reports to run as a seperate thread.  Once the report finishes it then uses GetFullState() to return the result set. The problem is once the GetFullState call is made it will error out because Sybase is unable to allocate the blocks of memory necessary to return the result set.

My first thought was to use the Storage = Disk option but this does not work as advertised (uses almost as much memory).  My next step is to have the thread save the data and the DW syntax into the database and the have the client restore this into the viewer.  Another thought is to save it as a PSR and then the client just view that. 

Are there any other ideas or suggestions or rule of thumb things out there for dealing with large reports that have to be stored and viewed?

Thanks,

Chris Craft

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Chris;

  Yes, the GetFullState would require another big chunk of memory which as you state, is in short demand by the time you get to that point in your report generation.

   I would definitely try the PSR option!

  I don't think Bruce's "Retrieve as Needed" (RAN) suggestion will fly as I suspect your report needs all the data immediately in order to compute grand totals. Plus, once you add sorting & filtering the RAN is ignored by PB anyway.

Good luck!

Regards ... Chris

Former Member
0 Kudos

Have you not had to really deal with this situation?  I would imagine working for the government that your reports would be huge if they needed detail activity, but on the other hand - maybe they don't 'audit' themselves so they never need to generate these reports :-).

I will look into the PSR option - the one negative side to that is they need to hit the disk which could be a security risk.  How have you dealt with that?

Chris

Former Member
0 Kudos

After the report finishes, can't you have it do a saveas pdf?

Or do you auditors require the ability to do filtering, searching etc on the results?  In which case saving as .xlsx is an option?

Former Member
0 Kudos

Hi Chris;

  Actually, the biggest report I ever did was for the RCMP's 911 Call Tracking System where some Division summary reports have over 32 child DWO's within 23 nested secondary DWO's (and that did not include DDDW's)!

  These reports have been running for years! However, I did do some "Chris Pollach" special tricks to optimize performance & memory. The same things you now find in my Foundation Class framework.  🙂

Regards ... Chris

Former Member
0 Kudos

We use a PB viewer window to view the results so that is currently not an option.  I do plan on offering the SaveAs option for our scheduled reports but right now it is not applicable.

On the same lines as the PSR - how would you 'secure' this since it has to be written to the file system?

Chris

Former Member
0 Kudos

My SOX auditors aren't that paranoid, but then I run the reports myself into a secure directory.

So I would imagine that you grant your program privilegesto save into a directory that only auditors have permission to access.

Alternatively, after saving the file then load it back into the database and delete the temporary copy.

Answers (1)

Answers (1)

former_member190719
Active Contributor
0 Kudos

Are they viewing them online, printing them, or something else?

If viewing, then retrieve as needed might help.

Former Member
0 Kudos

Could be all of the above.  I can't use Retrieve as Needed for a couple reasons, one which Chris stated below, but the other is these reports run in a scheduled queue which executes at night in a Shared Object.  I need all the data as well as the DW format to be preserved because they can keep these reports for a specified period of time.  This is why the GetFullState works so nicely - preserves the DW format and all the data in one nice call!

Chris

Former Member
0 Kudos

I had the same problem with GetFullState before. The solution for me was to break the result set up into two parts and ultimately return two blobs back to the calling thread. I could be wrong but I think I had to use the GetChanges\SetChanges methods to put the report back together. I'll have to look at the code to say for sure.

Former Member
0 Kudos

That sounds promising - if you can find the code to verify what you did I would be interested in seeing it.  Thanks