+ Start a Discussion
ChrisMcLChrisMcL 

too many soql queries - batch mode on accounts - creative solutions?

I am doing something similar to Person Accounts in my Account trigger.
 
Using the dataloader, I get the above error message. I know it is due to this query being in my account trigger for loop:
 
contact[] contactMatch= [select Id, LastName, MailingStreet from Contact where LastName = :accountNameSearch and mailingStreet = :billingStreetSearch and (AccountId = :a.Id or AccountId = null)  limit 2];
 
This contact query searches for contacts that have the same name and address as of the account(s) that are being added. If I was just referring to the account Id, then I could use a Map and an in clause in my query. However, I also check for matches based on other variables: name and address.
 
My for loop constructs the variables :billingStreetSearch and :accountNameSearch are based off the account fields for each account.
 
To solve this, I will:
  1. I will store the 3 items: account Id's, name and address into 3 maps (unless there is a way to to store 3 items into a single map)
  2. I will use three IN clauses in my contact query
  3. I will create another account for loop to search for matches in the contacts result set.
I have ONE question:
  1. How do I search the contact result set efficiently? eg. for a given account Id, is there a match in my contact list? I don't want to look through a batch of 200+contacts for each account trigger. Can I sync up the two lists somehow? eg. "these contacts are really only to do with this account, don't bother looking at the others in this contact list."

thanks for any ideas!

James