function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
JBabuJBabu 

Limitation of formula field in SOQL?

Hi,

 

I am writing a SOQL query which has formula field in the WHERE clause.

Is there any limitation on number of records which are retrieved?

 

Please let me know the details.

 

Thanks,

Babu.

Bhawani SharmaBhawani Sharma
Standard limits apply here. 50K per transaction.
sfdcfoxsfdcfox
Besides the usual limit, remember that formula fields are categorized as a non-selective filter criteria. This means that your code will fail if it does not use at least one index that reduces the potential query result to less than approximately 300,000 records before filtering by the formula.
JBabuJBabu

Hi,

I would possibly get 50K records at maximum in my formula field filtered query.
Does this still require index?
If so how do I do that in SOQL query?

 

Also please let me know if there is any link to check these kind of limitations.

Thanks,
Babu.
sfdcfoxsfdcfox
Having just watched the Query Optimizer webinar on youtube, the answer lies in the formula. If the formula uses only indexed fields, then you probably do not need to worry about the selectivity of the query. If the formula is derived from cross-object relationships or dynamic data, then an index may be required.

Salesforce.com now has the ability to generate custom indexes for formula fields that meet a certain criteria. If you are concerned that the performance of the query will suffer and/or be blocked because of non-selectivity, you may be able to request a custom index.

Your application's performance will be directly related to the performance of the formula field. They cannot index cross-object formulas, either, so you will need to take that into consideration.

Ultimately, there's not enough information from your post to determine if the formula will perform well or not.
JBabuJBabu
Hi,

My formula would be
recent_mod__c = if( (now()-lastmodifieddate) <= 15*24*60, 'true','false')

I would write soql query like example:
"select id from lead where recent_mod__c ='true' "
this would return records which are modifed with in last 15 mins.

Main reason I have used this method instead of using :

List<Opportunity> Optylist = new List<Opportunity>();
Optylist = [select name from Opportunity where SystemModstamp > :Datetime.now().addMinutes(-15)];

because I want to run the query through eclipse/workbench/force.com explorer.

For the above formula, does it use index? Is it ok if the number of records fetched are more than 1000? Please let me me know..

Thanks,
Babu.
sfdcfoxsfdcfox
I would imagine that the formula could be statically verified and compiled by the query optimizer as a hardcoded value, but I suspect that it is not, because you're receiving the non-selectivity error. Try adding "ORDER BY LastModifiedDate DESC LIMIT 50,000"
JBabuJBabu
Hi,

Do you mean to use ORDER BY LastModifiedDate DESC LIMIT 50000? I want to know the records which are modified with in last 15 mins.

I tried both queries:
(1)
select name from Opportunity where SystemModstamp > Datetime.now().addMinutes(-15) ORDER BY LastModifiedDate DESC LIMIT 50000

Error Message:
MALFORMED_QUERY:
Opportunity where SystemModstamp > Datetime.now().addMinutes(-15)

ERROR at Row:1:Column:52
unexpected token: 'Datetime.now'

(2)
select name from Opportunity where SystemModstamp > :Datetime.now().addMinutes(-15) ORDER BY LastModifiedDate DESC LIMIT 50000

Error Message:
MALFORMED_QUERY:
Opportunity where SystemModstamp > :Datetime.now().addMinutes(-15)
^
ERROR at Row:1:Column:52
unexpected token: ':' (Bind variables only allowed in Apex code)
sfdcfoxsfdcfox
No, you can use your formula field in this case:

SELECT Name FROM Opportunity WHERE recent_mod__c = 'true' ORDER BY LastModifiedDate DESC LIMIT 50000

This should allow the system to use indexes (if my understanding of SOQL optimization is correct).
JBabuJBabu
Hi,

Can you please let me know why do we need to add ORDER BY LastModifiedDate DESC LIMIT 50000? As the query
SELECT Name FROM Opportunity WHERE recent_mod__c = 'true'
gives the desired result (records which are modified with in last 15 mins)
Does adding ORDER BY LastModifiedDate DESC LIMIT 50000 makes it to use index?
Doesn't adding theis ORDER BY clause makes the query run slower?

Please advice.

Thanks,
Babu.
sfdcfoxsfdcfox
There is a sorted index available for numeric and date fields. So, by using order by, it can cause that index to be used, especially when providing a limit. You might still need to provide an additional where clause to help things along (perhaps LastModifiedDate = LAST_N_DAYS:2), but I'm not certain on that, as I don't have any database where this is actually a problem.