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.
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?
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
- Run an asynchronous import set with a large number of rows to import.
- 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
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