What would be the best way to manage large tables (8+ Million records)? What is a recommended limit to keep? Is table rotation a best practice to follow?

Jose Alva BCGov · ‎07-08-2022

We have a couple of tables which are growing at a rate on 400k records per quarter in a scoped financial application. Multiple users with various roles run reports and dashboards are using these tables. We are at the point where the reports and dashboards are taking too long to load. So we are looking at best practices for large table maintenance and management.

Thanks.

Sean Walters · ‎07-18-2022

Hi Jose,

I think there are a few key questions to understand

How long is the data needed ?

Does it need to be retrieved from ServiceNow ?

An approach that may be useful (depending on the exact requirements) is setting up data archiving rules.

Data archiving involves managing table size growth and archiving old data. It moves data that is no longer needed every day from primary tables to a set of archive tables.

The longer an instance runs, the more likely it is to accumulate data that is no longer relevant. For example, task records from two years ago are typically less relevant than currently active tasks. Old data may eventually cause performance issues by consuming system resources and slowing down queries and reports.

If you cannot delete this data because you need it for auditing or for historical purposes:

Set up an archive rule that archives the data and removes it from immediate access to free up system resources.
Set up a destroy rule to delete the data after a specified period.
Configure the archive and destroy rule properties that control batch processing of the archive and destroy rules.

You can read more about this on this ServiceNow doc:
https://docs.servicenow.com/en-US/bundle/sandiego-platform-administration/page/administer/database-rotation/concept/c_ArchiveData.html

Additionally what we did on one of our projects is have an integration that were pulled into a data warehouse and therefore can be deleted from ServiceNow as going forward you can request for data from the data team.

Hope that helps.

View solution in original post

Yousaf · ‎07-08-2022

Hi Jose,

Look at this Link you might find your answer
Table Extension in Servicenow - Docs

Mark Correct or Helpful if it helps.

Thanks,
Yousaf

***Mark Correct or Helpful if it helps.***

Jose Alva BCGov · ‎07-13-2022

Thank you Yousaf,

What's your experience using table rotation? How does it work with reports? What is the maximum recommended size on a table before using Table rotation?

Maik Skoddow · ‎07-08-2022

Hi @Jose Alva BCGov

there is no common answer for that, as it depends on your scenario and which data you need.

For example, is it necessary to load all data into one report or can they be reduced to let's say "all records from the last year"? If so, reduced amount of consumed data will increase reporting speed.

Also, indexes can be an important optimization option, but they have to be defined carefully and always dependent on your scenario.

So without more details from you or access to your instance it is impossible to give the right answers.

Kind regards
Maik

stephane9 · ‎07-11-2022

Hi, jus to add some more info to what Jose detailed:

This table is used as a General Ledger called "Received Services" or RESE. Each month end ( not each quarter), we generate about 400K new records out of our Asset Financials records , so we only write to this table when those jobs execute at month end.

We never delete out of that table and in term of reading, we have:

- a dashboard with that generate a set of reports for the current fiscal period and another for the last year , for those meeting a special condition status -> we created indexes for those

- a monthly export of all the records to feed our Data Warehouse, which is about to migrated to a delta load ( much better) but which may require a full extract every 6 month

Is it not extending or extended any tables.

We only have one table that references the RESE table (adjustments) and this table is only updated at month end as well with a few records. About 20 fields reference other tables like AR account, company etc..