Hello World.
I recently had the opportunity to work on an urgent issue that was confounding a ServiceNow admin in a large banking environment where reports were created centrally and then shared with various users, groups and teams. It was observed by some groups that a specific report might take 30 seconds to execute for User A, while user B found the report to take less than 4 seconds. The admin checked user roles, groups, and permissions and then impersonated the users and confirmed that for a specific report shared to a team, some users found the report to be very slow while others found it to be very quick.
Here's an example of the problem.
User Misty executes a report named incident_report_test and it takes 76-seconds for the results to come back:
Another user, Mike, finds the report returns results to him in 67-seconds. This is 9-seconds quicker than Misty sees and she is part of the same workgroup with all the same roles as Mike and in the same geographic locations (ruling out network-related latency as a possible cause).
A closer review of the above two screenshots hints at where the difference is response times comes from. Do you see it?'
Do get a better idea of the issue, as admin, we can turn on "Debug SQL (Detailed)" and then impersonate the users and capture the slow queries involved.
After doing this with both Misty and Mike, we can see a difference in the ORDER BY clause:
For Misty (76-second report execution), we find the ORDER BY on task.short description.
Now, compared to Mike, we see that he has an ORDER BY on task.state:
Looking at the report parameters, it's the same report for each user (administered and granted by another team within the org.).
So why the different ORDER BY parameters?
This is due to user list sort preferences (i.e. table_name.db.order and table_db.order.direction) being carried over and appended to the background SQL queries for these centrally created reports which do not already contain a sort within the report.
Both Mike and Misty have direct access to the incident table and they frequently go to this list and will change the sort on a given column depending on their needs. this can be confirmed by navigating in the platform to incident.LIST or as admin, going to sys_user_preference.LIST and looking up both user's preferences.
Why does all this matter? It's just a few seconds different, right?
Well, the above example was just for demonstration purposes, in real life, the difference might be much greater and only after several (very vocal) complaints did the admin reach out to us. The banking company has a very large user base, executing centrally created reports (written over the course of years) and the unexpected consequence is that two users, having different list preferences for sorting will have wildly different response times for a report. In the background, the SQL for the report is being appended with the user's own ORDER BY preference for a field (like short description) which might not even display on the report.
While it might be argued that lists and reports are separate things and that reports shouldn't allow appended sort conditions to be carried over from userland, the following options can be used to overcome this issue, to varying degrees:
1. The use of a module link to pass a hardcode parameter for sort order. This is discussed in the product documentation here:
https://docs.servicenow.com/bundle/newyork-platform-administration/page/administer/list-administration/concept/c_ControlTheListSortSequence.html#c_ControlTheListSortSequence
2. You could globally apply for a sort order per table via a dictionary entry. This is discussed in the product documentation here:
https://docs.servicenow.com/bundle/newyork-platform-administration/page/administer/list-administration/concept/c_ControlTheListSortSequence.html#d153306e122
3. A sort order condition can be explicitly added to the reports and this takes precedence over individual user preferences for sorting on the base table.
Option 3 is the most straight forward and easiest approach as it assures a consistent execution time for any given report between users (assuming all other conditions are equal...e.g. roles, rowcount, etc.).
After updating the report (as admin) with a sort order condition, both Mike and Misty saw nearly identical response times. Here is what Mike experiences after adding a sort condition to the report.
And Misty now experiences the same level of performance as her colleague Mike.
So, hopefully, this information will help somebody out there.
Thanks for reading and let us know if you have any questions or concerns.
1 Comment
