Speed up the "Recent & Popular Items" Service Portal Widget by using a snapshotIssue The "Recent & Popular Items" widget is introduced when the Service Portal - Service Catalog v2 plugin is activated. Plugin New widget added Customers then have the option to swap out the v1 SC Popular Items widget with the new v2 version. When users navigate to the Service Catalog home page in the Service Portal, it is possible to see Popular Items. However, in the new (v2) version of the widget, users are also able to see past requests in a tabbed display. Refer to the screenshot below Version 1 widget Version 2 widget Popularity in v1 is based on the count of related "Requested Item" [sc_req_item] records created from those catalog items. For v2 additional factors are taken into consideration as well. Version 2 also takes account of items a user does not have the right to view based on security. This, out of the box, adds more complexity to the query generated to retrieve these items. As the number of Requested Item records increases, the cost of calculating the top popular items becomes more expensive. This causes expensive queries in the database. This increases the response time of the widget / portal page. This can also impact semaphore usage/contention and even lead to semaphore exhaustion. CauseThe slowness is due to the expensive aggregation in the widget server script. The New York version of this widget has the following code: This results in a query pattern similar to this: SELECT sc_cat_item1.`sys_id` AS `cat_item_sys_id`, count(*) AS count_of_45033948 FROM ((task task0 LEFT JOIN sc_cat_item sc_cat_item1 ON task0.`a_ref_1` = sc_cat_item1.`sys_id` ) LEFT JOIN sys_metadata sys_metadata2 ON sc_cat_item1.`sys_id` = sys_metadata2.`sys_id` ) WHERE task0.`sys_class_name` = ? AND sys_metadata2.`sys_class_name` NOT IN ? AND (task0.`a_ref_1` IN ? The version 1 query looked liked this: SELECT task0.`a_ref_1` AS `cat_item`, count(*) AS count_of_45033948 FROM ((task task0 LEFT JOIN sc_cat_item sc_cat_item1 ON task0.`a_ref_1` = sc_cat_item1.`sys_id` ) LEFT JOIN sys_metadata sys_metadata2 ON sc_cat_item1.`sys_id` = sys_metadata2.`sys_id` ) WHERE task0.`sys_class_name` = 'sc_req_item' AND sys_metadata2.`sys_class_name` NOT IN ('sc_cat_item_guide' , 'sc_cat_item_wizard' , 'sc_cat_item_content') AND sc_cat_item1.`sc_catalogs` = 'e0d08b13c3330111c8b837659bba8fb4' AND (sc_cat_item1.`hide_sp` = 0 OR sc_cat_item1.`hide_sp` IS NULL ) GROUP BY task0.`a_ref_1`,sc_cat_item1.`name` ORDER BY count_of_45033948 DESC ,sc_cat_item1.`name` The full query with the new security restrictions generates an additional clause to the query looking for catalog items a user has visibility to. This can potentially, depending on the size of the service catalog, cause a large 'IN' clause to the query: 2020-07-30 13:22:39 (694) Default-thread-13 1DF062421B1ED4541D6955BE6E4BCB9B txid=de1162821b1e Time: 0:00:03.551 id: xxxx_1[glide.21] (connpid=666080) for: SELECT sc_cat_item1.`sys_id` AS `cat_item_sys_id`, count(*) AS count_of_45033948 FROM ((task task0 LEFT JOIN sc_cat_item sc_cat_item1 ON task0.`a_ref_1` = sc_cat_item1.`sys_id` ) LEFT JOIN sys_metadata sys_metadata2 ON sc_cat_item1.`sys_id` = sys_metadata2.`sys_id` ) WHERE task0.`sys_class_name` = 'sc_req_item' AND sys_metadata2.`sys_class_name` NOT IN ('sc_cat_item_guide' , 'sc_cat_item_wizard' , 'sc_cat_item_content' , 'sc_cat_item_producer') AND (task0.`a_ref_1` IN ('844ec43edb4b5f80bd27f9231d9619c0' , '0c5790aedbb29f00bd27f9231d961999' , '4c9ecdb5dbb65700f16ef1951d96193a' , '9cde49d3dbe05850e9f160850596193d' , '0ca702b9db329700f16ef1951d961938' , 'a30307dcdb29c4502b7334bffe96192c' , '80e27ba2db875f80bd27f9231d9619f3' , '585bffebdbad04508d4c72b10f9619f3' , '0d6602b5db329700f16ef1951d961927' , 'fb263ebddb7f5f04bd27f9231d96199d' , '0970d5b9dbb65700f16ef1951d96194b' , '18b97335dbc9cc1081b6fd961d961965' , '321e34e3db3a5740bd27f9231d9619fa' , '0dbf30e1dbfa5300f16ef1951d961907' , 'b169ab0edbf10c50bd27f9231d96199b' , 'caaa78d7db367f448d4c72b10f961932' , '8a9891f5dbf65700f16ef1951d961938' , '0eba90eddb765300f16ef1951d961916' , '0f2d0c29dbf25300f16ef1951d96192b' , '17bcb1dbdbc01b00bd27f9231d96191d' , '4bb35dbadb9bb200bd27f9231d961903' , '8fbbd7a2dbc35f80bd27f9231d9619db' , '8bcf4248db039b40bd27f9231d961930' , 'e02401d0db1767c081b6fd961d961901' , '47eeda75dbff9f00f16ef1951d96199c' , '844be9f1dbba5700f16ef1951d96194b' , '188fc96ddbcda300bd27f9231d96198d' , '00ba016ddb3e5300f16ef1951d96199d' , '48f5fca5dbba5300f16ef1951d96199d' , 'c4faf31edb3e5f00bd27f9231d9619da' , 'cd2390addb365300f16ef1951d96195e' , '0156c921db3e5300f16ef1951d961950' , '4f36d07fdbed1300bd27f9231d9619e9' , '0182986edb729f00bd27f9231d961951' , '950b976adb1d6f80bd27f9231d9619ec' , '9ac3c151dbd76b04bd27f9231d961974' , '15d84286dbb29b00bd27f9231d9619b7' , 'ca8210e9db365300f16ef1951d961979' , '4e35cb62db875380f16ef1951d961916' , '4aafe713db5e5f08f16ef1951d961905' , 'ced14866dbbe5f00bd27f9231d9619ab' , '322b9061dbb65300f16ef1951d961996' , 'fe3afe0edbe0b30081b6fd961d9619f3' , '8fa869fddb7a5700f16ef1951d961970' , '406209b9db36d700bd27f9231d9619d0' , '88184eb9db329700f16ef1951d961964' , '0c515baadbb55b40bd27f9231d96195d' , 'b07e89dedb57d7c0f16ef1951d9619f0' , '16d1b27cdb28ef80bd27f9231d9619bc' , 'cc6cc4a5dbf25300f16ef1951d9619b6' , '8882467ddbfe5700f16ef1951d96198d' , '4c9044b2db0b5f80bd27f9231d9619cb' , '8e241365db17c81057d6fda51d961965' , '04eea1fddbba5700f16ef1951d961995' , '815dff92db7e5f00bd27f9231d96191c' , '7df839f1eb1230000415e0f64206fe79' , '89d62b12db7a5f00bd27f9231d9619f8' , '81f19d3ddbb65700f16ef1951d961977' , '8973a6a2db8abf002b7334bffe961910' , '3539cafddb329700f16ef1951d9619a7' , '0680217ddb3a5700f16ef1951d961973' , '06ce9d75db3a5700f16ef1951d9619d9' , '4c374c9fdba293c8f16ef1951d961904' , '45094653dbee7f0481b6fd961d961955' , '47777598dbd8e744f16ef1951d961958' , '1a605eacdb94d740f16ef1951d961925' , '07f104a6dbbe5f00bd27f9231d96193b' , '8c0248e5db725300f16ef1951d961996' , '0847dc6edbb29f00bd27f9231d961901' , '885fec1e4f41274c9c56e3218110c78c' , '0086a5f9db7a5700f16ef1951d9619a5' , '8cbca5f5dbba5700f16ef1951d961952' , '42f1c6e8dba0081c81b6fd961d961932' , '8d49c631db729700f16ef1951d961919' , '8d5294a9db365300f16ef1951d96194c' , 'cd66d0aadbb29f00bd27f9231d961948' , 'cd7e7135dbfe5700f16ef1951d961976' , '21d35525db01e300bd27f9231d961959' , 'b4d0f074dbd92f40bd27f9231d9619ab' , '1103359d1b881490b50d7739cd4bcb6d' , '86f74eb9db329700f16ef1951d9619bc' , '285b724adb91c4d08d4c72b10f9619b3' , '0762ca3ddbfe5700f16ef1951d961997' , '9ed8c7a8db0a9704f16ef1951d9619ea' , '83be34a1dbfa5300f16ef1951d9619cf' , '03cc8a00db039b40bd27f9231d961918' , 'd01a0472db4b5f80bd27f9231d961919' , '8d827e1cdbd42b44f16ef1951d9619cd' , '44cbcfafdb62445057d6fda51d9619e9' , '58d5d025db765300f16ef1951d961903' , '1cdd50e9dbb65300f16ef1951d961903' , 'c5121c65db89a300bd27f9231d96198d' , '5cfbb4addbba5300f16ef1951d96199f' , '4c6bbc5cdbfa6b807e7b7c910f961905' , '5921c1e5db3a1700bd27f9231d96198f' , '52df83bddbb2809057d6fda51d9619b3' , '9942d5a5dbbe5300f16ef1951d96195c' , '514450e2dbb29f00bd27f9231d9619cb' , '1592e81edb13d7c0f16ef1951d9619a3' , '995619f1dbf65700f16ef1951d961974' , '917b00f2db4b5f80bd27f9231d96194f' , '3cbbe8b5dbc98700bd27f9231d961923' , '91aad7a2dbc35f80bd27f9231d961995' , '1dc14466dbbe5f00bd27f9231d96199e' , '97531948db4948108d4c72b10f96199d' , '5a37a17ddb7a5700f16ef1951d961929' , '563dad79dbba5700f16ef1951d9619bd' , '4012ec4adbef0bc0f16ef1951d96194e' , 'cfdb3385db7e1300f16ef1951d9619a4' , '1c374c9fdba293c8f16ef1951d961971' , '1ec51e40db439b40bd27f9231d9619a8' , '26c052e9db229bc0bd27f9231d9619c8' , '9edbca8cdbcf5b40bd27f9231d9619e8' , 'd47e3452db63cbc0f16ef1951d961988' , '6f0e039e1b4990dcb58155be6e4bcb1c' , '1f33e535db7a5700f16ef1951d961994' , '1f58fc69dbba5300f16ef1951d961971' , '474ecc1ddbc55300f16ef1951d96191d' , In this example, the in clause contains over 2000 sys_ids due to the size of the customers service catalog and then performs an aggregation (GROUP BY cat_item) to get the count of records for each catalog item in the current catalog.ResolutionCustomers who have users that heavily frequent the Service Catalog in the portal generate this query every time the page loads. Due to the slowness of the query, this can become very expensive to the database, and sometimes, as in the case of this example the most impactful query generated by an instance. The solution is to defer the expensive aggregation of the Requested Items table from the widget to a scheduled job that would perform the expensive aggregation and save a snapshot of the results in a table and modifying the widget to query the snapshot. Referring to the example for the version 1 widget under KB0789213, we have expanded the solution to work with the new security restrictions added in the version 2 widget. This solution is valid if the ranking of popular items does not change significantly enough to warrant every end-user from performing this calculation. In general, the steps to resolve this are: 1) Create a user-defined table to store the results of the snapshot. 2) Create a scheduled job to periodically refresh the snapshot. 3) Clone the "Recent & Popular Items" and modify the Server script of the new widget to query the snapshot table. 4) Modify any and all Instances of the "Recent & Popular Items" Items widget to the new widget. Detailed Steps: 1) Create a user-defined table to store the results of the snapshot: a) Navigate to the System Definition > Tables module. b) Click on the "New" button. c) Enter the following information: Label: Popular Items Name: u_popular_items d) Uncheck the "Create module" checkbox. e) Add the following Table Columns to the table: Column label: Catalog Type Type: Choice Max Length: 40 f) Add the following Table Columns to the table: Column label: Days Type: Integer Max Length: 40 Default Value: 30 g) Add the following Table Columns to the table: Column label: Catalog Item Column name: sc_cat_item Type: Reference Reference: Catalog Item [sc_cat_item] h) Add the following Table Column to the table: Column label: Count Column name: count Type: Integer Max length: 40 i) Add the following Table Column to the table: Column label: Producer Type: Reference Reference: Record Producer [sc_cat_item_producer] j) Click on the Additional actions > Save context menu item. 2) Create the Choice List values a) Click on the column label 'Catalog Type' b) Scroll to the bottom of the screen and click on the 'Choice' tab c) Click on the ’New’ button and fill in the following fields Label: Item Value: item d) Click on the 'Submit' button e) Click on the ’New’ button and fill in the following fields Label: Producer Value: producer f) Click on the 'Submit' button 3) Create a scheduled job to periodically refresh the snapshot: a) Navigate to the System Definition > Scheduled Jobs module. b) Click on the "New" button. c) Click on the "Automatically run a script of your choosing" link. d) Enter the following Information: Name: Refresh Popular Items Snapshot Run: Daily * Time: 00:00:00 * Run this script: // Start of Script refreshItemSnapshot(90); refreshItemSnapshot(180); refreshItemSnapshot(365); refreshProducerSnapshot(90); refreshProducerSnapshot(180); refreshProducerSnapshot(365); function refreshItemSnapshot(days) { truncateSnapshot('item', days); var createdQuery = 'sys_created_onRELATIVEGE@dayofweek@ago@' + days + '^'; var count = new GlideAggregate('sc_req_item'); count.addAggregate('COUNT', 'cat_item'); count.groupBy('cat_item.sys_id'); count.addQuery('cat_item.sys_class_name', 'NOT IN', 'sc_cat_item_guide,sc_cat_item_wizard,sc_cat_item_content,sc_cat_item_producer'); count.addQuery('cat_item.visible_standalone', 'true'); count.addEncodedQuery(createdQuery + 'cat_item.hide_sp=false^ORcat_item.hide_spISEMPTY'); count.orderByAggregate('COUNT', 'cat_item'); count.query(); while (count.next()) { var gr = new GlideRecord('u_popular_items'); gr.initialize(); gr.u_catalog_type = 'item'; gr.u_days = days; gr.u_catalog_item = count.getValue("cat_item.sys_id"); gr.u_count = count.getAggregate('COUNT', 'cat_item'); gr.insert(); } } function refreshProducerSnapshot(days) { truncateSnapshot('producer', days); var createdQuery = 'sys_created_onRELATIVEGE@dayofweek@ago@' + days + '^'; count = new GlideAggregate('sc_item_produced_record'); count.addEncodedQuery(createdQuery + 'producer.hide_sp=false^ORproducer.hide_spISEMPTY'); count.addAggregate('COUNT', 'producer'); count.addQuery('producer.visible_standalone', 'true'); count.groupBy('producer.sys_id'); count.orderByAggregate('COUNT', 'producer'); count.query(); while (count.next()) { var gr = new GlideRecord('u_popular_items'); gr.initialize(); gr.u_catalog_type = 'producer'; gr.u_days = days; gr.u_producer = count.getValue("producer.sys_id"); gr.u_count = count.getAggregate('COUNT', 'producer'); gr.insert(); } } function truncateSnapshot(type, days) { var popItems = new GlideRecord('u_popular_items'); popItems.addQuery('u_catalog_type', type); popItems.addQuery('u_days', days); popItems.deleteMultiple(); } // End of Script *** Note:Although Daily at midnight may be sufficient for some cases, you might consider altering the Run schedule per your specific needs. The more often you run the job, the more up-to-date the snapshot will be. e) Click on the Additional actions > Save context menu item. f) Click on the "Execute Now" button. 4) Create the alternative Service Portal Widget: a) Navigate to the Service Portal > Widgets module. b) Search for the "Recent & Popular Items" widget and open the form. c) Click on the "Clone Widget" button. d) Change the Name field from "Copy of Recent & Popular Items" to "Recent & Popular Items from Snapshot" e) Change the Server script field to the following: (function() { /* populate the 'data' object */ data.limit = options.limit || 8; var recent_by = options.recent_by || 'view'; var recent = new GlideRecord('sp_log'); if (recent_by === 'view') recent.addEncodedQuery('userDYNAMIC90d1921e5f510100a9ad2572f2b477fe^type=Cat Item View^sys_created_onONThis quarter@javascript:gs.beginningOfThisQuarter()@javascript:gs.endOfThisQuarter()'); else recent.addEncodedQuery('userDYNAMIC90d1921e5f510100a9ad2572f2b477fe^type=Cat Item Request^sys_created_onONThis quarter@javascript:gs.beginningOfThisQuarter()@javascript:gs.endOfThisQuarter()'); recent.orderByDesc('sys_created_on'); recent.query(); var recentItems = []; var catalogArr = ($sp.getCatalogs().value + "").split(","); data.showPrices = $sp.showCatalogPrices(); while (recent.next() && recentItems.length < data.limit) { if (isAlreadyAdded(recent.getValue('id'))) continue; var catalogItemJS = new sn_sc.CatItem(recent.getValue('id')); if (!catalogItemJS.canView(gs.isMobile()) || !catalogItemJS.isVisibleServicePortal()) continue; var item = {}; var catItemDetails = catalogItemJS.getItemSummary(true); if (!catItemDetails.visible_standalone) continue; var inCatalog = false; for (var i = 0; i < catItemDetails.catalogs.length; i++) { if (catalogArr.indexOf(catItemDetails.catalogs[i].sys_id + "") >= 0) { inCatalog = true; break; } } if (inCatalog) { item.name = catItemDetails.name; item.short_description = catItemDetails.short_description; item.picture = catItemDetails.picture; item.price = catItemDetails.price; item.sys_id = catItemDetails.sys_id; item.hasPrice = item.price != 0; item.page = catItemDetails.type == 'order_guide' ? 'sc_cat_item_guide' : 'sc_cat_item'; recentItems.push(item); } } function isAlreadyAdded(catItemSysId) { for (var i = 0; i < recentItems.length; i++) { if (catItemSysId == recentItems[i].sys_id) return true; } return false; } data.recentItems = recentItems; data.popularItems = getPopularItems(); function getPopularItems() { var limit = options.limit || 8; var items = []; var days = 0; var allowedItems = getAllowedCatalogItems(); if (options.popular_items_created == 3) days = 90; else if (options.popular_items_created == 6) days = 180; else if (options.popular_items_created == 12) days = 365; var count = new GlideRecord('u_popular_items'); count.addQuery('u_catalog_type', 'item'); count.addQuery('u_days', days); count.orderByDesc('u_count'); count.query(); while (count.next() && items.length < limit) { var catalogItemJS = new sn_sc.CatItem(count.u_catalog_item); if (!catalogItemJS.canView(gs.isMobile()) || !catalogItemJS.isVisibleServicePortal() || allowedItems.indexOf(count.u_catalog_item.toString()) == -1) continue; // user does not have permission to see this item var item = {}; var catItemDetails = catalogItemJS.getItemSummary(); item.order = 0 - count.u_count; item.name = catItemDetails.name; item.short_description = catItemDetails.short_description; item.picture = catItemDetails.picture; item.price = catItemDetails.price; item.sys_id = catItemDetails.sys_id; item.hasPrice = item.price != 0; item.page = 'sc_cat_item'; items.push(item); } var producers = 0; count = new GlideRecord('u_popular_items'); count.addQuery('u_catalog_type', 'producer'); count.addQuery('u_days', days); count.orderByDesc('u_count'); count.query(); while (count.next() && producers < limit) { catalogItemJS = new sn_sc.CatItem(count.u_producer); if (!catalogItemJS.canView(gs.isMobile()) || !catalogItemJS.isVisibleServicePortal() || allowedItems.indexOf(count.u_producer.toString()) == -1) continue; // user does not have permission to see this item catItemDetails = catalogItemJS.getItemSummary(); item = {}; item.order = 0 - count.u_count; item.name = catItemDetails.name; item.short_description = catItemDetails.short_description; item.picture = catItemDetails.picture; item.price = catItemDetails.price; item.hasPrice = item.price != 0; item.sys_id = catItemDetails.sys_id; item.page = 'sc_cat_item'; items.push(item); producers++; } return items; } function getAllowedCatalogItems() { var allowedItems = []; var catalogs = $sp.getCatalogs().value.split(','); catalogs.forEach(function(catalog) { var catalogObj = new sn_sc.Catalog(catalog); var catItemIds = catalogObj.getCatalogItemIds() || []; for (var i = 0; i < catItemIds.length; i++) { if (allowedItems.indexOf(catItemIds[i]) < 0) allowedItems.push(catItemIds[i]); } }); return allowedItems; } })(); 5) Modify any and all Instances of the SC Popular Items widget to the new widget. a) Navigate to the Service Portal > Widget Instances module b) Filter the list on Widget Name = SC Popular Items c) For each instance of the SC Popular Items widget, edit the Widget field and point it to the newly created "SC Popular Items from Snapshot" widget.