Table partitioning may significantly decrease report refresh time (up to 10 times). This is reasonable on large databases (>1000 per day) when refresh time is taking > 3 minutes.
Table partitioning works hand in hand with the "Month Limit" parameter. The smaller month limit is set the more significant partitioning will affect data refresh time. When a "Month Limit" value is set in the way that it takes all the historical data enabling partition may not improve refresh duration.
In order to enable partitioning it is required to have one of the following SQL Server versions / editions:
- 2014 – Enterprise edition
- 2016 Service Pack 1 and higher versions – both Standard and Enterprise editions support table partitioning
For new database created using the Luware Database updater setup, table partitioning is enabled by default (in case it is supported by the SQL Server version / edition).
Warning! Enabling of the table may take significant time - up to 2-3 hours, and the database may grow during this process. This happens due to the fact that data is getting restructured. For this reasons we strongly suggest perform database backup before enabling table partitioning, especially on large databases. Refresh of reports may be delayed significantly.
Be aware that during the process of data partition it is forbidden to:
- Update the database version
- Execute integration jobs such as [Reporting].[usp_Dataloader]
Ignoring these rules may lead to irreversible data corruption. This procedure is therefore strongly suggested to be performed outside of operation hours.
Partitioning Table details
Table partitioning is performed by data loader according to the configured settings in the table [Reporting].[_Setting_Partitioning] which serves as combined configuration set and execution log.
The [Reporting].[_Setting_Partitioning] table consist of following fields:
- TableName (constant) – table for which partitioning is configured
- IsEnabled (user editable) – flag which indicates if enabled
- Result (system log) – the result of partitioning enabling. Possible values:
- [usp_DataLoader] pending
- PARTITIONING SUCCESS
- Error message
- UpdateDateTime (system log) – time of the last change to that record
- ProcedureName (constant) – procedure name (technical)
By default the values of the table are:
In order to enable table partitioning the user has to change field [IsEnabled] from 0 to 1. A reverse action 1 -> 0 is not available.
It is possible to enable table partitioning for all four tables using following SQL operation:
UPDATE s SET [IsEnabled] = 1 FROM [Reporting].[_Setting_Partitioning] s
Also you may enable table partitioning separately for specific table, e.g.
UPDATE s SET [IsEnabled] = 1 FROM [Reporting].[_Setting_Partitioning] s WHERE [TableName] = '[Reporting].[FactSession]' -- Put one of the table names here
After the enabling table partitioning for one or multiple tables in [Reporting].[_Setting_Partitioning] the operation will be applied during the next data loader execution:
- Due to that the duration of the data loader may take much more time than usual.
- When data loader is finished, it is reasonable to check [Reporting].[_Setting_Partitioning] again to ensure that result field contains “PARTITIONING SUCCESS” value.
- In case any error occurs during table partitioning [IsEnabled] flag is set to 0 and the field [Result] will contain the error message.