Return to Answer

Although this may not be the best answer in the world, I had a similar problem with having to report on 4,000,000+ records.

What I did was to create a custom object that stored calculated records obtained by a batch class.

The specific scenario for us what that we have Applications with other objects as children of the Applications (we're a recruitment company) and we needed to calculate how many Applications had Interviews, Submitals, Deals etc...

I decided to write a relatively simple scheduled batch class that gathered the sum of these from yesterday (i.e. CreatedDate) (as Applications could come in late in the evening and we didn't want to miss those!) and stored them in an object that held the figure (plus a few other bits of 'metadata', such as the date that collection of records were created). That way:

• Rather than querying millions of records, we'd be querying 360 for a years worth of Applications
• All of the leg work done by the batch class could be run out of office hours (e.g. at 1am) so Users wouldn't be affected by performance (although this didn't seem to hit us too hard anyway)

The only problem with this is that you'd need to run this batch class for each day, keep in mind governor limits dictate you can only schedule up to 100 at a time.

Hopefully, this makes sense and you can translate it to your scenario and gives you at least a little bit of an idea as to solve this as I know it's nightmarish!

Although this may not be the best answer in the world, I had a similar problem with having to report on 4,000,000+ records.

What I did was to create a custom object that stored calculated records obtained by a batch class.

The specific scenario for us what that we have Applications with other objects as children of the Applications (we're a recruitment company) and we needed to calculate how many Applications had Interviews, Submitals, Deals etc...

I decided to write a relatively simple scheduled batch class that gathered the sum of these from yesterday (i.e. CreatedDate) (as Applications could come in late in the evening and we didn't want to miss those!) and stored them in an object that held the figure (plus a few other bits of 'metadata', such as the date that collection of records were created). That way:

• Rather than querying millions of records, we'd be querying 360 for a years worth of Applications
• All of the leg work done by the batch class could be run out of office hours (e.g. at 1am) so Users wouldn't be affected by performance (although this didn't seem to hit us too hard anyway)

The only problem with this is that you'd need to run this batch class for each day that you'd want to report on, keep in mind governor limits dictate you can only schedule up to 100 at a time.

Hopefully, this makes sense and you can translate it to your scenario and gives you at least a little bit of an idea as to solve this as I know it's nightmarish!

Although this may not be the best answer in the world, I had a similar problem with having to report on 4,000,000+ records.

What I did was to create a custom object that stored calculated records obtained by a batch class.

The specific scenario for us what that we have Applications with other objects as children of the Applications (we're a recruitment company) and we needed to calculate how many Applications had Interviews, Submitals, Deals etc...

I decided to write a relatively simple scheduled batch class that gathered the sum of these from yesterday (i.e. CreatedDate) (as Applications could come in late in the evening and we didn't want to miss those!) and stored them in an object that held the figure (plus a few other bits of 'metadata'). That way:

• Rather than querying millions of records, we'd be querying 360 for a years worth of Applications
• All of the leg work done by the batch class could be run out of office hours (e.g. at 1am) so Users wouldn't be affected by performance (although this didn't seem to hit us too hard anyway)

The only problem with this is that you'd need to run this batch class for each day, keep in mind governor limits dictate you can only schedule up to 100 at a time.

Hopefully, this makes sense and you can translate it to your scenario and gives you at least a little bit of an idea as to solve this as I know it's nightmarish!

Although this may not be the best answer in the world, I had a similar problem with having to report on 4,000,000+ records.

What I did was to create a custom object that stored calculated records obtained by a batch class.

The specific scenario for us what that we have Applications with other objects as children of the Applications (we're a recruitment company) and we needed to calculate how many Applications had Interviews, Submitals, Deals etc...

I decided to write a relatively simple scheduled batch class that gathered the sum of these from yesterday (i.e. CreatedDate) (as Applications could come in late in the evening and we didn't want to miss those!) and stored them in an object that held the figure (plus a few other bits of 'metadata', such as the date that collection of records were created). That way:

• Rather than querying millions of records, we'd be querying 360 for a years worth of Applications
• All of the leg work done by the batch class could be run out of office hours (e.g. at 1am) so Users wouldn't be affected by performance (although this didn't seem to hit us too hard anyway)

The only problem with this is that you'd need to run this batch class for each day, keep in mind governor limits dictate you can only schedule up to 100 at a time.

Hopefully, this makes sense and you can translate it to your scenario and gives you at least a little bit of an idea as to solve this as I know it's nightmarish!

Although this may not be the best answer in the world, I had a similar problem with having to report on 4,000,000+ records.

What I did was to create a custom object that stored calculated records obtained by a batch class.

The specific scenario for us what that we have Applications with other objects as children of the Applications (we're a recruitment company) and we needed to calculate how many Applications had Interviews, Submitals, Deals etc...

I decided to write a relatively simple batch class that gathered the sum of these from yesterday (as Applications could come in late in the evening and we didn't want to miss those!) and stored them in an object that held the figure (plus a few other bits of 'metadata'). That way, rather than querying millions of records, we'd be querying 360 for a years worth of Applications.

Hopefully, this makes sense and you can translate it to your scenario and gives you at least a little bit of an idea as to solve this as I know it's nightmarish!

Although this may not be the best answer in the world, I had a similar problem with having to report on 4,000,000+ records.

What I did was to create a custom object that stored calculated records obtained by a batch class.

The specific scenario for us what that we have Applications with other objects as children of the Applications (we're a recruitment company) and we needed to calculate how many Applications had Interviews, Submitals, Deals etc...

I decided to write a relatively simple scheduled batch class that gathered the sum of these from yesterday (i.e. CreatedDate) (as Applications could come in late in the evening and we didn't want to miss those!) and stored them in an object that held the figure (plus a few other bits of 'metadata'). That way:

• Rather than querying millions of records, we'd be querying 360 for a years worth of Applications
• All of the leg work done by the batch class could be run out of office hours (e.g. at 1am) so Users wouldn't be affected by performance (although this didn't seem to hit us too hard anyway)

The only problem with this is that you'd need to run this batch class for each day, keep in mind governor limits dictate you can only schedule up to 100 at a time.

Hopefully, this makes sense and you can translate it to your scenario and gives you at least a little bit of an idea as to solve this as I know it's nightmarish!