Data Table Indexing to help with Performance Issues - Clarification Please!!!

[jstoessel]

Currently we are looking at improvements to the performance of our reports and dashboards.

The topic of the recently released dashboard data refresh was brought up.  The question is this:

Is the data refresh something that potentially helps performance only at the local user browser level?

Continuing down, or maybe up the performance path - an Indicator will run on a schedule and pull a specific set of data from table.  Then when the dashboard widget is built on that indicator, the user doesn't have to wait for the refresh from the table, they are getting the data directly from the indicator generated table.

The slowest potential performance of a dashboard widget, would be anything that is directly pulling from the table.  I also believe that this is the only place that completing table level indexing would potentially improve the performance of the widgets appearing on a dashboard.

My real use case:

• Incidents Closed on This Fiscal Year

and the breakdown is by

• Business Service>Owned by>Department

Obviously this is not a standard way of looking at the incident data, but it is the requirement for where we are in our maturity path of moving from managing to keep incident assignment group queues empty toward looking at the stability of our business services.

Because this is a pretty unique way of looking at the data - I wrote Indicators to help narrow the scope of data processing.  I wasn't able to just do a single report with these breakdowns, I would get errors around too long to generate the report, etc.

We have 33 departments in IT, and over 1,500 Business Service named owners, and we create about 1,500 - 2,000 new incidents per day.  This means, I pretty much have 33 versions of every report at the Indicator level, which is an effort to improve performance as well.

So my bottom line question - would going through the process of performing indexing on the incident portion of the task table make PAE widgets built on indicators perform better?

Also - please correct anything I am obviously not understanding about how these things work!  Thanks!

@Thomas Davis 
@olgaredkina 

@Adam Stout 

[jstoessel]

YTD - I actually create a version of the Indicator which is based on YTD, so the scorecard is only showing 1 number and that seems to generate quickly, well, as a relative quick

for the YTD Top 10 that is a different story - I am using the same indicator, but applying the breakdown in PAE

I have a case open right now, the Top 10 functionality works on the Pareto, but it is doing strange things on Pivot Tables.  If there are 3 indicators in your Pivot, you could get back as many as 30 rows, each having their own Top 10 and the values in the other two columns are replaced with zero - super frustrating!!

Appreciate the feedback, and I'm continuing to work with the Platform Admins around indexing, I am not holding out much hope of it helping too much.  I feel that our production tables are somewhat the culprit.  We have 1.9M+ records in INCIDENT and they are archiving records closed more than 2 years... we have SO MUCH data

Understanding that the majority of the selection criteria for this reporting is about four or five dot walks away from the incident table...slow is the way it is

"Business Service>Owned by>Department>Department Head"

or

"Business Service>Owned by>Department>Location>Country" is USA

When I was using the attributes of the Owner exclusively, I was the victim of reorg/job changes so much, I had to move up to the department layer.  At least the Networking department still does networking things, even if the personnel churns.  We moved our "Owned By" up the org chart to be at the Director level, as they tend to stay in their same seats longer.

I do run the PA Cleanup and watch for warnings, and have reformed selection criteria multiple times to see if the order of filter impacts - limited successes there.

I wish we weren't always focused on every grain of rice, it would be so amazing to talk about pounds of rice, or even pallets of rice...but part of IT is that so many management decisions are made at the grain of rice (single transaction) level.  I guess that is why analysts like me are in our jobs.  🙂

Thanks again, nice to get some affirmation that I am doing many things right, or at least ok!

I am holding out hope for DPM, SPM, etc. - if we can get there - so much of this becomes so much easier!!!

[Daniel Oderbolz]

Let me discuss the indexing bit of your question.

I see a tendency that ServiceNow developers think that adding an index will always improve performance.

In your case, especially when looking at the fiscal year, partitioning would be beneficial.

Unfortunately, ServiceNow has decided not to use DB level partitioning (yet). I personally hope that with RaptorDB, this will become available as it really speeds things up (typically much more than an index in your use case).

When it comes to the department, an index on the Business Service is anyway present (because it is a reference field).

Since your query involves a complex join (between incident, business service and department) this bogs down performance.
I assume that the relationship between the departments and the services is rather static. So I would create Dynamic Filter Options like "One of our services" (which returns a list of services for the department of the logged in user) and then you no longer need to filter on the department as this is done earlier.

See also 

Dynamic Fiter Options 

Best
Daniel