+ Start a Discussion
Abhishek Singh 88Abhishek Singh 88 

how to take SOQL query out of for loop.

Hi All,
I have query in for loop.I tried it to pull out side but it is not behaving properly. It is not updating respective record properly if take query out of loop.here is my code.
for(BMCServiceDesk__BMC_BaseElement__c berec:childCis)
        {       
             
               for(BMCServiceDesk__BMC_BaseRelationship__c brrec:sourcedestinationlist)
               {    
		            
                      if(brrec.BMCServiceDesk__Destination_ClassName__c=='BMC_Application')
						  
                          besourceid.add(brrec.BMCServiceDesk__Destination__c);
                   
               }
                   baseapplist=[select id,name,HITRUST__c,PCI__c,PHI__c,Fisma_High_New__c,Fisma_Low_New__c,Fisma_Mod_New__c,SOC__c,SOC2__c,SOX__c,Environment__c,UAR__c,BMCServiceDesk__ClassName__c from BMCServiceDesk__BMC_BaseElement__c where ID IN:besourceid];
			   
                   for(BMCServiceDesk__BMC_BaseElement__c baseapp:baseapplist)
                     {
                              if(baseapp.HITRUST__c)
                                  
                                  flag1=true;
                     }
				   for(BMCServiceDesk__BMC_BaseElement__c basebe:newbelist1)
                     {
						 
                        if(oldbelist1.get(basebe.id).HITRUST__c!=basebe.HITRUST__c && basebe.HITRUST__c==false)
                        {
                            if(flag1)
                            {
								
                              berec.HITRUST__c=true; 
                            }
                              else{
                                     berec.HITRUST__c=false;
                                }
                              baseelementlist.add(berec);								
		}}}

baseapplist is inside for loop. Want to keep outside of loop.
Thanks.
Best Answer chosen by Abhishek Singh 88
Deepali KulshresthaDeepali Kulshrestha
Hi Abhishek,
Please try the given below code with the help of this, you can solve your problem, it may be helpful to you.
for(BMCServiceDesk__BMC_BaseRelationship__c brrec:sourcedestinationlist){            
  if(brrec.BMCServiceDesk__Destination_ClassName__c=='BMC_Application')              
      besourceid.add(brrec.BMCServiceDesk__Destination__c);       
}

baseapplist=[select id,name,HITRUST__c,PCI__c,PHI__c,Fisma_High_New__c,Fisma_Low_New__c,Fisma_Mod_New__c,SOC__c,SOC2__c,SOX__c,Environment__c,UAR__c,BMCServiceDesk__ClassName__c from BMCServiceDesk__BMC_BaseElement__c where ID IN:besourceid];   
   
for(BMCServiceDesk__BMC_BaseElement__c berec:childCis) {        
   for(BMCServiceDesk__BMC_BaseElement__c baseapp:baseapplist){
        if(baseapp.HITRUST__c)
            flag1=true;
    }
    for(BMCServiceDesk__BMC_BaseElement__c basebe:newbelist1){
        if(oldbelist1.get(basebe.id).HITRUST__c!=basebe.HITRUST__c && basebe.HITRUST__c==false){
            if(flag1){
              berec.HITRUST__c=true; 
            }
            else{
                berec.HITRUST__c=false;
            }
             baseelementlist.add(berec);                                
        }
    }
}

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Deepali Kulshrestha

All Answers

Greg HGreg H
There is a lot that can be done to improve this logic. Not only should you be pulling the SOQL out of the for loop, you should be pulling out the for loop within a for loop logic. I highly recommend using as many Map or Set datatypes as it takes to ensure you are doing single loops.

It also looks like you only pasted a portion of the code here. It's really difficult to interpret why there are so many loops using the same BMCServiceDesk__BMC_BaseElement__c object. Furthermore, it's hard to provide an adequate replacement for the supplied snippet. Here's a start to get your ideas flowing:
//create a map or set to use in the second for loop
//first grab all the Ids you need for your query
for (BMCServiceDesk__BMC_BaseRelationship__c brrec : sourcedestinationlist) { //for each BMCServiceDesk__BMC_BaseRelationship__c record
    if (brrec.BMCServiceDesk__Destination_ClassName__c == 'BMC_Application') { //if BMCServiceDesk__BMC_BaseRelationship__c.BMCServiceDesk__Destination_ClassName__c is 'BMC_Application'
        besourceid.add(brrec.BMCServiceDesk__Destination__c); //add the BMCServiceDesk__BMC_BaseRelationship__c.BMCServiceDesk__Destination__c to the besourceid set
    }
}
//determine the records that should have flag1 set to true
for (BMCServiceDesk__BMC_BaseElement__c baseapp : [SELECT Id, HITRUST__c FROM BMCServiceDesk__BMC_BaseElement__c WHERE Id IN :besourceid]) { //query for BMCServiceDesk__BMC_BaseElement__c records related to the 
    if (baseapp.HITRUST__c) { //if BMCServiceDesk__BMC_BaseElement__c.HITRUST__c is TRUE
        //populate the map or set
    }
}
//now iterate over the list of BMCServiceDesk__BMC_BaseElement__c records to check before/after values and designate HITRUST__c values
for (BMCServiceDesk__BMC_BaseElement__c berec : childCis) {
    //needs work... but check the before and after values as well as the map/set used in the earlier for loop
}

Good luck.
-greg
Deepali KulshresthaDeepali Kulshrestha
Hi Abhishek,
Please try the given below code with the help of this, you can solve your problem, it may be helpful to you.
for(BMCServiceDesk__BMC_BaseRelationship__c brrec:sourcedestinationlist){            
  if(brrec.BMCServiceDesk__Destination_ClassName__c=='BMC_Application')              
      besourceid.add(brrec.BMCServiceDesk__Destination__c);       
}

baseapplist=[select id,name,HITRUST__c,PCI__c,PHI__c,Fisma_High_New__c,Fisma_Low_New__c,Fisma_Mod_New__c,SOC__c,SOC2__c,SOX__c,Environment__c,UAR__c,BMCServiceDesk__ClassName__c from BMCServiceDesk__BMC_BaseElement__c where ID IN:besourceid];   
   
for(BMCServiceDesk__BMC_BaseElement__c berec:childCis) {        
   for(BMCServiceDesk__BMC_BaseElement__c baseapp:baseapplist){
        if(baseapp.HITRUST__c)
            flag1=true;
    }
    for(BMCServiceDesk__BMC_BaseElement__c basebe:newbelist1){
        if(oldbelist1.get(basebe.id).HITRUST__c!=basebe.HITRUST__c && basebe.HITRUST__c==false){
            if(flag1){
              berec.HITRUST__c=true; 
            }
            else{
                berec.HITRUST__c=false;
            }
             baseelementlist.add(berec);                                
        }
    }
}

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Deepali Kulshrestha
This was selected as the best answer
Abhishek Singh 88Abhishek Singh 88
Both Answers were looked correct, I chose 2nd one.
Thanks.