14

Take a look at Working with Very Large SOQL Queries. Querying for field != null will hurt your performance. Typically, a custom index isn’t used in these cases. The queried values exceed the system-defined threshold. The filter operator is a negative operator such as NOT EQUAL TO (or !=), NOT CONTAINS, and NOT STARTS WITH. The CONTAINS ...


7

Text field indexes do not index "null" values. In other words, the database can't reduce the cost for a query that filters for a null value in a text field. If most of your table has values, but a few do not, this can easily cause the query timeout exception you're receiving. Odds are, you'll probably need to resort to more drastic measures, such as perhaps ...


7

Provide hardcoded fields in query locator? I believe you can do this, this gives a compile-time check Database.getQueryLocator([SELECT Id FROM Account]); Src:https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_database_batch.htm#apex_Database_QueryLocator_getQuery


6

Child relationships can return a QueryLocator. This can catch developers off-guard, because it requires relatively large amounts of data to happen. For example: for(Account acc: [SELECT (SELECT Name FROM Contacts) FROM Account) { if(acc.Contacts.size() > 100) { // Do something ... This code will work correctly if there are less than 200 contacts, ...


5

Partially. You can always establish static references to the involved fields and sObjects, and build the query dynamically with templating. I never like seeing queries built by string concatenation anyway. List<String> fieldList = new List<String>{ String.valueOf(Account.Id), String.valueOf(Account.Name) }; String query = String.format( ...


5

There is no need to use dynamic SOQL in batch Apex (unless you have to). Because if you will do typo, and SOQL would be invalid, you will not see error message after code complying (saving to org). String query = 'SELECT Call_Duration_Required_abv__c,Id FROM Account where Call_Duration_Required_abv__c=false and IsPersonAccount=True and Id in (Select ...


4

The documentation has the following to say about iterator: To iterate over a query locator, save the iterator instance that this method returns in a variable and then use this variable to iterate over the collection. Calling iterator every time you want to perform an iteration can result in incorrect behavior because each call returns a new iterator ...


4

There is no need for a try-catch block your code, because there is nothing you can catch that's worth catching. There's governor limits that could be exceeded, which you can't catch, and the exceptions that could be thrown can be avoided with some minor modifications to the code. NullPointerException If account is null, you'll get this. Just make sure your ...


4

If you want to skip the newest 1000 records, use an OFFSET clause. Make sure you add an ORDER BY clause as well so that the newest records are those skipped. Since you can't use OFFSET in your query locator, you'll have to query down the 1000th record and use its CreatedDate in your filter: MyObject__c thresholdRecord = [ SELECT Id FROM MyObject__c ...


4

There's no "risk" with using QueryLocator as compared to using a direct assignent. Your code would use (about) same amount of CPU time, but may use less overall memory, and has a reduced chance of expired cursors. Note that using the following construct is more CPU friendly; whenever you put a query in a loop directly, you automatically get a QueryLocator: ...


4

Based on this answer: In a single transaction, you can have up to 300 parent-child subqueries. Each row returned by a parent-child subquery does count towards the 50,000 query row governor limit. More details from the official site In a SOQL query with parent-child relationship subqueries, each parent-child relationship counts as an extra query. These ...


4

Currently, I came up with some ugly but efficient solution for mocking Database.QueryLocator and to be precise, its substitution. To change the return type of AccountSelector.locatorByIds() method from Database.QueryLocator to Object: public with sharing class AccountSelector extends fflib_SObjectSelector { public override SObjectType getSObjectType() {...


3

Since Simmer '18, The SOQL Count() Function Doesn’t Count (As Much) Toward Limits so you can count way more than 50,000 records. If you are working from a Visaulforce page, setting readOnly="true" lifts the limit to 1,000,000 rows (see e.g. Setting Read-Only Mode for an Entire Page) but other limits such as the heap size still apply. But the general ...


3

Two options: Create an instance of Date, based on today, then subtract 60 days from that date. AddDays is a bit misleading, since you can add a negative number of days to the date. Date.today().addDays(-60); Use the LAST_N_DAYS date literal. This can be used to select a period of time (via =), or a period before or after the given period of days (via &...


3

@PranayJaiswal has the best answer but you can also consider the Force.com Enterprise Patterns - Selector layer that uses a queryFactory and type safe fields as in: Database.QueryLocator ql = CasesSelector.newInstance().selectByCloseDateAsQueryLocator(someDateFilter); public virtual class CasesSelector extends fflib_SObjectSelector implements ...


3

Lets expand your code: global Database.QueryLocator start(Database.BatchableContext BC) { String query = makeString(sources); return Database.getQueryLocator(query); } Becomes: global Database.QueryLocator start(Database.BatchableContext BC) { String query = makeString(sources); return Database.getQueryLocator('SELECT ...


3

Salesforce, from time to time, uses some not-so-clear wording and terminology. A perfect example is "Aggregate" queries. You might think that an "Aggregate" query is one that uses Aggregate functions like COUNT() or MIN() along with GROUP BY. That's completely logical (heck, you even get an AggregateResult[] from a query that uses Aggregate functions), but ...


3

tl;dr: yes. There's a few peculiarities when sorting by Created Date, partly due to the fact that the system assigns Id values and time stamps in a way that may cause them to be out of order when queried back, and because the audit fields are truncated to the nearest second. However, if you want to ignore those idiosyncrasies and just go with a "good enough"...


3

String.join(...) expects an object which inherits the iterable interface as a first parameter, generally a list. Set does not implement the iterable interface, and as a result, you get this error message. If you convert it over to a list before you call the string method, you'll be able to use your code as is. String s = String.join(new List<String>...


3

After a long support journey, it has been found out that there is internal salesforce code that get executed under specific conditions (depending on organization configuration), that can open a QueryLocator during the CampaignMember update. A bug was logged by the R&D team an they are working on them right now. Nevertheless we have implemented a ...


2

You can't use any sort of dot reference in a dynamic bind variable. So anywhere you are calling .keySet(), you would need to cache that Set to be referenced directly. Invalid String query = '...WHERE Id IN CHildParentMap.keyset() OR Id IN :ParentWithdrawaldateMap.keyset()' Valid Set<Id> ids = new Set<Id>(); ids.addAll(childParentMap.keySet()); ...


2

As you are running a new batch. In batch Processing every execute get a new set of limits and it counts a different transaction. So all three will be counted as 3 transaction. Also one execute method can process 2000 (max records from query locator) in single transaction. So they also break in chunk of 200(default size). They each will be counted as ...


2

You're mixing Apex bind variable syntax with string concatenation. You need to change this line: 'Product_Practice__c NOT IN :' + practiceFilter + ' AND ' + to 'Product_Practice__c NOT IN :practiceFilter AND ' + or use something like NOT IN (\'' + String.join(practiceFilter, '\', \'') + '\') AND ' to construct the list of Ids as a ...


2

The reason for the error itself is that if you use string coercion to add a collection to the query it outputs as {...}. In other words: system.debug('' + new Set<String>{'A', 'B'}); // yields {A, B} If you want to include a set in your query, simply merge in the variable: String query = '...Zip_vod__c IN :zipSet'; If you want to use literal values,...


2

When you use Apex binding in Dynamic SOQL, the bound variables must be in scope at the point when Database.query() is called, rather than at the point when the query string is initialized. Here, you'd probably want to create an instance variable (sourceKeys?), and bind to that. Doing so ensures that the bind's scope covers all of your instance methods.


2

You cannot compare two different field values in your SOQL 'where' clause. E.g. the below is not allowed. 'Quantity_Ordered__c >= Quantity_Available__c' What you can do to achieve your need is create a formula field to compare this two custom field, and use the custom field in your SOQL where clause.


2

That's just what Developer Console shows when you run a relationship query and the relationship on a given row is null. In this situation, ConvertedAccountId is null. You can't, and shouldn't, try to filter on the (null XXXXX) value, because it's a UI artifact of the Developer Console.


1

One honorable mention from me is SOQL For Loop that uses List instead of standard sObject. for(List<Account> accounts : [SELECT ID FROM Account]){ for(Account acc : accounts){ } System.debug(count); } Quite useful if you wanna iterate over the large list but worried about heap. SOQL For Loop use can be bit tricky, the ...


1

I have been struggling with the same, here is what I was told: The QueryLocator approach is not supported for any Virtual Entities that don't return a total size count. This is because BatchApex needs to know the total number of records to create the correct number of jobs. So you should use Iterator instead (with all its limits).


1

Using trigger or execute anonymous window Datetime d = datetime.now().addmonth(1) List <account> a = list <account>(); a = [select id,name from account where created date >: d limit1000]; Delete a;


Only top voted, non community-wiki answers of a minimum length are eligible