Large Data Volume Considerations practice questions

Implement a retry mechanism using a custom setting.

Why it fails: A retry mechanism is a reactive workaround rather than an architectural solution. While it might mask the issue, it does not fix the root cause of lock contention. The process will still experience performance degradation and potential failures, and it does not improve the system's throughput efficiency.

Change the batch size to 1.

Why it fails: Processing records one by one with a batch size of one will significantly increase the total processing time, potentially causing the overall job to exceed the maximum execution time. While it reduces contention, it is an extremely inefficient way to handle large volumes of data in Salesforce.

Disable all sharing rules on the object.

Why it fails: Sharing rules are calculated during record updates to maintain data access integrity. Disabling them is generally not possible in production and would violate security requirements. Even if disabled, row locking is a database-level mechanism designed to maintain data integrity, which would still apply to object updates.

Using a filter on a custom indexed field.

Why it fails: Using a filter on an indexed field allows the query engine to pinpoint the necessary records without scanning the entire database. This is the recommended approach for maintaining query performance on large data volumes, as it significantly reduces the number of rows evaluated by the database engine.

The query returns fewer than 100,000 records.

Why it fails: Returning a small subset of records is generally highly selective. The performance bottleneck typically occurs when the filter criteria match a large percentage of total records, forcing the query engine to evaluate a significant portion of the table, regardless of the absolute number of records returned.

The object has fewer than 100,000 records total.

Why it fails: Selectivity is less critical on very small objects, as a full table scan on a small dataset is virtually instantaneous. Performance issues related to non-selective queries only become problematic when the object size grows to millions of records, where scanning becomes a significant resource-intensive task for Salesforce.

The total number of fields on the object.

Why it fails: The total number of fields has a negligible impact on query performance compared to indexing and data volume. While very wide tables can have slight impacts on storage, they are not the primary driver of performance degradation when querying or updating records in a large dataset.

The color scheme of the Lightning app.

Why it fails: UI configuration, such as themes and colors, has absolutely no impact on backend database performance or SOQL query execution speed. These settings are strictly client-side and do not interact with the data storage or retrieval mechanisms that manage large data volume operations in the Salesforce platform.

Move records to a custom Archive object within Salesforce.

Why it fails: Moving records to another object inside Salesforce does not solve the underlying problem of large data volumes. The platform will still manage these records, consuming storage and potentially causing performance issues if the archive object also becomes too large, thus failing to achieve the benefits of off-platform archival.

Change the record ownership to a generic user.

Why it fails: Changing record ownership does not change the record count or the data volume. The database still stores the records and must process them during queries or DML operations, meaning this action provides no benefit for performance improvement or large data volume management in the Salesforce platform.

Use Salesforce Data Masking to hide the records.

Why it fails: Data Masking is used to protect sensitive data in non-production environments. It does not reduce the actual volume of data stored in production, nor does it improve the performance of queries or DML operations, making it completely ineffective for managing large data volume challenges in a production system.

Increase the batch size in the Data Loader settings to the maximum of 10,000.

Why it fails: Increasing batch sizes beyond the optimal range often triggers governor limit exceptions or lock contention issues. Excessive batch sizes can lead to row-level locking, which degrades performance across the entire organization during heavy update operations, making it a dangerous strategy for large-volume environments.

Implement a custom indexing strategy on all fields used in filter criteria.

Why it fails: While indexing is vital for query performance, it does not alleviate the processing overhead caused by Apex triggers or flow logic during DML operations. Adding too many indexes can actually slow down insert and update speeds due to the overhead of maintaining index tables during transactional commits.

Disable all Validation Rules and Apex Triggers before the update process.

Why it fails: Disabling critical validation logic compromises data integrity and business rules. While it may temporarily increase throughput, it creates significant risks of corrupt data entering the system, requiring extensive cleanup later. This strategy should only be used as a last resort in strictly controlled data migration scenarios.

Keep all Triggers and Flows active to ensure data quality.

Why it fails: Active automation logic adds significant overhead to every insert operation. For large migrations, this leads to excessive CPU time and potential governor limit hits. Best practice involves disabling non-essential automation during the load to ensure maximum performance and later enabling them for ongoing data validation.

Perform the load during peak business hours for real-time monitoring.

Why it fails: Loading large volumes during peak hours introduces competition for resources with end-users and other system processes. This increases the risk of locking errors, latency, and performance degradation for both the migration process and the user community. It should always be done during off-peak maintenance windows.

Insert all records in a single batch to minimize transaction count.

Why it fails: Attempting to load too many records in a single transaction will exceed the platform's execution and heap limits. Large volumes must be broken down into smaller batches to comply with Salesforce's governor limits and to ensure that the transaction can be processed within the allowed time.

Skinny tables are maintained automatically by Salesforce.

Why it fails: Skinny tables are not automatically updated. Any time the base object is modified, the skinny table must also be updated. This requires careful management to ensure that the data within the skinny table remains synchronized with the primary object, otherwise reports will provide inaccurate, outdated information.

They are ideal for frequently updated, transactional objects.

Why it fails: Skinny tables are best for read-heavy operations. Because they incur a performance penalty on every write operation to the base object, they are not suitable for objects that are updated constantly, as the synchronization overhead will negate any performance gains achieved in the query phase.

The query is selecting too many fields.

Why it fails: The query only selects two fields, 'Id' and 'Name'. This is a very light payload. Field selection typically impacts heap size or bandwidth, not query execution speed, unless you are selecting massive text fields or long blobs, which is not the case here.

The query lacks a ORDER BY clause.

Why it fails: Adding an ORDER BY clause would force the database to perform an additional sorting operation, which would likely decrease performance rather than improve it. The lack of an ORDER BY clause is irrelevant to the initial execution speed of the data retrieval.

The query is missing a LIMIT clause.

Why it fails: While a LIMIT clause is good practice for UI-bound queries to prevent exceeding governor limits, it does not improve the underlying database scan speed. The database engine must still search through the records to find those that match the criteria before applying the limit.

Increase the limit in the Organization-Wide settings.

Why it fails: Salesforce governor limits are hard-coded and cannot be modified by administrators. These limits are in place to maintain the stability of the multi-tenant environment, and any code that attempts to exceed them must be redesigned to work within the existing resource constraints, not by changing settings.

Wrap the query in a try-catch block to ignore the error.

Why it fails: Catching an exception does not remove the fact that the query exceeded the limit; it only handles the error. The transaction will still fail, and the data will not be processed. Architectural problems require structural solutions, not just error handling code which leaves the underlying issue unresolved.

Disable the governor limit for the specific user.

Why it fails: Governor limits apply to all users and are fundamental to the Salesforce multi-tenant architecture. They cannot be disabled for any user or profile. Every piece of code must be designed to execute within these limits to ensure fairness and reliability for all tenants on the instance.

Use the REST API to send records one by one.

Why it fails: Sending records one by one is extremely inefficient and will consume massive amounts of API calls, likely hitting usage limits. It also creates a high-latency process that is prone to failures and timeouts, making it an inappropriate strategy for handling large-volume data spikes in an enterprise environment.

Directly update the Salesforce database using a JDBC driver.

Why it fails: Salesforce does not allow direct JDBC or SQL access to the underlying database. Attempting to bypass the platform's APIs is not only unsupported but technically impossible. All data integration must occur through the exposed Salesforce APIs to ensure compliance, security, and data integrity.

Trigger a custom Apex class to perform a callout to the ERP.

Why it fails: Performing a callout for every record or even in bulk from within Salesforce is limited by the number of concurrent callouts allowed. For 1 million records, this approach would hit every possible limit, including execution time and callout concurrency, leading to a failed integration process.

Exceeding the governor limit for total records retrieved in a single transaction.

Why it fails: While governor limits are a concern, this specific error indicates a logic flaw in code structure, not a limit violation. The error specifically refers to assigning a list to an SObject variable, which happens regardless of the total record count, provided the query returns more than one result record.

Implementing heavy Apex logic inside the loop processing the data.

Why it fails: Heavy logic inside loops leads to CPU time limit exceptions, not assignment errors. The error described is a specific data-handling issue in Apex related to query result mapping. While performance is impacted by logic, it does not cause the system to attempt to assign a list to an SObject.

Using a standard report to update records in the database.

Why it fails: Standard reports are read-only tools and cannot directly trigger Apex exceptions regarding SObject assignment. This error originates from code, not from report generation or standard UI functions. The problem is fundamentally about how SOQL results are handled within the application layer during high-volume data processing.

Increase the record limit in the report settings to show all records.

Why it fails: Increasing the limit forces the system to process and display more data, which worsens performance. In a high-volume scenario, the goal is to filter data down to the smallest possible set. Requesting to see all records increases memory overhead and query time, potentially leading to report timeouts.

Change the report format to a matrix report with grouping.

Why it fails: Changing the report format does not address the underlying query performance bottleneck. While matrix reports are useful for analysis, they still require the underlying query to be performant. If the base query is not selective, changing the layout provides no benefit to the speed of data retrieval.

Use custom formula fields for all filtering criteria in reports.

Why it fails: Formula fields are often not indexed by default and can introduce complex calculations that must be evaluated for every record during the query. This significantly slows down report execution on large datasets compared to filtering directly on standard or custom indexed fields within the source object.

Increase the number of concurrent batches to maximize throughput.

Why it fails: Increasing concurrent batches is exactly the wrong approach for large data volumes if locking errors are occurring. Higher concurrency directly increases the probability of multiple threads hitting the same records simultaneously, which triggers row-locking contention and leads to failed batches rather than improved performance.

Enable all workflow rules and triggers to ensure data quality.

Why it fails: Enabling all automation during a large data load creates unnecessary overhead and increases the probability of locking errors. Triggers and workflows often perform additional DML or lookups that hold locks longer, making them the primary cause of contention during high-volume operations. They should be disabled during loads.

It is optimized for real-time, low-latency transaction processing.

Why it fails: The Bulk API is inherently asynchronous and involves significant overhead, making it unsuitable for real-time, low-latency requirements. For real-time updates, the standard SOAP or REST APIs are used. The Bulk API is specifically engineered for high-volume jobs that can tolerate some delay in completion.

It automatically bypasses all Apex triggers and validation rules.

Why it fails: The Bulk API does not automatically bypass triggers or validation rules. These must still be managed or disabled if necessary during large loads. Failing to account for this will result in the same performance bottlenecks and errors seen in standard API operations during large data processing jobs.

It is limited to 1,000 records per batch globally.

Why it fails: Bulk API batch sizes are configurable up to 10,000 records. A 1,000-record limit is a historical figure and does not represent the current capabilities of the Bulk API 2.0, which is more flexible and efficient at processing larger data chunks to maximize throughput during large volume operations.

Convert the Carrier__c master-detail relationship to a lookup and make the field optional.

Why it fails: Changing a master-detail to a lookup removes the parent's roll-up and ownership inheritance but does not eliminate the parent-row lock acquired when updating children. The lock is still taken on the referenced Carrier__c record during save, so the contention would persist while adding data-model complexity and losing cascade delete behavior.

Enable parallel mode in the Bulk API 2.0 job and increase the batch size to 10,000.

Why it fails: Parallel mode splits work across more concurrent batches, which increases the number of simultaneous transactions touching shared Carrier__c parents. That worsens lock contention rather than reducing it. Increasing batch size also lengthens each transaction, holding row locks longer and making UNABLE_TO_LOCK_ROW more likely on parent records.

Create a formula field on Shipment__c that derives the carrier name and use it in the nightly load instead of the lookup.

Why it fails: A formula field cannot replace a lookup relationship for data integrity or external ID upsert matching. It also would not prevent the underlying parent record from being locked during the upsert. This option solves neither the lock contention nor the query performance issue, and it introduces redundant data that cannot be indexed for selective filtering.

The custom index was not activated because the object has more than 10 million records.

Why it fails: Salesforce does not impose a record-count limit for creating custom indexes. Indexes can be created on objects with millions of records, though index creation may take time. The performance issue is due to selectivity, not an activation threshold. Therefore, this is incorrect.

Custom indexes cannot be created on picklist fields.

Why it fails: Salesforce allows custom indexes on picklist fields, provided the field is not already indexed by default. Picklist fields are not automatically indexed, so a custom index is permissible. The issue is not the field type but the selectivity of the filter. Therefore, this is not the reason performance remained poor.

The index was created on the wrong field; it should have been on Invoice_Date__c instead.

Why it fails: While Invoice_Date__c might be more selective, the question states that the architect created an index on Status__c, and performance remained poor. The problem is that Status__c is not selective enough. Creating an index on Invoice_Date__c could help, but it does not explain why the Status__c index failed.

Custom indexes are not supported on date fields; only standard indexes can be used.

Why it fails: Custom indexes can be created on date fields. Salesforce allows custom indexes on many field types, including date, number, and text. The limitation is not the field type but the selectivity of the filter. Therefore, this is not the reason the index did not improve performance.

The index on Transaction_Date__c is not being used because the filter on Status__c is not indexed.

Why it fails: The query optimizer can use an index on one field even if other filter fields are not indexed. However, the optimizer may choose not to use the index if the overall filter is not selective enough. The lack of an index on Status__c is not the primary reason; the selectivity of the combined filter is more critical.

The query returns more than 30% of the records, making the filter non-selective despite the index.

Why it fails: The query returns 500,000 records out of 20 million, which is 2.5% of the total. This is well below the 10% threshold for large objects, so the filter is selective. The issue is not the percentage of total records returned, but rather the absolute number of records and the selectivity threshold based on the first million records.

Increase the number of records displayed in list views.

Why it fails: Increasing the number of records displayed in list views does not improve query performance; it may worsen it by retrieving more data. The underlying issue is the lack of indexes on filtered fields, not the list view configuration.

Enable skinny table for Invoice__c.

Why it fails: Skinny tables can improve performance for large objects, but they are primarily used for read-only operations and require Salesforce support to create. They are not the first step for indexing missing fields, and they may not include all necessary fields for filtering, making them less effective here.

Create a formula field that concatenates Invoice_Date__c and Status__c.

Why it fails: Formula fields are not indexed and cannot be used to improve query performance. They are calculated at runtime and do not help the query optimizer filter records efficiently, so this approach would not solve the slow filtering problem.

Reduce the batch size and sort the records by parent ID to group updates on the same parent.

Why it fails: Reducing batch size can help, but sorting by parent ID would group records that share the same parent, which increases contention on the parent record. Lock contention occurs when multiple transactions attempt to update the same parent record. Grouping updates on the same parent would make the locking problem worse, not better.

Increase the batch size to process all records in a single transaction to avoid multiple lock requests.

Why it fails: Increasing batch size would prolong the transaction and hold locks longer, increasing the chance of contention with other transactions. A single large transaction is more likely to hit governor limits and lock timeouts. The goal is to reduce the duration and scope of locks, not extend them.

Disable record locking on the Account object by enabling the 'Bypass Locking' setting.

Why it fails: Salesforce does not provide a 'Bypass Locking' setting for standard objects. Record locking is enforced by the platform to maintain data integrity. Attempting to disable locking is not possible and would not be a valid solution for the integration's lock contention.

Enable field history tracking on the unique identifier field.

Why it fails: Field history tracking is used to audit changes and does not improve query performance. It adds overhead by storing historical data, but it does not index the field for faster retrieval, so it does not address the need for efficient lookups.

Create a formula field that returns the unique identifier.

Why it fails: Formula fields are not indexed and cannot be used for efficient querying. They are calculated at runtime and do not store data, so filtering on a formula field would still require a full table scan, making it unsuitable for large data volumes.

Use a text field with a unique constraint.

Why it fails: A unique constraint ensures data integrity but does not automatically create an index in Salesforce. Without an index, queries filtering on this field would perform a full table scan, which is inefficient for 30 million records.