617 views

Description

When importing data in an asynchronous import set, we call the "Set synchronous import set" Business Rule to populate the "sys_import_row" field. The "sys_import_row" is a sequential number which allows us to order the data numerically.  The query that is generated by this Business Rule can be slow (consuming large amounts of processing time on the database) for import sets with large numbers of rows. 

Potential Symptoms
           1. Slow performance/throughput of synchronous import sets
           2. Semaphore exhaustion (most likely in the API_INT semaphore set)

How to validate the slow query?

Check for a slow query patter record against sys_import_set_row: https://<instance_name>.service-now.com/sys_query_pattern_list.do?sysparm_query=exampleLIKEsys_import_set_row%5EexampleLIKEORDER%20BY%20sys_import_set_row0.%60sys_import_row%60%20DESC%20%20limit%200%2C1

What log messages will I see?

2017-06-25 22:36:52 (755) API_INT-thread-4 AED1C8D24FA7F6800E20650F0310C7FD Time: 0:00:28.286 id: test_1[glide.5] for:  /* test001, gs:AED1C8D24FA7F6800E20650F0310C7FD, tx:26d1c8d24fa7f6800e20650f0310c7fe */ SELECT u_bill_to_customer0.`sys_id` FROM (u_bill_to_customer u_bill_to_customer0  INNER JOIN sys_import_set_row sys_import_set_row0 ON u_bill_to_customer0.`sys_id` = sys_import_set_row0.`sys_id` )  ORDER BY sys_import_set_row0.`sys_import_row` DESC  limit 0,1
2017-06-25 22:36:52 (756) API_INT-thread-4 AED1C8D24FA7F6800E20650F0310C7FD *** Script: 218980
2017-06-25 22:36:52 (757) API_INT-thread-4 AED1C8D24FA7F6800E20650F0310C7FD Slow business rule 'Set synchronous import set' on u_bill_to_customer:, time was: 0:00:28.289

Note: "u_bill_to_customer" is the table that is being imported into so this will be unique per import/per instance.

Steps to Reproduce

 

  1. Run an asynchronous import set with a large number of rows to import.
  2. While the import runs, the slow query referenced above appears in the logs:

2017-06-25 22:36:52 (755) API_INT-thread-4 AED1C8D24FA7F6800E20650F0310C7FD Time: 0:00:28.286 id: test_1[glide.5] for:  /* test001, gs:AED1C8D24FA7F6800E20650F0310C7FD, tx:26d1c8d24fa7f6800e20650f0310c7fe */ SELECT u_bill_to_customer0.`sys_id` FROM (u_bill_to_customer u_bill_to_customer0  INNER JOIN sys_import_set_row sys_import_set_row0 ON u_bill_to_customer0.`sys_id` = sys_import_set_row0.`sys_id` )  ORDER BY sys_import_set_row0.`sys_import_row` DESC  limit 0,1

Workaround

Import the latest version of the Business Rule (see attachment sys_script_99c8911ac0a8006f00395bca69c1a922.xml). This uses the existing sys_import_set index on the sys_import_set_row ( sys_import_set, sys_import_state, sys_import_row ) columns.


Related Problem: PRB1073350

Seen In

There is no data to report.

Fixed In

Kingston

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-08-26 03:30:09
Published:2017-10-03