SOQL Query Limit and Data Loading

Question

I have a philosophical question about the SOQL Query limit. What I mean by that is I don’t want to talk about specific code – just the design requirements that are leading to code.

I am new and while I understand exactly why the limit exists I seem to be struggling to understand how certain things work within that limit. You guys are all SF Rocket Scientists so hopefully you can help me to understand a little better.

Our managed package contains a couple of objects…

• Equipment: This is something that the user wants to track movements for
• Loan History: A child of Equipment that shows all times in the past that the equipment was loaned to some User/Account/Contact/Opportunity

NOTE: To make things prettier and reporting easier for the users the ‘Current Loan’ information is replicated onto the Equipment record as well as being in a Loan History record.

So we throw up a page that lets a user loan or re-loan a piece of equipment.

That process itself requires no SOQL query because you are creating a new Loan History record.

BUT the process does need to do the following SOQL queries:

• Find the User/Account/Contact/Opportunity object so I can grab the ‘Loanee’ details like name, address etc. to provide the details of the new loan
• Find the Equipment record and update the current loan information
• Find the previous Loan history record and mark it as ‘complete’ (really put a loan completion date on it)

So that means if I am REALLY good and bulkified I will have 3 SOQL queries per loan transaction.

Now let’s turn our attention to a new requirement from our user community…

They want to LOAD loan transactions.

Doing the appropriate processing when a record is loaded versus when a user types information into a VF page is not a problem - the existing code can be 'fired' either way.

BUT...

Given the SOQL Query limit is 100 does the above not mean that the MOST amount of records I could l allow to be loaded is 33 ?

So let’s say a user has 1000 pieces of equipment and need to load 1 loan transaction per.

Does that mean 333 Excel spreadsheets need to be loaded?

Really the same question applies if I only had 1 SOQL query per transaction - then I guess I would only need 100 Excel spreadsheets?

Keeping my fingers crossed that y'all are going to tell me all about 'The Better way' for data loading :-)

Thanks in Advance

Update on 11/30 at 6:15pm Mountain time

Below is a very cut down version of the process we have now. It lives in an EquipmentService apex class and uses a 'LoanInfo' object as shown. This lets us use the code in the VF page or for data loading and another process we have that can cause a loan to be initiated.

So for the purposes of this discussion let's talk VF page where the LoanInfo is being populated from an Equipment_Loan__c record the page uses to get the user to select which type of loan, to whom and the dates.

You can see there are actually 6 SOQL Queries here but at any given time only 3 will actually be executed.

public class EquipmentService
{
  public class LoanInfo
  {
     public Id EquipmentID;
     public Date LoanStartDate;
     public Date LoanEstimatedReturnDate;
     public String LoanType;
     public Id LoanedTo;
  }

  public static void ProcessLoans(List<LoanInfo> LoanInfos)
  {
   for(LoanInfo LI : LoanInfos)
    {
          // Get the equipment you are loaning
          Equipment__c obj = [select <a bunch of fields> from Equipment__c where Id=:LI.EquipmentId];  
          if(LI.LoanType == 'User')
          {
              User objUser = [select <a bunch of fields> from User where Id=:LI.LoanedTo];
                // set a bunch of loan fields from the user record
         }
         if(LI.LoanType == 'Account')
         {
              Account objAcc = [select <a bunch of fields> from Account where Id=:LI.LoanedTo];
              // set a bunch of loan fields from the account record
          }
         if(LI.LoanType == 'Contact')
         {
              Contact objCon = [select <a bunch of fields> from Contact where Id=:LI.LoanedTo];
              // set a bunch of loan fields from the contact record
         }
         if(LI.LoanType == 'Opportunity')
         {
              Opportunity objCon = [select <a bunch of fields> from Opportunity where Id=:LI.LoanedTo];
              // set a bunch of loan fields from the opp record
         }

         // Update previous Equipment Loan Record - 
         List<Equipment_Loan__c> lstEL = 
              [select <a bunch of fields> 
              from Equipment_Loan__c where Equipment__c=:LI.EquipmentId and Actual_Return_Date__c=null]; 
         if (lstEL.size() > 0)
         {             
              for(Equipment_Loan__c objELOld: lstEL)
              {  
                // update any 'unreturned' previous records that might exist 
                // (should only be 1 but you never know)
              }
         }
    }
  }
}

Answer 1

As @eyescream and @Sdry stated you should do some preprocessing to build up a Set/List of Ids to supply to your SOQL query. You can create maps that can then be used after bulk queries are done. These sort of bulk patterns of building up a collection of Ids for a SOQL query is extremely common in Apex.

This shows using the LoanInfo and assumes that the EquipmentID will be unique in the batch.

Map<Id, LoanInfo> equipmentToLoanInfo = new Map<Id, LoanInfo>();
Set<Id> loanedToIds = new Set<Id>();
for(LoanInfo LI : LoanInfos)
{      
    // Map Equipment ID to Loan Info.
    // Will use keySet() in query later
    equipmentToLoanInfo.put(LI.EquipmentID, LI);

    // store off all of the Ids for later queries
    loanedToIds.add(LI.LoanedTo);
}

Map<Id, User> dbUsers = new Map<Id, User>([
    Select <a bunch of fields> 
    From User 
    Where Id In :loanedToIds
]);
// create Same type of maps for other LoanedTo types...

for(Equipment_Loan__c objELOld : [
    select <a bunch of fields> 
    from Equipment_Loan__c 
    where Equipment__c In :LI.equipmentToLoanInfo.keySet() and Actual_Return_Date__c=null
])
{  
     LoanInfo equipLoanInfo = equipmentToLoanInfo.get(objELOld.Equipment__c);
     if (equipLoanInfo.LoanType == 'User') {
         dbUser = dbUsers.get(equipLoanInfo.LoanedTo);
         // set the fields...

     } else if (equipLoanInfo.LoanType == 'Contact') {
         // same as user, but with contact
     } // continue with other types...
}

If all that you are doing is using the SF Data Loader on the Equipment_Loan__c and all that you need to do is hookup values on a trigger (e.g., before insert, etc.) then you can change the above to use a Map of Equipment_Loan__c records instead of LoanInfo, e.g.,:

Map<Id, Equipment_Loan__c> equipmentToLoanInfo = Trigger.NewMap;
Set<Id> loanedToIds = new Set<Id>();
for(Id eId : equipmentToLoanInfo.keySet())
{      
    Equipment_Loan__c equipLoan = equipmentToLoanInfo.get(eId);

    // store off all of the Ids for later queries
    loanedToIds.add(equipLoan.LoanedTo);
}

// Basically the same code as when using Map<Id, LoanInfo>, 
// but change to use Equipment_Loan__c

The other type of situation that you can run into is where you may need to map a one to many. For example, if your LoanInfos within the same batch could share an EquipmentID, you might want to use processing such as the following.

Map<Id, List<LoanInfo>> equipmentToLoanInfos = new Map<Id, List<LoanInfo>>();
for (LoanInfo LI : LoanInfos) {
    if (equipmentToLoanInfos.get(LI.EquipmentID) == null) {
       equipmentToLoanInfos.put(LI.EquipmentID, new List<LoanInfo>());
    }
    equipmentToLoanInfos.get(LI.EquipmentID).add(LI);
}

Answer 2

In DataLoader (or other API tools) you can specify the batch size to be 30 ;) Means more round trips between the client and the server but this will make sure you stay within the limits. There's also similar setting in Excel Connector if you're using this to load data.

As for the actual root cause... I suspect the logic you're talking about is stored in some kind of trigger?

• The bad news: in pure trigger context the limit is 20 queries, not 100 (100 is available in Visualforce page's controller for example)
• The good news: yep, it's a well known problem with simple design pattern.

No matter what will the "batch size" in Data Loader (or other data source) be, triggers will start with fresh set of governor limits for every 200 records (100 if the trigger was saved with API version < 20).

Roughly speaking it will pay you off immensely to first learn which Users/Accounts/Contacts actually need to be queried for data, then issuing the actual query. You haven't provided any sample code so this thing is kind of best guess...

// assuming that imported "Equipment__c" record has a text field Username__c which contains '[email protected]' etc. data

List<Equipment__c> newLoad; // initialize with trigger.new, excel file content etc as you see fit.
Set<String> usernames = new Set<String>();
for(Equipment__c e : newLoad){
    userNames.add(e.Username__c );
}

// now we can simply make 1 query for all: [SELECT Id FROM User WHERE Username IN :usernames];
// but that'd be too easy ;)

Map<String, User> usernameToFullUserMap = new Map<String, User>();
for(User u : [SELECT Id, Username FROM User WHERE Username IN :usernames]){
    usernameToFullUserMap.put(u.Username, u);
}

// now we have retrieved detailed info about ALL users in the current context but we have wasted only 1 query!
// Time to actually use it back in original code.

for(Equipment__c e : newLoad){
    doSomeMagicWithEquipmentAndLendingUser(e, usernameToFullUserMap.get(e.Username__c));
}