+ Start a Discussion
justinhowell82justinhowell82 

Optimize custom object relationship for efficient APEX bulkification

Background:

We have a terrible .net/mssql quoting solution that we are planning to do away with.  We are shopping it around at the same time also trying to see if we can duplicate all of the "required" functionality with some custom SFDC objects/classes/controllers/pages.  The one functionality that I'm not to sure on how to replicate (becuase of govenor limits) is the use of SQL lookup tables.  To keep it simple, we have 4 tables Quote, QuoteLine, Product, PriceAgreements.  The way the quote line validation works is that for every quote line (up to 500 lines) we look to see if the product on that line is in the PriceAgreements table and if it is we enforce contractual pricing constraints.  I tried to do this quickly in SF (described below) and quiclkly ran into a governor limit of no more than 100 SOQL queries.

 

SFDC setup

We have a custom quote (Q) and quote line (QL) obect.  The QL has a lookup relationship to the standard salesforce Product (P) object.  We also have a priceagreement (PA) object that also has a lookup to P.  When I call a class to validate all lines on the quote I'm looping over a list of QL and querying the PA object to determine if the product on the quote line is in the priceagreement object.  Once I get to 100 quote lines it craps out.  I can't help but think this could be elegantly designed but that is where I am stuck.

 

Any ideas on a way to be able to perform this validation line by line without hitting a govenor limit.  We are flexible with our object structure as it is just a proof of concept so I'll take anything!!

 

Thanks,

Justin Howell

crop1645crop1645

Justin -- welcome to the world of bulk processing in SFDC

 

You simply can't do SOQL queries in a loop (as you discovered).

 

Pass 1

Instead, you loop through the QL and build up a List of Product 'keys' that you will fetch in one SOQL call (into memory - most likely a Map). Similarly for PA 'keys'. 

 

Pass 2

Reloop through your QL and compare against the maps you built in pass 1

 

SOQL calls within for loops will inevitably run into governor limits; same for DML calls within for loops