"SC Popular Items" sc-popular-items widget causes excessive queries


Description

The OOB "sc-popular-items" widget relies on an aggregate query of the live sc_req_item table, with the code like the following:

var count = new GlideAggregate('sc_req_item');
count.addAggregate('COUNT','cat_item');
count.groupBy('cat_item');
count.addQuery('cat_item.sys_class_name', 'NOT IN', 'sc_cat_item_guide,sc_cat_item_wizard,sc_cat_item_content');
count.addQuery('cat_item.sc_catalogs', 'IN', data.sc_catalog);
count.orderByAggregate('COUNT', 'cat_item');
count.query();
while (count.next() && items.length < data.TopItemslimit) {
if (!$sp.canReadRecord("sc_cat_item", count.cat_item.sys_id.getDisplayValue()))
continue; // user does not have permission to see this item
var item = {};
item.count = count.getAggregate('COUNT', 'cat_item');
item.name = count.cat_item.name.getDisplayValue();
item.sys_id = count.cat_item.sys_id.getDisplayValue();
items.push(item);
}

This runs every time someone loads or reloads a page that contains the "sc-popular-items" widget. For large scale Service Catalogs this query can become very impactful on the database. A re-design is suggested in the article Speed up the "SC Popular Items" Service Portal Widget by using a snapshot possibly resulting in a much faster user experience and reduction of overall load on the database.

Another hypothetical solution would be to leverage the Platform Analytics application to store aggregate trend data to use as the source of the widget.

Steps to Reproduce

1. Clone the out-of-the-box widget "SC Popular Items" widget.
2. Use the widget in a Service Portal page that is frequently accessed.
3. Have enough tasks and sc_req_item records in the system to make the queries slow.

Workaround

This problem is under review and targeted to be fixed in a future release. To receive notifications when more information becomes available, subscribe to this Known Error article by clicking the Subscribe button at the top right of this form.

The indexes listed below are needed for the widget to work. During the upgrade indexes might fail to be applied if your instance reaches the maximum number of indexes on TASK. In that case, open a Case for SN Technical Support, to move from 64 to 128 indexes and create the required indexes:


TABLEĀ INDEX
sc_req_item
  • sys_class_name, cat_item, sys_created_on
  • sys_class_name, cat_item, order_guide
sc_item_produced_record
  • producer, sys_created_on

Related Problem: PRB1386320