2sxc Patron Infrastructure - Sql Data Timeline Compression
Every change in 2sxc is logged to a table called
The purpose is to allow editors to roll back changes and restore entities to a previous state.
Websites with a lot of content editing can grow this table until it becomes the largest 2sxc table.
With the feature
SqlDataTimelineCompression all history-data will be stored as ZIP compressed JSON.
This can easily save you hundreds, if not thousands of MB in DB storage.
Activate the feature
Do this using the standard mechanisms to become a patron.
If you activate
Patron Infrastructure, this feature is automatically enabled.
Compress Previous Data
Older data is not modified by the change, but you can run the following SQL to compress it:
-- Will Compress everything and remove the data from the json column UPDATE [dbo].[ToSIC_EAV_DataTimeline] SET [CJson] = COMPRESS(CAST([Json] AS VARCHAR(MAX))), [Json] = NULL WHERE [Json] IS NOT NULL
Alternative (for analysis) - should result in 60-80% reduction
-- Will compress, but leave the old data there UPDATE [dbo].[ToSIC_EAV_DataTimeline] SET [CJson] = COMPRESS(CAST([Json] AS VARCHAR(MAX))) WHERE [Json] IS NOT NULL -- Compare sizes Select Sum( DATALENGTH([CJSON])) as CompressedSize, Sum(DATALENGTH([Json])) as JsonSize From [dbo].[ToSIC_EAV_DataTimeline]
Decompress Previous Data
If you were using this feature before and must opt-out, you can decompress the history data with the following SQL:
UPDATE [dbo].[ToSIC_EAV_DataTimeline] SET [Json] = CAST(CAST(DECOMPRESS([CJson]) AS VARCHAR(MAX)) AS NVARCHAR(MAX)), [CJson] = NULL WHERE [CJson] IS NOT NULL
- Added in v15