How to update Million record in PROD

[khareakshay]

I have a requirement where a custom field(u_sub_state_timestamp) should show the timestamp of the last change in another field(u_sub_state). For this I have created a business rule which updates the u_sub_state_timestamp with current date and time when the u_sub_state changes and it is working as expected. However I also need to update u_sub_state_timestamp field for older record by fetching the latest timestamp for u_sub_state from sys_audit table. The count of older records is close to a million. 

What is the safest approach to acheive this. 

[Tanushree Maiti]

Hi @khareakshay 

To update nearly a million records, avoid querying the sys_audit table directly from a script. Direct queries against sys_audit are not recommended because they can time out and may cause significant performance degradation when processing large data volumes.

Instead,

• use a Fix Script with GlideRecord and sys_history_line (if you get the your expected value from here), processing records in manageable batches.
•  Define the required time range in a system property (sys_properties) and retrieve it using gs.getProperty() so that the date criteria can be dynamically applied to the query.
• To minimize the impact on system performance, execute the backfill during a weekend or other business off-hours.

This approach is more scalable, configurable, and reduces the risk of performance issues while processing the backfill.

[Paer]

Also, have you thought about using metrics in SN, so that you can avoid custom fields?
That way you avoid increasing your technical debt.

[Ankur Bawiskar]

@khareakshay 

you can run fix script in chunks and use setLimit(10000) and also use autoSysFields(false) and setWorkflow(false) to avoid any BR triggering on update

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏