What is Task table flattening in SNOW?

Table flattening is a database concept of storing logically related tables on a physical level, which ServiceNow uses in heavily queried tables for improving the instance performance and query execution time on these tables.

How are tables physically stored in ServiceNow?

In the ServiceNow approach, one logical table such as “Task” is often being stored as more tables physically. There are two basic models provided for storing logical tables.

One of them is the Table per class extension model, which is designed for storing a logical table in several individual physical tables.

This model however often suffers from performance bottlenecks, because the queries against any child table must match the parent (i.e. Incident table must match to Task table).

This problem becomes significant especially when the query is trying to access fields from multiple child tables, such as Incident and Problem table values.

How to improve the query performance on logical tables?

Nevertheless, these performance issues are avoided in ServiceNow by a Table per hierarchy extension model, which stores related logical tables in a single flattened physical table (for example the Incident and Problem data are represented together by the Task table on a physical level).

Therefore, query can access these fields directly on the flattened table without the necessity of performing joins.

A flat table hierarchy does not have any effect on appearance, nor functionality of tables when they are displayed in ServiceNow (even database views functionality remains intact by choosing the Table per hierarchy model).

If your instance runs on a MySQL database, ServiceNow uses this model only for the Task table, because there is no performance benefit to flattening other tables.

However if you are using Oracle database, you can contact the technical support to discuss the Task table extension model.

How to display the extension model on a table

To view the extension model which is being used for a particular table, navigate to: System definition > Tables.

Then you may need to configure the form, in order to see the extension model field. Two options can be displayed here:

• Table per class or None: table is using the “Table per class” model, which defines a unique physical table for each logical table in a given hierarchy (for example Incident, Problem and Change tables are stored individually)
• Table per hierarchy: table is using the “Table per hierarchy” model, which stores a single flattened table in a given hierarchy (for example Task table contains all the data from Incident, Problem and Change logical tables)

Importing Task tables from ServiceNow into your database

With the help of SnowMirror, a product designed for ServiceNow database replication, it is possible to transfer data from the Task table to your data warehouse, even if the Task table uses Table per Hierarchy or Table per class extension models.

So technically, in case you would like to synchronize the Incident table, just the Incident table will be synchronized and not the columns from the parent Task table.

However, when setting up the synchronization you can also check the option “Include Inherited columns”, which allows you to choose columns from the parent table to synchronize along with the selected child table.

Therefore, regardless of the extension model stated above, the data in your replication database are stored in separate physical tables, whilst the inherited columns from parent table can be optionallyincluded.

Conclusion

Task table flattening in ServiceNow with the extension table model ”Table per hierarchy” greatly reduces query execution time. Whether the table is flattened or not, it is always a good practice to synchronize the table data into your local database using SnowMirror, so that your cloud instance is not influenced by data-heavy access that could slow it down.