function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Matthew HamptonMatthew Hampton 

Non-selective query against large object type (more than 100,000 rows) Help Please!!

All:

 

I have the following trigger written:

 

trigger FiberUpdate on Fiber_Qualified_Address__c (after insert, after update) {
Set<String> AddressSet = new Set<String>();
Set<String> LocSet = new Set<String>();
Set<String> ZipSet = new Set<String>();
                    for(Fiber_Qualified_Address__c s: Trigger.New)
                             {
                                      AddressSet.add(s.street_address__c);
                                      LocSet.add(s.loc__c);
                                      ZipSet.add(s.zip_code__c);
                              }
                              
list<Subscriber__c> gpon = [select id, name, fiber_address__c, street_address__c, loc__c, zip_code__c from Subscriber__c where street_address__c IN: AddressSet
and loc__c IN: LocSet
and zip_code__c IN: ZipSet];

        if(gpon.size()>0) 
        { List<Subscriber__c> toUpdate=new List<Subscriber__c>(); 
        Map<ID, String> FQAID = new Map<ID, String>(); 
        for(Subscriber__c FQAID2: gpon) { Map<String, Subscriber__c> FQAMap = new Map<String, Subscriber__c>(); 
        for(Subscriber__c foa: gpon) { FQAMAP.put(foa.street_address__c + '' + foa.loc__c + '' + foa.zip_code__c, foa); } 
        for(Fiber_Qualified_Address__c s: Trigger.new) { if(FQAMAP.containsKey(s.street_address__c + '' + s.loc__c + '' + s.zip_code__c)) 
        { Subscriber__c cand = FQAMap.get(s.street_address__c + '' + s.loc__c + '' + s.zip_Code__c); 
        cand.fiber_address__c = s.id; toUpdate.add(cand); } } update toUpdate; }
}
}

 

and the following test class:

 

@isTest
private class testfiber{
static testmethod void testmy(){
Subscriber__c obj1 = new Subscriber__c();
obj1.street_address__c ='testadd';
obj1.loc__c = 'testloc';
obj1.zip_code__c = '87483';
insert obj1;

Fiber_Qualified_Address__c obj = new Fiber_Qualified_Address__c();
obj.street_address__c ='testadd';
obj.loc__c = 'testloc';
obj.zip_code__c = '87483';
insert obj;
}
}

 

I built them both in Sandbox, tested them out, acheived what I wanted, depoloyed to production, now I am getting an error:

 

System.QueryException: Non-selective query against large object type (more than 100,000 rows) . Consider an index filter.

 

Can anyone help me re-work the code or test coverage to avoid this error?

 

What I need the code to do is:

 

1. After update or insert of records in Fiber_Qualified_Address__c, take three fields (street_address__c, loc__c and zip_code__c) and lookup to see if there is a record in Subscriber__c that has the same three values in the same three fields.

 

2. If there is a match, create a lookup relationship in field Fiber_Address__c on Subscriber__c with Fiber_qualified_address__c

 

3. If there is no match, do nothing.

 

4. I would be inserting/updating anywhere from 100-1000 fiber qualified addresses at a time.

 

Thanks in advance for any help.

 

Matt

VPrakashVPrakash

Change your test class to

 

@isTest
private class testfiber{
static testmethod void testmy(){
Subscriber__c obj1 = new Subscriber__c();
obj1.street_address__c ='testadd';
obj1.loc__c = 'testloc';
obj1.zip_code__c = '87483';
insert obj1;

Fiber_Qualified_Address__c obj = new Fiber_Qualified_Address__c();
obj.street_address__c ='testadd1';
obj.loc__c = 'testloc1';
obj.zip_code__c = '874831';
insert obj;
}

Matthew HamptonMatthew Hampton

I made this change and received the same error when I ran the test class.

spraetzspraetz

When a table in Salesforce becomes larger than 100k rows, when querying the table using SOQL you will need to provide at least one indexed field in there WHERE clause of your query.

 

All objects come with two indexed fields: id and name.

 

If you need to make another field indexed, you have two options.  

 

1.) Mark a field as an External ID in the Custom Field Wizard.  Keep in mind that only certain field types can be External IDs (example: text, email).  Marking a field as an external ID automatically makes it indexed.  There is a limit to the number of External ID fields per object, so use them wisely.

 

2.) Call Salesforce support and ask for a custom index.  This should really only be used when option 1 absolutely will not work.  

 

One thing to note is that you cannot index a formula field.

 

Hope this helps!

Matthew HamptonMatthew Hampton

OK...so I was able to change one of my fields (Street_Address__c) to an external ID field...now the error I am getting is "Duplicate ID in list" at Line 23, Column 63.

 

I did some reading and found out that a way to get around this was to use map rather than list, however I have tried numerous changes of the code to get this to work with no such luck.

 

Can anyone please help?

 

Thanks,

 

Matt

sandipmsandipm