Passed Salesforce Certified Integration Architecture Designer

I cleared Salesforce Certified Integration Architecture Designer exam on 9 Sept 2016.

Here are my notes those may help you to prepare and give you some idea on what to expect.

Important Topics:

1. SOAP API - partner WSDL VS enterprise WSDL
2. REST API 
3. Outbound messaging with all limitations and pro and corns 
4. There was question about email service which was not mentioned in study guide 
5. Data Loader 
6. ETL tools pro and cons 
7. Governor limits 
8. Security 
9. Other APIs metadata, chatter, stemming, bulk 

Other notes 

• Almost 70% questions were scenario based 
• It took me 89 minuets to complete then exam and did not got any time for review 
• I know the above points so i followed do it once and do it right principal
• There was only one question on  Enterprise Integration Patterns - The Book and to be very frank, i just read the title of it. As its too much to read. if you are like me then may be you can just brush through it and focus on rest of important topics 
• Many times you will feel that all options are correct 
• Follow elimination technique to reduce your choices 

Please don't ask for any sample questions. If you have any other questions please feel free to ask in comment and i will get back to you.

All the best!

Overview of Salesforce database :

·         Salesforce works on Multi-tenant architecture, i.e many organizations (Tenants) are using same infrastructure.

·         Salesforce database doesn’t save data in traditional way. Data of all tenants are in common table.

·         It saves Metadata Information in hundreds of table.

·         Run-time engine, generates organization specific query to get information about their organizations and fetch data from common table.

·         Database tables are partitioned by Organization ID and generates virtual table specific to Org.

·         Due to Multi-tenant architecture ,traditional Query optimization query and technique will not work in this case.

·         To consider multitenant statistics, the platform has its own SOQL query optimizer that determines optimal SQL to satisfy a specific tenant's request.

Overview of Force.com query optimizer :

·         The Force.com query optimizer is an engine that sits between your SOQL and the database itself.

·         Because of salesforce.com’s multitenancy, the optimizer gathers its own statistics instead of relying on the underlying database statistics.

·         Using both these statistics and pre-queries, the optimizer generates the most optimized SQL to fetch your data.

·         It looks at each filter in your WHERE clause to determine which index, should drive your query.

How does Force.com query optimizer works:

·         Determines the best index from which to drive the query, if possible, based on filters in the query

·         Determines the best table to drive the query from if no good index is available

·         Determines how to order the remaining tables to minimize cost

·         Injects custom foreign key value tables as needed to create efficient join paths

·         Influences the execution plan for the remaining joins, including sharing joins, to minimize database input/output (I/O)

·         Updates statistics

Query Plan Tool :

·         There is no such tool available to optimize final generated SQL but Salesforce released Query Plan Tool to analyze how query is performing.

·         Query Plan tool in the Developer Console can help speed up SOQL queries done over large volumes.

·         Use the Query Plan tool to optimize and speed up queries done over large volumes.

How to enable the Query Plan Tool in the Developer Console?

Go to:

Developer Console>>Help > >Preferences and set "Enable Query Plan" to true

Why use this tool?

·         Use this tool to check the Query Plan for any SOQL queries that execute slowly or queries which are run on large volume data .

·         It will provide you with insight on the different plans

·         It provide the cost of SOQL using the index compared to a full table scan. If the cost for the table scan is lower than the index, and the query is timing out.

·         In such a scenario, you will need to perform further analysis on using other filters to improve selectivity

How To determine if a filter is selective?

1.       If the filter is on a standard field, it'll have an index if it is a primary key, a foreign key and an audit field

2.       Custom fields will have an index if they have been marked as Unique or External Id

3.       If the filter doesn't have an index, it won't be considered for optimization

4.       If the filter has an index, determine how many records it would return:

Ø  For a standard index, the threshold is 30 percent of the first million targeted records and 15 percent of all records after that first million.

i.e. the selectivity threshold for a standard index maxes out at 1  million total targeted records, which you could reach only if you had more than 5.6 million total records.

Ø  For a custom index, the selectivity threshold is 10 percent of the first million targeted records and 5 percent all records after that first million.

i.e. the selectivity threshold for a custom index maxes out at 333,333 targeted records, which you could reach only if you had more than 5.6 million records.

5.       If the filter exceeds the threshold, it won't be considered for optimization.

6.       If the filter doesn't exceed the threshold, this filter is selective, and the query optimizer will consider it for optimization.

View Query plan :

·         To use the Query Plan tool you simply enter a query in the Query Editor and press the "Query Plan" button which returns a Query Plan for the SOQL provided.

·         The Query Plan tool will show a list of available plans that our Query Optimizer can utilize for the query provided and will be arranged by cost ascending.

·         Each Plan will contain information on Cardinality, Operation Type, Cost, sObject Type etc.

·         The plan with the lowest cost is the plan that is used for driving the query execution.

Terminology used to display in query plan:

1.       Cardinality : The estimated number of records that the leading operation type would return.

2.        Fields : The indexed field(s) used by the query. If the leading operation type is Index, the fields value is Index. Otherwise, the fields value is null.

3.       LeadingOperationType : The primary operation type that Salesforce will use to optimize the query

a. Index - The query will use an index on the query object.

b. Other - The query will use optimizations internal to Salesforce.

c. Sharing - The query will use an index based on the sharing rules associated with the user who is executing the query. If there are sharing rules that limit which records that user can access, Salesforce can use those rules to optimize the query.

d. TableScan - The query will scan all records for the query object.

4.        Notes : An array of one or more feedback notes.

5.       RelativeCost : The cost of the query compared to the Force.com query optimizer’s selectivity threshold. Values above 1 mean that the query won’t be selective.

6.       sobjectCardinality : The approximate record count for the query object.

7.       sobjectType: The name of the query object.

How is the Cost determined?

·         Each plan has its own Cost value. The cost value is derived from the latest gathered database (DB) statistics on the table and values.

·         The plan with the lowest cost will be the plan used.

·         If the Cost is above 1, it means that the query won’t be selective.

Improve the performance of SOQL queries

When querying large objects special design considerations must be taken into consideration.

1. Make SOQL query selective

2. Improve performance with Custom indexes using Selective SOQL Queries

1. Make SOQL query selective:

·         The performance of a SOQL will depend on the presence of a selective filter

·         If a SOQL query contains at least 1 selective filter, the query is said to be selective.

·         If the SOQL query doesn't contain a selective filter, the query is said to be non-selective and will require a full table scan.

·         A filters can be simple or composite

o   Simple filters - single field expression in where clause or multiple expressions with 'AND' operator

o   composite filter - result of joining 2 or more field expressions via the "OR" operator is a .

·         A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold.

·         The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the threshold related conditions.

2. Improve performance with Custom indexes using Selective SOQL Queries

·         Salesforce support can add Custom Indexes upon request for customers

·         When creating a case with Support, be sure to include the SOQL query which has the field to be indexed as a filter in the WHERE clause. Also be sure to include bind values if any.