When should I start worring about the number of Records on a single object in a SF Org?

Question

Just completing a project that has been deployed into a global SF Org. We have one object in particular that is likely to require a large number of records and is a related list on most pages.

At what point is this likely to have an impact on page performance? Since lookup and master fields are indexed, I'm assuming that perfomance is pretty much optimised by default for related lists?

The first product migrated to this app added 220K records to this particular object, and that's likely to grow at around 20%/year through on-going business. The originally targeted migration was for 6 products comprising around 2.2M records and the intention is that several 10s of products are added to the platform in the future. So the number of records could easily grow to several million records.

Is this a worry and if so does anyone have any best practices to share?

Many thanks.

Answer 1

Once you get into the millions, you will probably notice a performance hit. This is not usually an issue as much with related lists - as it is using an indexed field (parent id) to search. However, in list views, reports, and SOQL queries you will notice a difference. I would recommend making common filter fields into indexed fields. You can do this either by contact SF support or marking the field as an external ID. There are other options as well, but I find this solution applies most often and is the easiest to implement.

You can learn a lot more about using large amounts of data with salesforce by reading this: http://www.salesforce.com/us/developer/docs/ldv/salesforce_large_data_volumes_bp.pdf

It is pretty short and very interesting and informative.

Answer 2

View performance shouldn't really be affected, but you may notice hits in list views, reports, or custom logic around that data if you are using fields that are not indexed. To minimize performance impacts, you can request some custom indexes on objects with large data volumes (+1M records) if needed.

Answer 3

Once you exceed 100K records on a single object, you may need to revisit any SOQL queries to make sure they are selective.

See http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

For best performance, SOQL queries must be selective, particularly for queries inside of triggers. To avoid long execution times, non-selective SOQL queries may be terminated by the system. Developers will receive an error message when a non-selective query in a trigger executes against an object that contains more than 100,000 records. To avoid this error, ensure that the query is selective.