+ Start a Discussion
webuser.ax1109webuser.ax1109 

Limit Exception - How to tackle this?

Hi all,

 

       I got stuck with the limit exception error in Salesforce. I'll explain the situation first, we are trying to copy about quarter million products from a custom object to Standard Product Object in salesforce and in the mean time we are also creating entries in PricebookEntry object. We want to do both these tasks using a Single Batch Apex.

 

I've tried the following the methods for solving this problem

 

1) Wrote a Batch Apex which has following things written in the execute method

 

    a) Loop which Upserts Products to Standard Product Object from Custom Object

    b) Loop through the Standard Product Object and create pricebook entries in a seperate loop.

 

Result : Got exception "Too many queries"

 

2) Wrote a Batch Apex with a single loop, instead of adding them to a list and then adding it by batch, I've tried to upsert products to Standard Product Object one by one also creating pricebook entries at the same time

 

Result : Got exception "Too many DML Statements"

 

 

What would be the best methodology to use in this situation?

 

I'm also adding my code snippets with this,

 

Batch Apex with two loops

 

global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  //Upserting products from CB_Products to Product2
 
      productstoupload = new List<Product2>();      
      for(sObject s : scope){
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;           
      productstoupload.add(prod);                      
    }       
    upsert productstoupload SF_ID__c;       
   
  //Upserting entries from Product2 to PricebookEntry
 
    //pricebookupdate = new List<PricebookEntry>();
    pricebookinsert = new List<PricebookEntry>(); 
   
 
   
    List<sObject> batch = [Select Id, Price__c, Availability_Status__c FROM Product2];
       
     for(sObject s : batch){
    
         Product2 prod = (Product2)s;               
         PricebookEntry pbe = new PricebookEntry();     
         pbe.Pricebook2Id=pricebook.ID;       
         pbe.Product2Id=prod.Id;
         pbe.UnitPrice=prod.Price__c;
        
         //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.
                
         if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
         pbe.IsActive= false;
         }                
         else{        
         pbe.IsActive= true;        
         }
        
         pbe.UseStandardPrice=false;
        
        //Checking whether there is a Pricebook Entry exist for a particular product
     
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
                    
       //If there is no existing entry then we will insert a new Pricebook Entry
                    
          if(exist.isEmpty()){
          pricebookinsert.add(pbe);
          }
         
       //Else we will update the existing entry
         
          else{      
          //pricebookupdate.add(pbe);
         
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
          if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
          pbupd.IsActive=false;
          }
          else{
          pbupd.IsActive=true;
          }
          pbupd.UnitPrice=prod.Price__c;
          update pbupd;         
         
         
          }
         
    } 
   
    insert pricebookinsert;
   
   // update pricebookupdate;
   
  }

 

Batch Apex with Single Loop:

 

 global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  /
/Upserting products from CB_Products to Product2
 
      for(sObject s : scope){
     
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();     
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;                 
      upsert prod SF_ID__c;
    
          PricebookEntry pbe = new PricebookEntry();     
          pbe.Pricebook2Id=pricebook.ID;       
          pbe.Product2Id=prod.Id;
          pbe.UnitPrice=prod.Price__c;
     
      //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.
                
          if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
          pbe.IsActive= false;
          }                
          else{        
          pbe.IsActive= true;        
          }               
         pbe.UseStandardPrice=false;
        
        
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
        
         if(exist.isEmpty()){
         insert pbe;
         //pricebookinsert.add(pbe);
         }
        
         else{                    
         
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
         
              if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
              pbupd.IsActive=false;
              }
              else{
              pbupd.IsActive=true;
              }
         
              pbupd.UnitPrice=prod.Price__c;
              update pbupd;        
         }
    }       
   
    //insert pricebookinsert;                  
 
  }

 

 

 

It would be a great help, if someone could provide any help on this issue.

 

Many Thanks,

 

Joe

 

pankaj.raijadepankaj.raijade

try following code. This is optimized for to handle the limits.

Please vertfy the logic 

 

global void execute(Database.BatchableContext bc, List<sObject> scope)

{

//Upserting products from CB_Products to Product2    productstoupload = new List<Product2>();          

for(sObject s : scope)

{

CB_Products__c cbp = (CB_Products__c)s;

Product2 prod = new Product2();

// FIELD 'MAPPINGS':

prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;

prod.Name                    = cbp.Product_Name__c;

prod.Price__c                = cbp.UK_List_Price__c;

prod.Manufacturer__c         = cbp.Manufacturer__c;

prod.Availability_Status__c  = cbp.Availability_Status__c;

prod.Supplier_Code__c        = cbp.Supplier_Code__c;

prod.Size__c                 = cbp.Size__c;

prod.UOM__c                  = cbp.UOM__c;

prod.IsActive                = true;prod.SF_ID__c                = cbp.Id;

productstoupload.add(prod);

}

upsert productstoupload SF_ID__c;

         //Upserting entries from Product2 to PricebookEntry

//pricebookupdate = new List<PricebookEntry>();   

pricebookinsert = new List<PricebookEntry>();       

map<id, sObject> mapbatch = new map([Select Id, Price__c, Availability_Status__c FROM Product2]);    List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id IN :mapbatch.KeySet()];   map<id, List<PricebookEntry>> mapexist = new map<id, List<PricebookEntry>>();

for(PricebookEntry oPricebookEntry: exist)

{

if(mapexist.containsKey(oPricebookEntry.Product2Id))

mapexist.get(oPricebookEntry.Product2Id).add(oPricebookEntry);

else

mapexist.put(oPricebookEntry.Product2Id, new list<PricebookEntry>{oPricebookEntry});

}

set<id> setexist2 = new set<id>();

for(sObject s : batch)

{

if(!mapexist.containskey(s.Id))

{

Product2 prod = (Product2)s;

PricebookEntry pbe = new PricebookEntry();

pbe.Pricebook2Id=pricebook.ID;

pbe.Product2Id=prod.Id;pbe.UnitPrice=prod.Price__c;

        //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.              if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA')

pbe.IsActive= false;

else

pbe.IsActive= true;

pbe.UseStandardPrice=false;

//Checking whether there is a Pricebook Entry exist for a particular product

//If there is no existing entry then we will insert a new Pricebook Entry                 pricebookinsert.add(pbe);

}

//Else we will update the existing entry

        else

{

list<PricebookEntry> lstpbupd = mapexist.get(s.Id);

for(PricebookEntry opbupd:lstpbupd)

{

if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA')

pbupd.IsActive=false;

else

pbupd.IsActive=true;

pbupd.UnitPrice=prod.Price__c;

pricebookinsert.add(pbupd);

}

}

}

upsert pricebookinsert; 

} 

webuser.ax1109webuser.ax1109

Hi Pankaj,

 

   Thanks for your reply. I've tried your code and it includes some errors which are syntactical error as well as logical errors. I've tried to correct the code with my limited knowledge (I'm not a Apex Developer, basically I'm a PHP Web Developer). But still I'm getting the following error while saving the code 

 

"Error: Compile Error: Initial term of field expression must be a concrete SObject: LIST<PricebookEntry> at line 146 column 1"

 

could you have a look at my code and see whether you can fix it? Many thanks in advance for your help.

 

global void execute(Database.BatchableContext bc, List<sObject> scope)

{

//Upserting products from CB_Products to Product2    productstoupload = new List<Product2>();         

for(sObject s : scope)

{

CB_Products__c cbp = (CB_Products__c)s;

Product2 prod = new Product2();

// FIELD 'MAPPINGS':

prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;

prod.Name                    = cbp.Product_Name__c;

prod.Price__c                = cbp.UK_List_Price__c;

prod.Manufacturer__c         = cbp.Manufacturer__c;

prod.Availability_Status__c  = cbp.Availability_Status__c;

prod.Supplier_Code__c        = cbp.Supplier_Code__c;

prod.Size__c                 = cbp.Size__c;

prod.UOM__c                  = cbp.UOM__c;

prod.IsActive                = true;prod.SF_ID__c                = cbp.Id;

productstoupload.add(prod);

}

upsert productstoupload SF_ID__c;

//Upserting entries from Product2 to PricebookEntry

//pricebookupdate = new List<PricebookEntry>();  

pricebookinsert = new List<PricebookEntry>();      

//map<id, sObject> mapbatch = new map<id, sObject>([Select Id, Price__c, Availability_Status__c FROM Product2]);   

Map<Id, ID> mapbatch = new Map<Id, Id>();
for(Product2 sl:[Select Id, Price__c, Availability_Status__c FROM Product2])
mapbatch.put(sl.Id,sl.id);

List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id IN :mapbatch.KeySet()];  

map<id, List<PricebookEntry>> mapexist = new map<id, List<PricebookEntry>>();

for(PricebookEntry oPricebookEntry: exist)

{

if(mapexist.containsKey(oPricebookEntry.Product2Id))

mapexist.get(oPricebookEntry.Product2Id).add(oPricebookEntry);

else

mapexist.put(oPricebookEntry.Product2Id, new list<PricebookEntry>{oPricebookEntry});

}

set<id> setexist2 = new set<id>();
List<sObject> batch = [Select Id, Price__c, Availability_Status__c FROM Product2];
for(sObject s : batch)

{
Product2 prod = (Product2)s;
if(!mapexist.containskey(s.Id))

{



PricebookEntry pbe = new PricebookEntry();

pbe.Pricebook2Id=pricebook.ID;

pbe.Product2Id=prod.Id;pbe.UnitPrice=prod.Price__c;

        //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.             
if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA')

pbe.IsActive= false;

else

pbe.IsActive= true;

pbe.UseStandardPrice=false;

//Checking whether there is a Pricebook Entry exist for a particular product

//If there is no existing entry then we will insert a new Pricebook Entry                 pricebookinsert.add(pbe);

}

//Else we will update the existing entry

        else

{

list<PricebookEntry> lstpbupd = mapexist.get(s.Id);

for(PricebookEntry opbupd:lstpbupd)

{

if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA')

lstpbupd.IsActive=false;

else

lstpbupd.IsActive=true;

lstpbupd.UnitPrice=prod.Price__c;

pricebookinsert.add(lstpbupd);

}

}

}

upsert pricebookinsert;

} 

pankaj.raijadepankaj.raijade

Which is the line no 146? I am not able to identify the exact line.