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!

[Sudhanshu Talw1]

There is no difference. I was suggesting bcoz it will run by its own multiple times.

Option	Description
Proceed in Background	Use this option for long-running scripts, or if you do not know the expected execution time.
Proceed	Use this option to run the script immediately and display the results in a confirmation window.

 This is what servicenow said.

IDK there is some issue.

Although i have ran for 2-3 days.

Then you should use transaction quota rules:

https://docs.servicenow.com/bundle/jakarta-servicenow-platform/page/administer/platform-performance/...

[MrMuhammad]

Seems like the transaction quota is set to 5 mins only. Increase the transaction quote of Fix Script Processor as I mentioned above with visuals. Let set that to 28800 sec (8 hrs) in dev and run. 

Also make use of setLimit(1) in 2nd GlideRecord of u_table_2. This method is must have where applicable and has a significant performance boost. As in your case if a sinlge record found with specified queries in u_table_2 GR, you don't need to check rest of the records.

For example

without setLimit()

if record found in table 2 at first iteration, glideRecord will still look out all the table for further matches. 

with SetLimit(1)

As soon as first match found glideRecord will ignore further records and enter into the if condition. 

I hope that helps!

[mkader]

My fix script processor is set to 14,400 seconds 

[MrMuhammad]

Can you please share the latest script you are using? 

[mkader]

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.hasNext()){
        gr1.initialize();
        gr1.u_description = gr.u_description;
        gr1.u_short_description = gr.u_short_description;
        gr1.u_name= gr.u_name;
        gr1.insert();
    }
}