Lookup Price Rule query considerations with Salesforce CPQ

Given the limits for total number of SOQL queries run in an org per transaction, each line's criteria cannot be individually queried against the database. As a result, all lookup queries are pulled together to create one large query that pulls in all potential matches from the lookup object before using that list of potential matches to determine the record and values used in Price Actions. What is a potential match? See the below example.

Example - You have a price rule to pull the value of LOField3 (i.e. Lookup Object Field 3) into a quote line field based on the values of QLField1 and QLField2 (i.e. Quote Line Field 1 and Quote Line Field 2).

Lookup Object records:
1. LOField1 = A | LOField2 = 1 | LOField3 = Apple
2. LOField1 = A | LOField2 = 2 | LOField3 = Banana
3. LOField1 = A | LOField2 = 3 | LOField3 = Blueberry
4. LOField1 = B | LOField2 = 1 | LOField3 = Kiwi
5. LOField1 = B | LOField2 = 2 | LOField3 = Mango
6. LOField1 = B | LOField2 = 3 | LOField3 = Orange
7. LOField1 = C | LOField2 = 1 | LOField3 = Peach
8. LOField1 = C | LOField2 = 2 | LOField3 = Pear
9. LOField1 = C | LOField2 = 3 | LOField3 = Strawberry

Quote Lines:
1. QLField1 = A | QLField2 = 1
2. QLField1 = B | QLField2 = 2

Price Rule Lookup Queries:
1. QLField1 value equals LOField1
2. QLField2 value equals LOField2

Price Rule Price Action:
1. Set the value of <quote line field> Equal to LOField3

The CPQ engine assembles the SELECT clause of the SOQL query run for the lookup by pulling in all fields on the lookup object that are referenced in the Lookup Queries and Price Actions. 

The CPQ engine takes values from the quote lines and uses them to assemble the WHERE clause in the SOQL query that runs to find potential matching lookup object records. The pricing engine applies OR logic for each possible value in each individual field. The collections of field values are connected to other collections of field values with AND logic. The example results in the following query:

SELECT LOField1, LOField2, LOField3 FROM LookupObject WHERE (LOField1 = A OR LOField2 = B) AND (LOField2 = 1 OR LOField2 = 2)

Note there are 4 of our lookup object records (1, 2, 4, and 5) that would be returned by the query even though there are only 2 quote lines. The CPQ package loops over the records returned by the query to determine the exact match for each line to pull the correct lookup table field value.

This small example shows us having 2x the number of needed records returned from the query. If there were a third quote line (QLField = C | QLField2 = 3), the query would then return all 9 lookup records (3x the number of records needed!). This example is to show that the number of lookup records, lookup fields, and quote line records have the potential to have a multiplicative effect when running lookup queries.

How to reduce query rows returned

As seen above, the number of lookup price rules, number of lookup queries, number of quote lines, and number of lookup table records can have a multiplicative effect on the total query rows returned during calculation. Reducing any of these four things can reduce the number of query rows returned.

If you are hitting a Too Many Query Rows error from Lookup Price Rules, one possible solution is to consider splitting up your lookup object into multiple objects. Have Price Conditions that separate which lookup table your price rule applies to. Cutting the pool of potential matches for each query reduces the number of extra records pulled. Though an extra SOQL query or two is added to the processing, these SOQL queries would each be more precise based on what was used to divide the lookup table.

How to Reduce Heap Size

If reducing the number of query rows returned still yields a heap size error, then there are two ways to work to avoid this error, then the best course of action is to reduce the number of fields queried off the lookup object from Price Actions. Each lookup object field that is referenced in the Price Actions have its value pulled for each lookup object row returned. Each field contributes to the heap total for records returned from the query.

Consider: If a field on the lookup object only contains a small number of unique values, consider removing that field from the lookup table and designing a price rule that could properly place that value into your quote without lookup tables. Every field counts!