Salesforce Selective Queries

A basic understanding of the selective query concept is fundamental to scalability on the Salesforce platform. Non-selective queries have a significant performance impact on List Views, Reports and SOQL and can often result in Apex Trigger runtime exceptions as below – as the data volume increases over time.

System.QueryException Cause: null Message: Non-selective query against large object type (more than 200000 rows)

SOQL queries executed from Apex Triggers have to be selective where the record count in the queried object is 200,000 or more. The determination of a selective query is a function of volume plus field-level selectivity. It is therefore the case that the selectivity state of a given query is volatile meaning in practical terms that the initiating Apex Trigger may work one day and not the next.

Selective Query Definition

Selectivity is determined by the index state of the query filter conditions and the number of records the filter returns (selectivity) versus the object total records. The thresholds below show the difference between the selectivity calculation for a standard index versus a custom index.

Selectivity Thresholds:
Standard Index – 30% (first 1M) then 15%
Custom Index – 10% (first 1M) then 5%

Unary filter:

e.g. select Name from Account where IndexedField__c=’ABC’

With a custom index on IndexedField__c the filter must return <10% of the total records in the object to be considered selective – up to the first 1 million records from that point the threshold drops to 5%.

Multiple filters AND (exclusively):

e.g. select Name from Account where IndexedField__c=’ABC’ and SecondIndexedField__c=’123′

The Query Optimiser will set the leading operation on the basis of lowest cost. If no filters are selective a table scan is performed.

If all filters have an index then a Composite Index Join optimisation can be applied.

In this case each filter must be less than 2x (two-times) the selectivity threshold.
All filters combined must be less than selectivity threshold.

If all filter fields are standard then use the standard index selectivity threshold – otherwise use custom index selectivity threshold.

Multiple filters OR (at least one):

e.g. select Name from Account where IndexedField__c=’ABC’ or SecondIndexedField__c=’123′

Selective AND filter indexes could be set as the Leading Operation – if none exist, then a table scan occurs unless all filters have an index then a Composite Index Union optimisation becomes possible.

In this case each filter must be less than selectivity threshold.
All filters combined must be less than selectivity threshold.

If all fields are standard then use the standard index selectivity threshold – otherwise use custom index selectivity threshold.

Parent Field Filter:

e.g. select Name from Contact where IndexedField__c=’ABC’ and Account.IndexedField__c=’ABC’

Where parent object fields are referenced in a filter, each filter index is individually and the lowest cost option selected as the leading operation.

Note, the parent field is not indexed on the queried object, so Account.Id can incur a table scan on Opportunity whereas AccountId may allow the standard index to become the leading operation.

The notes above provide a basic outline of the concepts but should be sufficient to convey the key concepts.

 

Implementation Approach

As data volumes grow query behaviour can change dramatically, to mitigate this database queries originating in Apex code, list view and report definitions must consider the future peak data volume and field-level data characteristics (primarily selectivity). This considered approach can help identify an appropriate indexing strategy that maintains query performance by ensuring query selectivity. So, forward planning is absolutely key; queries should be designed to be selective up to the projected peak data volumes. Thinking ahead to this extent is very seldom applied in my experience, particularly where the Salesforce implementation evolves project-by-project and the longer term picture is not a priority.

In order to evaluate the selectivity of a given query – the following 2 approaches can be applied.

REST API Query Resource Feedback Parameter

The Force.com REST API exposes a Query resource that accepts an explain parameter which can set with a SOQL query, List View Id or Report Id. The results show the options considered by the Query Optimiser and the lowest cost option (leading operation) taken. A relative cost value of less than 1 indicates a selective query, anything higher indicates non-selective. The example below shows the output for a report where a report filter hits an indexed field.

selective-queries-api

Developer Console Query Plan

From Summer ’14 on the Developer Console can be enabled [Help>Preferences>Enable Query Plan] to display a Query Plan option on the Query Editor tab. The construct of the output is the same as the API approach. Note, this appproach is limited to SOQL queries.

selective-queries-developer-console

The references section below provides link to the technical detail of the 2 approaches introduced above.

 

References

Query & Search Optimisation Cheat Sheet

Developing Selective Force.com Queries through the Query Resource Feedback Parameter Beta

Developer Console Query Editor

Improve performance with Custom indexes using Selective SOQL Queries

Custom Index Request Checklist

Apex Unit Test Best Practice

This post provides some general best practices in regard to Apex Unit Tests. This isn’t a definitive list by any means, as such I’ll update the content over time.

Top 10 Best Practices (in no order)

1. TDD. Follow Test Driven Development practice wherever possible. There is no excuse for writing unit tests after the functional code, such an approach is indicative of a flawed development process or lax standards. It’s never a good idea to estimate or deliver functional code without unit tests – the client won’t appreciate an unexpected phase of work at the point of deployment, not to mention the pressure this approach puts on system testing.

2. Code Quality. Ensure unit tests are written to cover as many logical test cases as possible, code coverage is a welcome by-product but should always be a secondary concern. Developers who view unit tests as a necessary evil, or worse, need to be educated in the value of unit tests (code quality, regression testing, early identification of logical errors etc. etc.).

3. Test Code Structure. For some time now I’ve adopted a Test Suite, Test Helper pattern. A suite class groups tests related to a functional area. A test helper class creates test data for a primary object such as Account (i.e. AccountTestHelper.cls), secondary objects such as price book entry would be created within the product test helper class. The suite concept provides a logical and predictable structure, the helper concept emphasises that test data creation should be centralised.

4. Test Code Structure. Put bulk tests in a separate class e.g. AccountTriggerBulkTestSuite.cls (in addition to AccountTriggerTestSuite.cls). Bulk tests can take a long time to complete – this can be really frustrating when debugging test failures – particularly in production.

5. Test Code Structure. Ensure test classes contain a limited number of test methods. I tend to limit this to 10. As with point 4, this relates to test execution time, individual methods can’t be selectively executed – the smallest unit of execution is the class.

6. SeeAllData. Always use SeeAllData=true by exception and at the test method level only. Legacy test code related to pricebooks that historically required this can now be refactored to use Test.getStandardPricebookId(). Also, set the [Independent Auto-Number Sequence] flag to avoid gaps in auto number sequences through the creation of transient test data.

7. Test Case Types. As the Apex Language reference proposes, write unit tests for the following test case types.

Positive Behaviour – logical tests that ensure the code behaves as expected and provides successful positive outcomes
Negative Behaviour – logical tests for code behaviour where parameters are missing, or records do not adhere to defined criteria – does the code protect the integrity of unaffected records – does the runtime exception handling function as expected
Bulk – trigger related tests primarily – how the code behaves with a batch of 200 records – mix the batch composition to stress the code against governor limits
Restricted User – test relevant combinations of user role and profile – this test case type is prone to failure through sharing model adjustments – triggers should delegate processing to handler classes that have the “with sharing” modifier

8. Debugging. Always use the syntax below for debug statements within code (test and non-test code). An efficient practice is to add sensible outputs whilst writing the code. This approach avoids a code update or re-deployment to add debug statements during error diagnostics. Note – in such cases Checkpoints could be a better approach anyway – particularly in production. The use of the ERROR logging level enables a restrictive log filter to be applied such a clear debug log is produced and max log size truncation is avoided – note, log filters can also have a positive impact on transaction execution time.

System.debug(LoggingLevel.ERROR, 'my message');

9. Commenting. Always comment test methods verbosely to ensure the test case intent is clear and that the test code can be mapped to the related non-test code. Test classes should be fully self documenting and be viewed as the primary enabler for the future maintenance of the non-test code.

10. Maintenance. Test code is highly dependent on the environment state. Any configuration change can require test code to be updated; this could be a new mandatory custom field or a sharing model adjustment. In many cases the resultant unit test failure state is not encountered until the next deployment to production, which can’t proceed until the tests are fixed. This scenario will be familiar to many people. The mitigation requires the local administrator to understand the risk, frequently run the full set of unit tests and to manage the test code update cycle proactively.

Example Test Suite Class

/*
Name: RecordMergeTestSuite.cls
Copyright © 2014  CloudMethods
======================================================
======================================================
Purpose:
-------
Test suite covering RecordMerge operations.
Bulk tests are defined in the class RecordMergeBulkTestSuite.cls
======================================================
======================================================
History
------- 
Ver. Author        Date        Detail
1.0  Mark Cane&    2014-09-16  Initial development.
*/
@isTest(SeeAllData=false)
public with sharing class RecordMergeTestSuite {
	/*
     Test cases:	
        singleTestCase1 - postive code behaviour/expected outcome test case 1.
        negativeTestCase1 - negative outcome test case 1.
        restrictedUserTestCase1 - postive/negative code behaviour in the context of specific user role/profile combinations.
        ..
        future test cases to cover : * some coverage provided
        1. tbd.
        2. tbd.
    */
    
    /* */
	static testMethod void singleTestCase1() {
		// Test case 1 : postive outcome test case 1.
        setup();

		// Steps - 1. 
		// Logical tests - 1.
    }
    /* */    

    /* */
	static testMethod void negativeTestCase1() {
		// Negative test case 1 : negative outcome test case 1.
        setup();

		// Steps - 1.
		// Logical tests - 1. 
    }
    /* */    

    /* */
	static testMethod void restrictedUserTestCase1() {
		// Restricted user test case 1 : postive/negative code behaviour in the context of specific user role/profile combinations.		    	    			
		List<User> users;
		
		System.runAs(new User(Id = Userinfo.getUserId())){ // Avoids MIXED_DML_OPERATION error (when test executes in the Salesforce UI).
			setup();		    					
			users = UserTestHelper.createStandardUsers(2, 'Sophie', 'Grigson');
		}
		
		System.runAs(users[0]){
			accounts = AccountTestHelper.createAccounts(1, 'Abc Incorporated');
			
			// Steps - 1. 
			// Logical tests - 1.
		}		
    }
    /* */
	
	// helper methods    
    private static void setup(){
   		SettingsTestHelper.setup();    	
    }
    // end helper methods
}

Salesforce Summer 13 – Checkpoints

My first post on this blog back in March 2012 related to Simulated Breakpoints, a developer console feature enabling a head dump to be captured when code execution hit a specified line(s) of Apex script. Whilst not comparable to the power of breakpoints in debugging with other languages, Simulated Breakpoints was a definitely step forward for Force.com development, but I suspect this still remains an unused feature, with System.debug() statements being used instead. I don’t believe too many developers are juggling the Force.com IDE and Developer Console, which is unfortunate as the latter provides features and metrics not supported by the IDE.

Checkpoints.
In Summer ’13 Simulated Breakpoints are now termed Checkpoints and can be set on lines of Apex script in the same way using the code editor (now with syntax highlighting) within the Developer Console. In addition to capturing a heap dump, Apex script or SOQL query Execution Actions can be added which run when code execution hits the Checkpoint. Very useful in determining state of the execution context and in particularly in debugging data related issues.

In the screenshot below we can see the new Developer Console UI for Summer 13, the Checkpoints tab and the definition of an example SOQL query Execution Action which will run when the Checkpoint is hit.

3.Checkpoint create

In the screenshot below we can see the result, when the Heap Dump log statement is double-clicked a Checkpoint tab is revealed with subtabs that show the Heap Dump itself, plus the result of defined Execution Actions. Note, I was unable to open the Checkpoint results where an Apex Execution Action was defined, from the log activity it does appear to run as an Execute Anonymous block, but it isn’t clear if the user context is the debugging user or the running user.

4.Checkpoint result

Simulated Breakpoints

The first of a series of posts relating to new advancements in the Apex language of particular relevance to technical architects.

From a debugging perspective the Apex language lags behind its modern language counterparts. Standard features such as breakpoints (conditional or otherwise) and edit-and-continue are lacking, due to the challenges of pausing runtime execution in a multi-tenanted environment. The typical debug workflow has therefore involved the use of copious amounts of..

System.debug('MyVar value is:'+myVar);

.. statements and plenty of patience. In a development or QA org this is inefficient at best but workable. In a production setting however, deploying instrumented code to assist in diagnosing a runtime issue becomes incredibly time expensive – remember unit tests have to run. With Spring ’12 however the enhanced Developer Console, nicely renamed from the old System Log title, provides a far more efficient approach – Simulated Breakpoints!

In short, a breakpoint can be set on any line of Apex script using the familiar technique of clicking in the sidebar next to the required script line to reveal a red dot indicator. This can be done for all Apex code exposed via the Repository tab in the Developer Console. Subsequent debug logs will capture a snapshot of the heap during runtime execution when the breakpoint is encountered. The snapshots can be found in the Heap Dumps tab within the Developer Console. Not quite edit-and-continue but a marked improvement nonetheless.

This understated capability is a real advancement in debugging Apex script on the Force.com platform. Throw in the other new capabilities such as unbounded raw log access and Visualforce markup editing and it’s definitely time to look again at the Developer Console if you’re doing serious Apex coding.