SLAAsyncQueueSNC script include retrieving Large Result Set from sys_audit table - Known Error

SLAAsyncQueueSNC script include retrieving Large Result Set from sys_audit table Description We are observing performance degradation caused by the SLAAsyncQueueSNC script include.

SLAAsyncQueueSNC retrieves a large result from sys_audit table, causing a warning:

QueryWarning *** WARNING *** Large Table: Table handling an extremely large result set: 34667 Query that got us here is: TABLENAME = sys_audit ENCODED_QUERY = tablename=ticket^documentkey=e7fdb2d12b8643106688f048ce91bf5d^ORDERBYDESCinternal_checkpoint^GROUPBYinternal_checkpoint

Steps to Reproduce

Update any task record with > 10K unique updates, the description example shows: 34,667 Note that this is far from being a recommendation, it is simply a consequence of so either runaway process or a very long running task with automatic updates. Wait or force sys_audit extended type sharded table make new shards, so that getting the latest checkpoint requires a cross shard union query.

Workaround Modify script include: SLAAsyncQueue adding two functions as below:

queueTask: function(gr) { if (!gr || !gr.isValidRecord()) { this.lu.logError("queueTask: no record supplied"); return; } if (this.lu.atLevel(GSLog.DEBUG)) this.lu.logDebug("queueTask: adding " + gr.sys_meta.label + " " + gr.getDisplayValue() + " to queue for update number " + gr.getValue("sys_mod_count")); var sysCreatedOn = gr.getValue("sys_created_on"); var modCount = gr.getValue("sys_mod_count"); var internalCheckpoint = this._getLatestCheckpoint(gr.getTableName(), gr.getUniqueValue(), sysCreatedOn); internalCheckpoint = internalCheckpoint === -1 && modCount + '' === '0' ? 0 : internalCheckpoint; if (this.lu.atLevel(GSLog.DEBUG)) this.lu.logDebug("queueTask: internalCheckpoint " + internalCheckpoint + " modCount: " + modCount); var slaQueueGr = new GlideRecord(SLAAsyncSNC.SLA_ASYNC_QUEUE); slaQueueGr.setValue("document_table", gr.getRecordClassName()); slaQueueGr.setValue("document_id", gr.getUniqueValue()); slaQueueGr.setValue("update_number", modCount); slaQueueGr.setValue("internal_checkpoint", internalCheckpoint); var sysId = slaQueueGr.insert(); if (!sysId) { this.lu.logError("queueTask: failed to queue entry for record " + gr.sys_meta.label + " " + gr.getDisplayValue() + " at update number " + gr.getValue("sys_mod_count")); return; } if (this.lu.atLevel(GSLog.DEBUG)) this.lu.logDebug("queueTask: added entry to queue with sequence number " + slaQueueGr.getValue("sequence")); }, _getLatestCheckpoint: function(tableName, sysId, sysCreatedOn) { var checkpoint = -1; if (!tableName || !sysId) return checkpoint; var gr = new GlideRecord('sys_audit'); gr.addQuery('tablename', tableName); gr.addQuery('documentkey', sysId); // Windowing sys_audit shards with sys_created_on >= task.sys_created_on // cuts the number of SQL unions -- shards before the task's creation // can't hold data for it. Relies on sys_created_on not being // future-dated (e.g. via autoSysFields(false)). if (sysCreatedOn) gr.addQuery('sys_created_on', '>=', sysCreatedOn); gr.setWorkflow(false); gr.orderByDesc('internal_checkpoint'); gr.setLimit(1); gr.query(); if (gr.next()) checkpoint = gr.getValue('internal_checkpoint') + ''; return checkpoint || -1; },