Best way to compare 2 tables deactive missing record with large data without impacting performance

[Raviteja Kunal1]

We have one table where data will be received from SAP and we need to compare data with another table and update it accordingly. Missing records need to be deactivated. Compare two tables and find the missing one. What is the best way to do this without impacting performance. Data will be around 1.5 lakh records.

Thank You

[Kris Moncada]

Had to look up what "lakh" stands for. 😃

Since I don't know any more particulars about you scenario (i.e. unique fields), here's one example of comparing the "SAP" table with Table 2.   

1. Create a object hash of Table 2. In this example, I am using some fields to create a unique property to be later referenced. The property could also be based on a unique field.
2. Next while iterating over the "SAP" table, we create a property using similar fields on the "SAP" table, and see if the property exists on the table2_obj. If it doesn't, then insert the record into Table_2.

var table2_obj = {};
var gr_table_2 = new GlideRecord('table_2');
gr_table_2.query();

while(gr_table_2.next()){
	var table2_property = gr_table_2.getValue('table_2_field_1') + gr_table_2.getValue('table_2_field_2');

	if(!table2_obj[table2_property]){
		table2_obj[table2_property] = gr_table_2.getUniqueValue();
	}

}



var gr_sap = new GlideRecord('table_1_sap');
gr_sap.query();

while(gr_sap.next()){
	var property = gr_sap.getValue('some_field_1') + gr_sap.getValue('some_field_2');

	if(!table2_obj[property]){
		// this record is missing on table 2.
		gr_table_2.initialize();
		gr_table_2.setValue('table_2_field_1', gr_sap.getValue('some_field_1'));
		gr_table_2.setValue('table_2_field_2', gr_sap.getValue('some_field_2'));
		gr_table_2.insert();
	}

}

Hope this helps.

[Raviteja Kunal1]

Hi @Kris Moncada

Thank You for the response.

Lakh means One hundred thousand records. To summarize I need to compare two tables data.