[SalesForce] SOQL Query Limit and Data Loading

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)
              }
         }
    }
  }
}