SOQL Query Optimization - Causes/fixes of Non-Selective Queries

As we all begin to work with larger and larger datasets, we're bound to run into issues with non-seletive SOQL queries. Let's start out with a definition so we're all on a same page.

Non-selective queries are SOQL queries (typically against) tables with more than 100k rows that bring back 100k rows. i.e. you have not specified speficially what you are looking for so a full table scan is happening and if it were to proceed too long would cause locking.

Sample error: System.QueryException: Non-selective query against large object type (more than 100000 rows).


This usually happens in the context of a trigger because Salesforce is careful to make sure that transactions and therefore locks do not last very long. Note very specifically that it occurs in a trigger. Many times if you run that same query from an Apex Class called outside the context of a trigger or through Execute Anonymous you will not see the error. - Terry Luschen


At the heart of every SOQL query is the underlying database. Every relational database managment system (RDMS) has what is a called a query optimizer at its core. Before a query hits the database it parses the query, looks at what you are asking for and determines the fastest way to get it.


Query optimization is a function of many relational database management systems. The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans. - Wikipedia


So what are some of the root causes of a non-selective SOQL query?

  • Too many records - again, when you get a table above 100k rows and start asking for records that meet the condition of all 100k rows you're going to hit this issue. For example, let's say all your Contacts were imported on Aug 1, 2011 when you migrated to Salesforce. If you run a query that says: 
         SELECT id FROM Contact WHERE CreatedDate  > 2010-01-01T00:00:00Z

  • Using the good ol' Wildcard - a LIKE condition that has a leading "%" WILL NOT USE an index
         SELECT id FROM Account WHERE Name LIKE ‘%Acme%’ //this would be better suited for a SOSL query

         WHen you build a report or use a list view where you add a Contains clause that is the same

         as doing what we have adove - it essentially translates to %Value%.

  • Using NOT and != in your query - When you filter on these two inequality filters the query optimizer cannot take advantage of the index to make the query faster. By doing the opposite and filters on "=" or using an IN ('a','b','c').
  • Complex Join statements - when you start to build in complex AND/OR logic into the queries and adding sub-queires - this forces the query optimizer to produce and optimized query for the join. In reality it may be a lot more efficient to break these into multiple queries. I have found this to be particularly true when I see a lot of OR logic in the queries I look at.

If Salesforce is going to use an index when an OR condition is involved - ALL the fields you have listed in the OR condition must be indexed fields.


Salesforce Spring '16 - System.Test setCreatedDate

Really excited to see the new method added to System.Test that allows you to set the created date/time of a record. This is super helpful when inserting test data and you want some of it to appear to be older.

//Sets CreatedDate for a test-context sObject.

setCreatedDate(recordId, createdDatetime)


Debugging Apex Made Easier in Winter '16

With the release of Winter '16, Salesforce has taken a big step forward in making it easier to debug Apex code. One of the hard things we have all had to deal with is that debugging Apex is very much a "print line" exercise up to now. 

The Apex Debugger in Eclipse is now being released for general availability. It behaves very similar to debuggers from other languages for those of us who started out in Java, C++, C# etc. I've used it in my sandbox and I am able to set a breakpoint, start a debugging session and determine root causes.

Features & Uses


  • Set breakpoints in Apex classes and triggers
  • View variables, including sObject types, collections, and Apex System types.
  • View the call stack, including triggers activated by Apex Data Manipulation Language (DML), method-to-method calls, and variables
  • Interact with global classes, exceptions, and triggers from your installed managed packages. When you inspect objects that have managed types that aren’t visible to you, only global variables are displayed in the variable inspection pane.
  • Complete standard debugging actions, including step into, over, and out, and run to breakpoint
  • Output your results to the Console window




Updates regarding the auto-activation in the behavior of getContent() and getContentAsPDF()

A few people have reached out to me regarding this. Back in Summer '15 there was a change to the behavior of both getContent() and getContentAsPDF(). At the time, we were told these updates would be auto-applied and activated in Winter '16. Salesforce has decided to push this auto-activation to Spring '16. See page 394 of the Winter '16 release notes.

The nice part of the postponement is that this release will be enhanced to allow them to be called in asynchronous Apex (pg 401)

Important note: Even when calls to getContent() and getContentAsPdf() aren’t tracked against callout limits, they’re still subject to the usual Apex limits such as CPU time, etc. These limits are cumulative across all callouts within the originating transaction.



Salesforce Lightning Experience Roadmap

Below (obviously subject to change) is the lastest Lightning Experience Roadmap.

Click to expand


Salesforce Spring '16 Preview Documents Here

Excited to see the new Spring '16 Preview documents are online. Some really cool stuff coming down the way and I recommend when you get a minute that you take glance at what is coming!

Salesforce Spring ’16 Release Notes

Download as a PDF here


Sign up for a Free Salesforce Wave Analytics Developer Account

I went to the Partner Forum the other day at part of the Salesforce World Tour here in Dallas. I've been working my way through Trailhead and absolutely love the concept. One of the things from Dreamforce this year that they went into was Wave Analytcs but I've just been so swamped that I haven't had a chance to deep dive.

Last night I decided I couldn't/shouldn't put it off any longer. In case you are wondering how to get started you go here:

Fill in this form and you're off to the races.

I then recommend you hop into Trailhead and start working on your

Wave Analytics Basics badge.

We're talking way cool here. The power behind Wave Analytics is insane. I've used probably 5 raw statistical programs in my career and another 3 full-blown BI solutions. This is by way the coolest thing I have seen this year. I'm going to be playing with this all weekend. I am blown away. Other vendors need to take notice.






What counts toward Salesforce CPU limits?

Once in a while you we all hit the following Apex CPU time limit exception. Example

System.LimitException: Apex CPU time limit exceeded


Most of the time when I see this and dig into it I'll find things like FOR loops inside of FOR loops (i.e. dream within a dream) or tons of workflows/processes being run before the code that threw the exception was even hit.

To start with let's just cover what counts against this CPU timer and why it is there. The CPU timer is there because Salesforce is a multitenant system. It has to serve many customers on the same servers so the governor limits are there to make sure one customer or block of code is not using up all the resources.

Counts Doesn't Count
Any Apex Code HTTP Callouts
Workflow Execution Database operations, e.g. DML, SOQL
Library functions exposed through Apex SOSL
Any time spent evaluating formulas for validation/workflows


As I said before, one of the biggest trouble spots I often run into is poorly written logic in formulas that eat up time.

One cool thing is that you can open the Developer Console and click on a specific execution. Click on one of the log entries and then switch the perspective to Analysis.

Salesforce Developer Console Switch Perspective

Next you will get to see some nifty tables, charts and graphs that will help you to really see how much of your CPU time is being used and being used by what.

Click to expand (Salesforce Dev Console Execution Timeline)


One great resource to have in your back pocket is if you don't need the code to execute at a given instance run it in a batch. i.e. Database.Batchable



Salesforce Winter '16 Limits Cheatsheet

Don't leave home without it. Each release Salesforce puts out, I always download the latest "Governance" guide to my desktop. Here is the latest one from Winter '16.

Winter '16 Salesforce Limits Quick Reference Guide (version 35)