Search code examples
apex-codeforce.comsoql

governor limits with reports in SFDC

We have a business requirement to show a cost summary for all our projects in a single table.

In order to tabulate these costs we have to query through all the client tasks, regions, job roles, pay rates, cost tables, deliverables, efforts, and hour records (client tasks are in the same table and tasks and regions are in the same table and deliverables, effort, and hours are stored as monthly totals).

Since I have to query all of this before I go for-looping through everything it hits a large number of scripting lines very quickly. Computationally it's like O(m * n * o * p) and some of our projects have all four variables that go up very quickly. My estimates for how to do this have ranged from 90 million lines of code to 600 billion.

Using batch apex we could break this up by task regions into 200 batches, but that would reduce the computational profile to (600 B / 200 ) = 3 billion lines of code (well above the salesforce limit.

We have been playing around with using informatica to do these massive calculations, but we have several problems including (1) our end users can not wait more than five or so minutes, but just transferring the data (90% of all records if all the projects got updated at once) would take 15 minutes over informatica or the web api (2) we have noticed these massive calculations need to happen in several places (changing a deliverable forecast value, creating an initial forecast, etc).

Is there a governor limit work around that will meet our requirements here (massive volume of data with response in 5 or so minutes? Is force.com a good platform for us to use here?

This is the way I've been doing it for a similar calculation:

Loop Overview

Solution

  • An ERD would help, but have you considered doing this in smaller pieces and with reports in salesforce instead of custom code? By smaller pieces I mean, use roll-up summary fields to get some totals higher in your tree of objects. Or use apex triggers so as hours are entered the cost * hours is calculated and placed onto the time record, and then rolled-up to the deliverables. Basically get your values calculated at the time the data is entered instead of having to run your calculations every time.

    Then you can simply run a report that says show me all my projects and their total cost or total time because those total costs/times are stored/calculated already.

    Roll-up summaries only work with master-detail Triggers work anytime, but you'll want to account for insert, update as well as delete and undelete! Aggregate Functions will be your friend assuming that the trigger context has fewer than 50,000 records to aggregate - which I'd hope it does b/c if there are more than 50,000 time entries for a single deliverable that's a BIG deliverable :)

    Hope that helps a bit?