Optimize fix script to run more efficiently

[mkader]

Hello,

I have a fix script that copies records from one table and inserts to another table. There are over 500,000 records being copied over. The fix script works fine, however, when I run the query it takes a very long time to run and will often timeout. In order for me to fully execute the fix script, I must run it several times. This is just a one off so we want to do this using a fix script. Below is the script I have written. 

var gr = new GlideRecord('u_table_1');
gr.query();
while (gr.next()){
    var gr1 = new GlideRecord('u_table_2');
    gr1.addQuery('u_description',gr.u_description);
    gr1.addQuery('u_short_description',gr.u_short_description);
    gr1.addQuery('u_name',gr.u_name);
    gr1.query();
    if(gr1.next())
{
//do nothing
}
//if not found then insert
else{
    gr1.initialize();
    gr1.u_description = gr.u_description;
    gr1.u_short_description = gr.u_short_description;
    gr1.u_name= gr.u_name;
    gr1.insert();
}
}

Is there maybe a way to segment the fix script so that it runs and completes much quicker?

Thanks!

[MrMuhammad · Accepted solution]

Try this. 

var gr = new GlideRecord('u_table_1');
gr.query();

while (gr.next()){
    var gr1 = new GlideRecord('u_table_2');
    gr1.addQuery('u_description',gr.u_description);
    gr1.addQuery('u_short_description',gr.u_short_description);
    gr1.addQuery('u_name',gr.u_name);
    gr1.setLimit(1);
    gr1.query();

    if(!gr1.hasNext()){
        gr1.initialize();
        gr1.u_description = gr.u_description;
        gr1.u_short_description = gr.u_short_description;
        gr1.u_name= gr.u_name;
        gr1.setWorkflow(false); //stop execution of Business rule or other scripts.
        gr1.insert();
    }
}

If it stops in 5 mins then try running in background script then check logs if it throws any other issues. Also, can you confirm - both the tables are in same scope? 

[mkader]

Okay so I tested this and it works. My issue now is there are 3-400 records missing, but the transaction shows as completed. What can be the cause of those missing records? Are there duplicates from the source table? Not really sure how to track this information down since there are so many records. 

Is it possible to create a filter on a table shows only the duplicate records?

[MrMuhammad]

If above responses helped you in anyway please mark them as helpful. 

It would be hard to apply filters to find duplicates. You can try groupBy on column header in the list view of source table but groupBy works for single column only. To find duplicates based on multiple fields data you need to write a script. 

Checkout below link for sample codes.

https://www.servicenowelite.com/blog/2013/11/22/duplicate-record-scripts

Thanks & Regards,

Sharjeel