Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DF14: Faster SOQL? Yes, Please

DF14: Faster SOQL? Yes, Please

By John Tan (@johntansfdc) and Chris Peterson (@ca_peterson) at Dreamforce '14

This presentation was recorded and can be watched at http://dreamforce.vidyard.com/watch/4MfH1Hne0EV76ChTjSWfbQ

Chris Peterson

October 16, 2014
Tweet

More Decks by Chris Peterson

Other Decks in Technology

Transcript

  1. Faster SOQL? Yes, please!
    John Tan
    Architect Evangelist, salesforce.com
    @johntansfdc
    Chris Peterson
    Force.com MVP
    Senior Developer, FinancialForce.com
    @ca_peterson

    View full-size slide

  2. Safe Harbor
    Safe harbor statement under the Private Securities Litigation Reform Act of 1995:
    This presentation may contain forward-looking statements that involve risks, uncertainties, and assumptions. If any such uncertainties materialize or if any of
    the assumptions proves incorrect, the results of salesforce.com, inc. could differ materially from the results expressed or implied by the forward-looking
    statements we make. All statements other than statements of historical fact could be deemed forward-looking, including any projections of product or service
    availability, subscriber growth, earnings, revenues, or other financial items and any statements regarding strategies or plans of management for future
    operations, statements of belief, any statements concerning new, planned, or upgraded services or technology developments and customer contracts or use
    of our services.
    The risks and uncertainties referred to above include – but are not limited to – risks associated with developing and delivering new functionality for our service,
    new products and services, our new business model, our past operating losses, possible fluctuations in our operating results and rate of growth, interruptions
    or delays in our Web hosting, breach of our security measures, the outcome of any litigation, risks associated with completed and any possible mergers and
    acquisitions, the immature market in which we operate, our relatively limited operating history, our ability to expand, retain, and motivate our employees and
    manage our growth, new releases of our service and successful customer deployment, our limited history reselling non-salesforce.com products, and
    utilization and selling to larger enterprise customers. Further information on potential factors that could affect the financial results of salesforce.com, inc. is
    included in our annual report on Form 10-K for the most recent fiscal year and in our quarterly report on Form 10-Q for the most recent fiscal quarter. These
    documents and others containing important disclosures are available on the SEC Filings section of the Investor Information section of our Web site.
    Any unreleased services or features referenced in this or other presentations, press releases or public statements are not currently available and may not be
    delivered on time or at all. Customers who purchase our services should make the purchase decisions based upon features that are currently available.
    Salesforce.com, inc. assumes no obligation and does not intend to update these forward-looking statements.

    View full-size slide

  3. Your company uses Salesforce/
    Force.com …

    View full-size slide

  4. You are a developer or architect …

    View full-size slide

  5. You want to ensure your queries
    perform …

    View full-size slide

  6. Agenda
    1.  Selective Filters
    2.  Query Plan Tool Demos

    View full-size slide

  7. Query Plan Tool in Developer Console

    View full-size slide

  8. John Tan
    Architect Evangelist, salesforce.com

    View full-size slide

  9. Developers Community
    http://bit.ly/Arch_Core_Resources

    View full-size slide

  10. Cheat sheet
    http://bit.ly/Query_Cheat_Sheet

    View full-size slide

  11. Chris Peterson
    Force.com MVP
    Senior Developer, FinancialForce.com

    View full-size slide

  12. Query Plan API
    http://bit.ly/Query_Plan_API

    View full-size slide

  13. Selective filters

    View full-size slide

  14. What is the Force.com query optimizer?
    Generates the most efficient query based on:
    •  Statistics
    •  Indexes / Skinny Tables
    •  Sharing

    View full-size slide

  15. Basic Algorithm
    •  Pre-query engine
    •  Chooses the most selective filter from the WHERE clause
    •  Determine the best leading table/index to drive the query
    Query optimizer will make the best choice best on the filters in your query.

    View full-size slide

  16. Best Practice: One+ Selective Filter Per SOQL statement

    View full-size slide

  17. Selective Filters: Four Components
    1.  Filters - add filters to reduce data
    2.  Operators - avoid 2 inefficient filter operators
    3.  Thresholds – ensure filter meets selectivity threshold
    4.  Index Creation - index the filter field

    View full-size slide

  18. Fields that often make good filters
    •  Date fields
    •  Picklists
    •  Fields with wide and even distribution of values

    View full-size slide

  19. Non-Deterministic Formula Fields Aren’t Good Filters
    •  Can’t index
    •  For example:
    –  References related object
    –  CASE(MyUser__r.UserType__c,1,”Gold”,”Silver”)
    –  Create separate field and use trigger to populate
    Force.com SOQL Best Practices: Nulls and Formula Fields

    View full-size slide

  20. Avoid negative operators
    Query for reciprocal values instead
    ✖ Status != 'closed'
    ✔ Status IN ('open', 'in progress')

    View full-size slide

  21. Avoid filters with a leading % wildcard
    CONTAINS or LIKE ‘%searchstring%’
    ✖ CONTAINS ('district') / LIKE '%district%'
    ✔ STARTS WITH ('district') / LIKE 'district%'

    View full-size slide

  22. Standard Index Selectivity Threshold
    A standard index is selective when it returns:
    •  < 30% of the first 1 million records
    •  < 15% of returned records after the first 1 million records
    •  No more than 1 million total records

    View full-size slide

  23. Standard Index Selectivity Threshold
    For 750,000 Account records
    •  < 30% of the first 1 million records
    •  750,000 x .30 = 225,000

    View full-size slide

  24. Standard Index Selectivity Threshold
    For 3,500,000 Account records
    •  < 30% of the first 1 million records
    •  < 15% of returned records after the first 1 million records
    •  (1,000,000 x .30) + (2,500,000 x .15) = 675,000

    View full-size slide

  25. Standard Index Selectivity Threshold
    Over 5,600,000 Account records
    •  No more than 1 million records
    •  1,000,000

    View full-size slide

  26. Custom Index Selectivity Threshold
    A custom index is selective when it returns:
    •  < 10% of the first million records
    •  < 5% of returned records after the first million records
    •  No more than 333,333 records

    View full-size slide

  27. Standard Fields With Indexes
    •  Id
    •  Name
    •  OwnerId
    •  CreatedDate
    •  SystemModstamp
    •  RecordType
    •  Master-detail fields
    •  Lookup fields

    View full-size slide

  28. Custom Indexes
    •  Discover common filter conditions
    •  Determine selective fields in those conditions
    •  Request custom indexes

    View full-size slide

  29. A filter condition is selective when …
    … it uses an optimizable operator
    … it meets the selectivity threshold
    … selective fields have indexes

    View full-size slide

  30. Query Plan Tool in Developer Console

    View full-size slide

  31. Optimizations

    View full-size slide

  32. AND conditions
    WHERE FirstName__c = 'Jane' AND LastName__c = 'Doe' AND City__c
    = 'San Francisco'
    Step 1 – Allow each index to still be considered if they return < 2X selectivity threshold
    Step 2 – INTERSECTION of all indexes must meet *selectivity threshold
    Step 3 – Use composite index join to drive query
    *If all indexes are standard indexes, use standard index selectivity threshold. Otherwise, use the custom index selectivity threshold

    View full-size slide

  33. AND conditions
    Composite index join – MyUser object 100,000 total records

    View full-size slide

  34. AND conditions
    Composite index join – MyUser object 100,000 total records

    View full-size slide

  35. 2-column index
    For this simple example, it makes more sense to have Customer Support create a 2-column index.

    View full-size slide

  36. OR conditions
    WHERE FirstName__c = 'Jane' OR LastName__c = 'Doe' OR City__c =
    'San Francisco'!
    Step 1 – Each field must be indexed and meet selectivity threshold
    Step 2 – UNION of all the indexes must meet *selectivity threshold
    Step 3 – Use union to drive query
    *If all indexes are standard indexes, use standard index selectivity threshold. Otherwise, use the custom index selectivity threshold

    View full-size slide

  37. OR conditions
    Union - MyUser object 100,000 total records

    View full-size slide

  38. Sort Optimization
    ORDER BY CreatedDate LIMIT 100
    Step 1 – ORDER BY on date or number field
    Step 2 – Bound results with LIMIT clause
    * May have to add CreatedDate <> null explicitly to WHERE clause

    View full-size slide

  39. Summary
    •  Query optimizer generates the most efficient query possible
    •  1+ selective filters per SOQL statement
    •  Test with > 10K records

    View full-size slide

  40. Links
    •  Architect Core Resources – http://bit.ly/Arch_Core_Resources
    •  Query cheat sheet – http://bit.ly/Query_Cheat_Sheet
    •  Query Plan API wiki - http://bit.ly/Query_Plan_API
    •  Sort Optimization blog - http://bit.ly/Sort_Optimization

    View full-size slide