3

The list views in salesforce are essentially queries running in the back end. So my question is when a user does the following query/or access a list view that implements the same.

SELECT id FROM Account WHERE Industry = 'Public'

There are two factors that that the query engine has to consider when returning the user the results to the query/list view.

  1. The records from the total database content of account records that satisfy the conditions to be returned as a result to this query
  2. The records that are visible to the user

And in the end the system returns the records that satisfy both these conditions. So which of these conditions does salesforce evaluate first? Does it first filter out all those account records that are visible to the user and then runs the WHERE clause Or is it the other way around.

I am facing a scenario in which list views are not working and are running into the 'time Exceeded' error. The system does have millions of records in the database that satisfy the list view criterion. But only about a handful of records < 100 are actually visible to the user. I am aware that list views are ineffective against large data stocks but in this case we are only expecting < 100 records in the results.

Please let me know your thoughts.

2

List views are actually considered "filters". You're correct though that they're ultimately processed as queries. If you're familiar with the query optimizer that's available in the Developer Console, you may want to use it to help you with your filters. The more indexed fields you use as filters, the more selective your query is going to be.

It's my understanding, that Salesforce's query engine is somewhat dynamic and evaluates how to run a specific query at run time. Sharing is one of those considerations. A worst case scenario would be for a user who's an admin that can see essentially everything. Those are the ones you want to optimize your queries for. The engine will then further decide on its own how to take sharing into consideration by chunking down blocks of records a user doesn't have access to.

The most difficult filters for the engine to process will be the following ones:

  • Negative filter operators ( !=, NOT LIKE, EXCLUDES )
  • Comparison operators paired with text fields ( text_field <, text_field >, text_field <=, text_field >= )
  • Leading % wildcards ( LIKE '%somestring', trailing % wildcard are far less an issue ) Note: this is the equivalent to 'contains' which is an absolute performance killer and should be avoided if at all possible.
  • References to non-deterministic formula fields ( e.g. cross-object formula fields )

My suggestion is to look at your equivalent query with the "filters" applied to determine it's "cost". A cost > 1 means that it will involve a table scan which is not good for performance. The greater the size of your database, the more difficult the query will be to perform, and thus the slower it will be.

If your query falls into this situation and you're not able to make it more selective, you may need to enlist Salesforce's help to have them index a particular field or create a skinny table for you. Indexing additional fields will slow the database down during DML, so isn't an especially desirable thing to do.

Edit

In the specific query you cite as an example, you don't use any indexed fields in your WHERE condition that include Id, Name, OwnerId, CreatedDate, SystemModStamp, RecordType, Master-Detail Fields, Look-up Fields, Unique Fields or External Id fields. Including any of those would help make your query more selective. Although Id is indexed, you're not providing an Id in your WHERE clause. Industry is not a RecordType. Consequently, you're not using any indexed fields in that particular query to make it a selective query.

That means your query requires a Table Scan (every record must be read to find the results). That isn't a function of permissions, even for those who don't have permissions to see all the records, for every record they can see, it would require a table scan.

  • Thank you. Is it possible to make "visibility" the key point they have to consider while running this list view? Is this something that can be done by the developer or by sfdc support? – Prajith Mar 30 '16 at 13:52
  • 1
    I don't believe there is. MVP Lars Neilsen from Symphonic Source, maker of Cloudingo Studio and DupeCatcher, presented last week at our DUG. He covered this exact material and what he knows of how SF processes queries. SF uses a proprietary algorithm to determine which way to process the query at run time & convert it to SQL. It can change depending on circumstances. Check the Query Optimizer to see what's happening for your specific query. – crmprogdev Mar 30 '16 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.