For the best optimization, a Parent object should not have more than 100000 records of its child because it will slow down the process and will prevent you from deleting child records as per the official text:
Can’t I Delete a Certain Custom Object, if contains more than 100,000
records. If the object you want to delete has more than 100,000
records, first delete an appropriate number of records and then delete
You will also get the Selective Query error when you will try to query parent and child records together. You will run into the issue as below:-
System.QueryException: Non-selective query against large object type
(more than 100000 rows). Consider an indexed filter or contact
salesforce.com about custom indexing. Even if a field is indexed a
filter might still not be selective when:
- The filter value includes null (for instance binding with a list that contains null)
- Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that
occurs many times)
Regarding the tier-tuning Salesforce only consider this:-
The selectivity threshold is 10% of the first million records and less than 5% of the records after the first million records, up to a
maximum of 333,333 records. In some circumstances, for example with a
query filter that is an indexed standard field, the threshold can be
higher. Also, the selectivity threshold is subject to change.
Page load is affected with above things plus:-
- No, records you are showing per related list
- No of related list you have
- No. of columns you have (My guess is no. of formula type columns should also affect)
Per Parent records we should not have more than 10,000 records.
As suggested by @sfdcfox:
- You should always reference the document"Considerations for Relationships."
- 200 child junction objects with a rollup summary to the other parent, 10,000 details in a master-detail relationship.