Optimize Comparison of Records of same object with different record types

Question

I have one object XYZ__c having two record types say A and B. I need to create junction Object JUNC__c(Fields: A__c(Lookup-XYZ__z),B__c(Lookup-XYZ__c)) record if XYZ__c record fields are same. I implemented as below,

List<XYZ__c> lstA =  [select id,f1__c,f2__c from XYZ__c where recordType.name ='A'];    
List<XYZ__c> lstB =  [select id,f1__c,f2__c from XYZ__c where recordType.name ='B'];    
List<JUNC__c> lstToBeInserted = new List<JUNC__c>();
    for(XYZ__c A:lstA){
         for(XYZ__c B: lstB){
            if(A.f1==B.f1 && A.f2==B.f2){
               lstToBeInserted.add(new JUNC__c(A__c=A.id,B__c=B.id));
            }
         }
    }
    if(lstToBeInserted.size()>0)
       insert lstToBeInserted;

It works. but i want a way to optimize it. what if i have thousands of records. it will take lot of time.Please help!

Answer 1

Here is a map-based solution. The trick is to combine all the values that you want to match into the map key:

Map<String, Id> rtas = new Map<String, Id>();
Map<String, Id> rtbs = new Map<String, Id>();
for (XYZ__c xyz : [select id,f1__c,f2__c from XYZ__c where recordType.name in ('A', 'B')]) {
    Map<String, Id> m = xyz.recordType.name == 'A' ? rtas : rtbs;
    String key = xyz.f1__c + xyz.f2__c;
    m.put(key, xyz.Id);
}

Set<String> commonKeys = new Set<String>(rtas.keySet());
commonKeys.retainAll(rtbs.keySet());

if (commonKeys.size() > 0) {
    List<JUNC__c> inserts = new List<JUNC__c>();
    for (String key : commonKeys) {
        inserts.add(new JUNC__c(
                A__c = rtas.get(key),
                B__c = rtbs.get(key)
                ));
    }
    insert inserts;
}

PS

The approach of using a custom key class (to handle the order dependent and relative fields - see the comments below) would look much the same:

Map<Key, Id> rtas = new Map<Key, Id>();
Map<Key, Id> rtbs = new Map<Key, Id>();
for (XYZ__c xyz : [
        select id, f1__c, f2__c, f3__c, f4__c
        from XYZ__c
        where recordType.name in ('A', 'B')
        ]) {
    Map<String, Id> m = xyz.recordType.name == 'A' ? rtas : rtbs;
    m.put(new Key(xyz), xyz.Id);
}

Set<Key> commonKeys = new Set<Key>(rtas.keySet());
commonKeys.retainAll(rtbs.keySet());

if (commonKeys.size() > 0) {
    List<JUNC__c> inserts = new List<JUNC__c>();
    for (Key key : commonKeys) {
        inserts.add(new JUNC__c(
                A__c = rtas.get(key),
                B__c = rtbs.get(key)
                ));
    }
    insert inserts;
}

with the tricky stuff in the Key class:

public class Key {

    private XYZ__c xyz;
    private String t;
    private Integer hashCode;

    public Key(XYZ__c xyz) {
        this.xyz = xyz;
        this.t = xyz.recordType.name;
    }

    public Boolean equals(Object o) {
        if (o instanceof Key) {
            XYZ__c a = this.xyz;
            XYZ__c b = ((Key) o).xyz;
            if (a.f1__c != b.f1__c) return false;
            if (a.f2__c != b.f2__c) return false;
            if (a.t == b.t) {
               // Same record type, need to be identical
               if (a.f3__c != b.f3__c) return false;
               if (a.f4__c != b.f4__c) return false;
               return true;
            } else if (a.t == 'A') {
               // Record type 'A' and 'B'
               if (!a.f3__c.contains(b.f3__c)) return false;
               if (!(a.f4__c <= b.f4__c)) return false;
               return true;
            } else {
               // Record type 'B' and 'A'
               if (!b.f3__c.contains(a.f3__c)) return false;
               if (!(b.f4__c <= a.f4__c)) return false;
               return true;
            }
        } else {
            return false;
        }
    }

    // Can't include order dependent or comparison fields here as that would put
    // data in different hash buckets so matches couldn't be found
    public Integer hashCode() {
        if (hashCode == null) {
            hashCode = (String.valueOf(xyz.f1__c) + String.valueOf(xyz.f2__c)).hashCode();
        }
        return hashCode;
    }
}

This would need a good set of unit tests to be sure it worked.