Governor Limit Hit ( Issue with code inside the for loop)

Question

I created a trigger to call a apex code (Before insert, Before update). The process is basically initiated when a referral record is created, i used some fields from referrals to do a look up for a ID on another object address and address has million rows. There are multiple criteria's to bring the correct address loop up Id.

The code breaks even if i insert one record. Currently, when i have limit the search to one row, the apex code given me the write answer but as soon as i remove that static value, the apex code gives me an error for governor limit -101

Can someone please help me? I am a new to apex and I am unable to understand how to make the iteration efficient.

Code:

public class CmaReferrals { 
    public static void run () { 
        updateUsingAddress(); 
    }

    public static void updateUsingAddress() {        
        Referral__c [] listRef = [ select ID, street_name__c, street_number__C, street_type__c, Apartment_Unit_Number__c, 
                            City__c, Zip_Postal_Code__c from Referral__C 
                                where
                                STREET_NAME__c = 'lisgar' //(static value) (as soon as i remove this line, the query breaks)
                                and ADDREsS__C = null
                                and STREET_NAME__c !=  null
                                and street_number__c!= Null
                                and (Referral_Type__c = 'Marketing Alliance'
                                or  Referral_Type__c = 'New Construction'      
                                or  Referral_Type__c = 'Placeholder 3') ];  
    
        List<referral__c> refListToUpdate = new List<referral__c>(); 
        
        for(Referral__c row:listRef)
        {
            if (row.city__C != null && row.Zip_Postal_Code__c!= null)
            {
                address__c [] addressId = [ SELECT Id, City__c, Postal_Zip_Code__c, SAM_KEY__c  FROM  ADDRESS__C 
                                            WHERE Street_name__c = :row.Street_Name__c  
                                            and Street_number__c = :row.Street_Number__c 
                                            and Street_type__C = :row.Street_Type__c
                                            and ApartNum__c = :row.Apartment_Unit_Number__c
                                            and City__c =  :row.City__c
                                            and Postal_Zip_Code__c= :row.Zip_Postal_Code__c
                                            and (ADDR_COMPANY__c Like '2%'
                                            or   ADDR_COMPANY__c Like '1%')
                                            and SAM_KEY__c Like '2%'];
                if (addressId == null || addressId.size() < 1)
                {
                    address__c [] addressIdPost = [ SELECT Id, City__c, Postal_Zip_Code__c, SAM_KEY__c FROM  ADDRESS__C 
                                                    WHERE Street_name__c = :row.Street_Name__c  
                                                    AND Street_number__c = :row.Street_Number__c 
                                                    and Street_type__C = :row.Street_Type__c
                                                    and ApartNum__c = :row.Apartment_Unit_Number__c
                                                    and City__c =  :row.City__c
                                                    and (ADDR_COMPANY__c Like '2%'
                                                    or   ADDR_COMPANY__c Like '1%')
                                                    and SAM_KEY__c Like '2%'];
                    if(addressIdPost != null && addressIdPost.size() > 0)
                    {
                        refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressIdPost[0].id, 
                                                            City__c = addressIdPost[0].City__c, 
                                                            Zip_Postal_Code__c = addressIdPost[0].Postal_Zip_Code__c,
                                                            SAM_Key__c = addressIdPost[0].SAM_Key__c ));
                    }   
                    
                } Else
                {
                    if(addressId != null && addressId.size() > 0)
                    {
                        refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressId[0].id, 
                                                            City__c = addressId[0].City__c, 
                                                            Zip_Postal_Code__c = addressId[0].Postal_Zip_Code__c,
                                                            SAM_Key__c = addressId[0].SAM_Key__c ));
                    }
                }      
            } Else if(row.city__C != null)
            {
                address__c [] addressId = [SELECT Id, City__c, Postal_Zip_Code__c, SAM_KEY__c
                                            FROM  ADDRESS__C 
                                            WHERE Street_name__c = :row.Street_Name__c  
                                            and Street_number__c = :row.Street_Number__c 
                                            and Street_type__C = :row.Street_Type__c
                                            and ApartNum__c = :row.Apartment_Unit_Number__c
                                            and city__C =  :row.City__c 
                                            and (ADDR_COMPANY__c Like '2%'
                                            or   ADDR_COMPANY__c Like '1%')
                                            and SAM_KEY__c Like '2%'];
                
                if (addressId == null || addressId.size()<1)
                {
                    address__c [] addressIdCity = [ SELECT Id,  City__c, Postal_Zip_Code__c, SAM_KEY__c FROM  ADDRESS__C 
                                                    WHERE Street_name__c = :row.Street_Name__c  
                                                    and Street_number__c = :row.Street_Number__c 
                                                    and Street_type__C = :row.Street_Type__c 
                                                    and city__C =  :row.City__c
                                                    and ApartNum__c = :row.Apartment_Unit_Number__c
                                                    and (ADDR_COMPANY__c Like '2%'
                                                    or   ADDR_COMPANY__c Like '1%')
                                                    and SAM_KEY__c Like '2%']; 
                    if(addressIdCity != null && addressIdCity.size() > 0)
                    {
                        refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressIdCity[0].id,
                                                            City__c = addressIdCity[0].City__c, 
                                                            Zip_Postal_Code__c = addressIdCity[0].Postal_Zip_Code__c,
                                                            SAM_Key__c = addressIdCity[0].SAM_Key__c ));
                    }   
                } Else 
                {
                    if(addressId != null && addressId.size() > 0)
                    {
                        refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressId[0].id, 
                                                            City__c = addressId[0].City__c, 
                                                            Zip_Postal_Code__c = addressId[0].Postal_Zip_Code__c,
                                                            SAM_Key__c = addressId[0].SAM_Key__c ));
                    } 
                }   
        } Else
        { 
                address__c [] addressId = [SELECT Id, City__c, Postal_Zip_Code__c, SAM_KEY__c FROM  ADDRESS__C 
                                            WHERE Street_name__c = :row.Street_Name__c  
                                            and Street_number__c = :row.Street_Number__c 
                                            and Street_type__C = :row.Street_Type__c
                                            and ApartNum__c = :row.Apartment_Unit_Number__c
                                            and (ADDR_COMPANY__c Like '2%'
                                            or   ADDR_COMPANY__c Like '1%')
                                            and SAM_KEY__c Like '2%'];
                if(addressId != null && addressId.size() > 0)
                {
                    refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressId[0].id,
                                                        City__c = addressId[0].City__c, 
                                                        Zip_Postal_Code__c = addressId[0].Postal_Zip_Code__c,
                                                        SAM_Key__c = addressId[0].SAM_Key__c ));
                }                              
        }
    }
    
    update refListToUpdate;
}

Trigger:

trigger CmaReferralTriggers on Referral__c (after insert, after update) { 
    CmaReferrals.run();
}

Answer 1

My 2 cents: You are looking to associate address record to referral record based on the city and postal code of the incoming referral data.

In most of your SOQL's you are using city,postal code or static values like below to

query
and (ADDR_COMPANY__c Like '2%'or   ADDR_COMPANY__c Like '1%')
and SAM_KEY__c Like '2%'

1. You can try to create Map of records with the same Postal code and city combination and combine them and try to associate address records for them using same SOQL
2. Use limit 1 when you query on SOQL since you are only taking the first record

If this is a frequent bulk insert requirement for referrals then i would look at batch classes and use batch size to meet this requirement.

The current way of doing it is not so efficient in my opinion.

If you still insist doing it this way and the above suggestions doesn't help. An another way to do it is to create chunks of 50 records and trigger queueable so that the same 200 records is split into 4 chunks and you will get more asynchronous limits to meet your requirement.