Job taking too much time to complete - Pls suggest code optimization techniques

[bassasam]

Hi ,

We have a scheduled job which takes 3 days to complete.

Can anyone please suggest  code optimization techniques..

Code basically does the following :

• Glide through 5000 records on user table
• For every user record , delete a record on a custom table and create a fresh record on the same custom table with the information on user record.
• A few switch case statements are used in order to determine field values on the custom table
• Encoded queries have been used on all query statements.

[OlaN]

Hi,

Whoa! 3 days for that seems unlikely much. Are there a lot of cascade deleting of records happening when you delete records in that custom table?

Have you tried if the same slow progression occurs if you build a Flow that does the same?
By reviewing the Flow execution details you could at least identify what steps in your process that takes long time to complete.

[Sebastian L]

3 days?! Difficult to help you without posting the actual script. It sounds like something is very wrong somewhere hehe, don't know if you have business rules running or other logic depending on the custom table... Can be so many reasons! 🙂

One thing, is there a good reason for deleting one record, and then creating a new - instead of just updating it?

[Abhijit4]

Hi,

Please paste your code to review, updating and deleting 5k records should not take 3 days.

Thanks and Regards

Abhijit

[Hitoshi Ozawa]

Kind of difficult to provide code optimization technique without seeing the actual code.

Try to time each step. ServiceNow has an OCITimer() to measure time.

https://docs.servicenow.com/bundle/rome-application-development/page/app-store/dev_portal/API_refere...

Time these steps separately,

1. Time to do a query
2. Delete a record
3. Create a record

Some basic tips

1. Avoid nested GlideRecord queries. In other words, try not to nest while loops. This is usually the performance killer.
2. Try to filter records in a query instead of using if statements in a loop.
3. Use GlideAggregate instead of GlideRecord when just getting count of records
4. Avoid dot walking if possible. Dot walking is better than nested loops so dot walk if nesting loops is necessary.
5. Use .getValue() when possible to avoid fetching the entire record instead of just a column.
6. If records can be deleted all at once before the insert, try using .deleteMultiple()
7. When deleting and creating/updating a custom table and it is not necessary to execute business rule nor workflow, use ".setWorkflow(false).
8. Check if the queried columns are indexed.
9. Avoid encoded query if there's only 1 query condition. Just use .addQuery(). (This won't have too much performance impact though)