Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SFDC Data Architecture & Management > Indexing > Flashcards

Indexing Flashcards

1
Q

In what order does Salesforce perform indexed searches?

A
  1. Searching indexes
  2. Narrow results based on access permissions, search limits and other filters (which produces a result set)
  3. After the result set reaches a predetermine size, the remaining records are discarded.
  4. Result set is then used to query the records from the database to retrieve the fields that a user sees
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

List the 6 steps that the Lightning Platform Query Optimizer follows

A
  1. Determines the best index from which to drive the query based on the query filters
  2. Determines the best table to drive the query from if no good index is available
  3. Determines how to order the remaining tables to minimize cost
  4. Injects Custom Foreign key value tables as needed for efficient join paths
  5. Influences the execution plan for the remaining joins, including sharing joins, to minimize database IO
  6. Updates statistics
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How often does the Salesforce statistics gathering process runs?

A

Nightly

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Which fields does Salesforce not support for custom indexes?

A
  1. Multi-select picklists
  2. Currency fields in a multicurrency organization
  3. Text areas (long and rich)
  4. Non-deterministic formula fields
  5. Binary fields (Blob, file or Encrypted text)
  6. New data types added by Salesforce
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Are external ID fields indexed?

A

Yes. The query optimizer will consider external ID fields

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What data types can be External Ids?

A
  1. Auto Number
  2. Email
  3. Number
  4. Text
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

The Salesforce multi-tenant architecture makes the underlying data table for custom fields unsuitable for indexing. How does Salesforce overcome this limitation?

A

The platform creates an index table that contains a copy of the data, along with information about the data types.

It then builds a standard database index on this index table.

The index table places upper limits on the number of records that an indexed search can effectively return.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What about null records in the index table? Are they included or not?

A

By default they do not include records that are null (or have empty values).

BUT, you can work with Salesforce Customer Support to create custom indexes that include null rows

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How does the query optimizer know that an index won’t work (or will return too many rows)

A

The query optimizer maintains a table containing statistics about the distribution of data in each index.

It then uses this table to pre-query to determine whether using the index can speed up the query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

When will standard indexed fields be used?

A

If the filter matches
less than 30% of the first million records
and less than 15% of remaining records
up to a maximum of 1 million records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

When will custom indexed fields be used?

A

If the filter matches
less than 10% of the first million records
and less than 5% of the remaining records
up to a maximum of 333,333 records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

For a standard index, if the query is executed against a table with 2 million records, what is the maximum number of records returned that will allow the index to be used?

A

450,000 records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

For a standard index, if the query is executed against a table with 5 million records, what is the maximum number of records returned that will allow the index to be used?

A

900,000 records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

For a custom index, if the query is executed against a table with 500,000 records, what is the maximum number of records returned that will allow the index to be used?

A

50,000 records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

For a custom index, if the query is executed against a table with 5 million records, what is the maximum number of records returned that will allow the index to be used

A

333,333 records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

When a query contains AND, the query optimizer will use the custom indexes unless ONE of them returns more than what % of the object’s records up to how many total records?

Answer in the format of: x% of the object’s records or y total records

A

The index will be used unless one of them returns more than 20% of the object’s records or 666,666 total records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

100When a query contains OR, the query optimizer will use the custom index unless they ALL return more than what % of the object’s records, or total records?

Answer in the format of: x% of the object’s records or y

A

The index will be used unless they all return more than 10% of the object’s records or 333,333 total records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How can you create a custom index?

A

Contact Salesforce Customer support

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Are custom indexes copied to all sandbox environments?

A

Yes (if the sandbox is created from the production copy that has the custom index)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Salesforce typically maintains indexes on which fields? (For most objects that is)

A

Indexed Standard Fields on All objects:

  • Id
  • Name
  • Division
  • OwnerId
  • CreatedDate
  • Systemmodstamp
  • RecordType (indexed for all standard objects that feature it)
  • Master-Detail fields
  • Lookup fields
  • Email (Leads and Contacts)

Other indexed fields:

  • Unique fields
  • External ID fields
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

For an OR clause, must all the fields be index for any index to be used?

A

Yes, all the fields must be indexed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What does the Lightning query optimizer do with a LIKE filter value (if that field has a custom index on it)

A

The query optimizer does not use its internal statistics table. Instead, it samples up to 100,000 records of actual data to decide whether to use the custom index

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Which type of formula fields can be used in custom indexes?

A

Deterministic formula fields.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Give 3 examples of common non-deterministic formulas

A
  1. Reference other entities (like fields accessible through lookup fields)
  2. Include other formula fields that span over other entities
  3. Use dynamic date and time functions (for example, TODAY and NOW)
25
Q

Apart from the usual non-deterministic formulas (ones that reference lookup fields or dynamic dates), there are other formula fields that are also considered non-deterministic. (Name 3)

A
  1. Owner, autonumber, divisions or audit fields
  2. References to fields that Force.com cannot index
  3. Standard fields with special functionalities
26
Q

Which fields can the Lightning Platform not index (and is therefore seen as non deterministic if used in a formula)

A
  • Multi-select picklists
  • Currency fields in a multi-currency organization
  • Text Area fields(Long and Rich)
  • Binary fields (blob, file or encrypted text)
27
Q

Which standard fields with special functionalities cannot be used by the Lightning Platform for indexing (and is therefore seen as non deterministic if used in a formula)
(Hint, there are 5 fields on Opportunity, 2 on Case and Activity, 3 on Product and one field on Solution)

A

Opportunity: Amount, TotalOpportunityQuantity, ExpectedRevenue, IsClosed, IsWon

Case: ClosedDate, IsClosed

Product: Product Family, IsActive, IsArchived

Solution: Status

Activity: Subject, TaskStatus, TaskPriority

28
Q

What happens if you have a custom index on a formula, and you change the formula?

A

The index that was on the formula is disabled. You need to contact Salesforce Customer Support to re-enable the index

29
Q

When will cross-object indexes be used?

A

When specified using the cross-object notation

Ex:
Select id from score__c
wjhere crossobject1__r.crossobject2__r.indexField__c …

30
Q

What are two-column custom indexes?

A

They are a specialized feature of the SFDC platofrm and are useful for list views and situations where you want to use one field to select records to display, and another field to sort them

Example: An account list view that selects by state and sorts by city, can use a two-column index with State in the first column and City in the second

31
Q

For the following query, would two single indexes or a two-column index be better?

SELECT Name
FROM Account
WHERE f1__c = ‘foo’
AND f2__c = ‘bar’

A

A two-column index on f1__c and f2__c is more efficient than single indexes on f1__c and f2__c

32
Q

What is one of the differences between having a two-column index vs a single column index

A

Two column indexes can have nulls in the second column, whereas single-column indexes can’t unless Salesforce Customer Support explicitly enabled the option to include nulls

33
Q

Provide a solution for the following situation:

The customer needed to allow nulls in a field and be able to query against them. Because single column indexes for picklists and foreign key fields exclude rows in which the index column is equal to null, an index could not have been used for the null queries.

A

The best practice would have been to not use null values initially. If you are in a similar situation, use some other string, such as N/A in place of NULL.

If you cannot do that, create a formula field that displays text for nulls, and then index that formula field

34
Q

What are the negative filter operations in SOQL that will not allow the index to be used?

A

!=
NOT LIKE
EXCLUDES

35
Q

What are the negative filter operations in Reports and List Views that will not allow the index to be used?

A

not equal to
does not contain
excludes

36
Q

Which comparison operators in SOQL paired with text fields will not allow the index to be used?

A

text_field <
text_field >
text_field <=
text_field >=

37
Q

Which comparison operators in Reports and List Views paired with text fields will not allow the index to be used?

A

text_field less than
text_field greater than
text_field less or equal
text_field greater or equal

38
Q

Which filter conditions in SOQL with leading “%” wildcard will not allow the index to be used?

A

LIKE ‘%string%’

39
Q

Which filter conditions in Reports and List Views with leading “%” wildcard will not allow the index to be used?

A

contains

40
Q

Which SOSL fields can be used for search indexes?

A

Name fields
Phone fields
Text fields
Picklist fields

41
Q

True or False: SOQL can use the index on SystemModStamp for this query:

Select Id, Name from Account where LastModifiedDate < CutoffDate__c

A

False

42
Q

True or False: SOQL can use the index on SystemModStamp for this query:

Select Id, Name from Account where LastModifiedDate > 2017-11-08T00:00:00Z

A

True

43
Q

Which field types cannot be setup such that their indexes include NULL rows

A
  1. Picklist fields
  2. Lookup fields
  3. External Ids

Instead, contact Salesforce Support for assistance with creating a two-column (compound) index

44
Q

What custom field type is automatically indexed when created?

A

External Id

45
Q

What does the Force.com Query Optimizer do when a query has a compound WHERE clause?

A

It considers the selectivity of the single-column indexes alone, as well as the intersected selectivity that results from joining two single-column indexes.

46
Q

List 3 differences with the Force.com Query Optimizer compared to traditional relational database optimizers

A
  1. Multitenant Statistics - Because Salesforce is a multitenant environment, Salesforce keeps tenant-specific statistics to provide insight into each tenant’s data distribution
  2. Composite Index Joins - The Force.com Query Optimizer considers the selectivity of the single-column indexes alone, as well as the intersected selectivity that results from joining two single-column indexes.
  3. Sharing Filters - The force.com Query Optimizer considers the selectivity of sharing filters alongside traditional filters (i.e. The WHERE clauses) to determine the lowest cost plan for query execution.
47
Q

What happens when an index is not available for a field in a filter condition?

A

The only alternative is to scan the entire table/object, even when the filter condition uses a optimzable operator with a selective value

48
Q

When will the platform automatically recalculate optimizer statistics in the background?

A

When the data set changes by 25% or more

49
Q

What should you do if you change a little less than 25% of your data set for a LDV object and you notice slower query or report performance?

A

Submit a case to Salesforce Premier Support to see if a manual statistic recalculation for select objects in your org can return operation to peak performance

50
Q

What is a compound WHERE clause condition?

A

i.e. WHERE x and y

51
Q

Describe LastModifiedDate

A
  1. Updated whenever a user creates or updates a record
  2. Can be imported with any back-dated value if your business requires preserving the original timestamps when migrating data into Salesforce
  3. NOT indexed
52
Q

Describe SystemModStamp

A
  1. Always read only
  2. Updated whenever a user creates or updates a record, AS WELL AS whenever an automated system process updates the record.
  3. Indexed
53
Q

Is it possible that LastModifiedDate <= SystemModStamp

A

Yes

54
Q

Is it possible that LastModifiedDate >= SystemModStamp

A

No

55
Q

True or False: SOQL can use the index on SystemModStamp for this query:

Select Id, Name from Account where LastModifiedDate = CustomDate__c

A

True

56
Q

Which is a best practice: Using LastModifiedDate or SystemModStamp to filter your SOQL Queries?

A

SystemModStamp

57
Q

What options exist to optimize performance for LastModifiedDate if your business requirements do not allow you to use SystemModSTamp (or if SystemModStamp is not available for the object you are querying)?

A
  1. Use a custom date field - Create a custom date field and use a workflow or other mechanism to populate this field with the value of LastModifiedDate. Then contact Salesforce to have a custom index placed on the custom date field.
  2. Use a skinny table - If your query or report performance over large data volumes is sluggish, consider a skinny table. If LastModifiedDate is added as a column, it can be indexed on a skinny table.
  3. Filter on LastActivityDate. If your business requirement is to pull up Account or Contact records related to activities, and if you plan on using a Skinny Table, contact Salesforce to request an index on the Skinny Table
  4. Use the Data Replication API - Use getUpdated() to retrieve updated records. Under the hood, the API uses SystemModSTamp to determine the matching records, and if it doesn’t exist, will automatically use LastModifiedDate or CreatedDate
58
Q

Name 2 reasons why SOQL queries that filter using a formula field can result in slow performance

A
  1. Formula fields are not indexed by default (and therefore require a full table scan if they are the primary operation type chosen by the query optimizer)
  2. Formula field values are calculated on the fly (actual values are not stored in the database for these fields)

SFDC Data Architecture & Management (14 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions