The data architect for Universal Containers has written a SOQL query that will return all records from the Task object that do not have a value in the whatID field:
SELECT ID, Description, subject FROM Task WHERE WhatID != NULL
When the data architect uses the query to select values for a process, a time-out error occurs.
What does the data architect need to change to make this query more performant?
To make the query more performant, the WHERE clause should be changed to filter by a deterministic and indexed value. This approach ensures the query is selective and can utilize indexes, thereby reducing the load on the database and improving performance. Filtering by a deterministic value typically decreases the size of the result set more efficiently than checking for NULL values, which can often result in non-selective queries and subsequent time-out errors.
Answer is A. See https://help.salesforce.com/s/articleView?id=000385213&type=1 to explain how to mitigate query limitation one must add "selective criteria" into the SOQL query. For example, "The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions."
the query is already selective. can't make it more selective than that. however, we can get rid of unwanted fields, like the description.
You cannot be sure if the query is selective or not because it depends on the ORGs table scan. If you run the Query Plan on diff orgs you have different relative costs (<1 is selective).
Only A is suitable! As D suggested from thneeb is not selective, because whatId is required field, therefore, there is no sense to check WhatID != null
A. Change the WHERE clause to filter by a deterministic defined value: This is a good practice. The query should be selective, meaning one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold
this is normally a best practice, but in the context of this question, incorrect, as the query is already selective. any more selective and we don't answer the architect's question.
I think it is C, removing a long text field would make a query more performant and Description is not required for the request. Changing the filter to a deterministic value would mean it's not fetching all records where WhatId != null.
https://help.salesforce.com/s/articleView?id=sf.improving_report_performance.htm&type=5
Answer has to be C. Remember the purpose of the question. Wants ALL records that DON'T have a value in WhatID. D is wrong, because that limits the results to only 100 records, and there might be more. B is wrong, we need SOQL. A is wrong IN THIS case, because we already have a deterministic WHERE clause. We want records where WHATID is not null. C makes sense because if we want to increase performance, get rid of stuff we don't care about. The purpose of the query is to pull not null What IDs, so we don't care about the long text description.
Here are some examples of deterministic defined values that could be used to filter the query: A specific task ID The current date The ID of the current user By filtering the query to retrieve a smaller number of records, the data architect can significantly improve its performance and avoid timeout errors.
but then that wouldn't get us what the architect wants, which is all tasks that do not have a whatID value.
I think it is C. As we are looking for records that have WhatID as blank, I don't see how we can have a more "deterministic defined value" as a criteria. D option limits to 100 and therefore doesn't return everything either.
I think that A is wrong. I would use D and limit the result and reexecute the query until the NULL values are solved.
how you can query all record if you put a LIMIT clause?