Optimisation for DeleteUnrequiredInstalledFileEvidence Writer

[#1]

Summary

This article describes how to optimise some SQL code to work around a known performance issue in the "DeleteUnrequiredInstalledFileEvidence" writer.

Synopsis

In FlexNet Manager Suite version 2016 or later , a License Reconcile may spend a large amount of time on running the "DeleteUnrequiredInstalledFileEvidence" writer. However, this known performance issue can be worked around by optimising some SQL code.

Discussion

The performance issue is currently being tracked as bug IOJ-1807313 & IOJ-1887519. Until it's permanently addressed in a future release, you may consider to optimise some SQL code in the FileEvidence.xml Writer as detailed below. Please make sure to back up your original FileEvidence.xml writer file before the change.

Please contact Flexera Technical Support if you need further assistence.

Workaround

                       -- Delete obsolete records that do not exist anymore
                                DECLARE @DeletedInstalledFileEvidenceCount BIGINT
                                DECLARE @BatchSize INT
                                DECLARE @Rows INT

                                SET @Rows = 1
                                SET @DeletedInstalledFileEvidenceCount = 0
                                                                
                                WHILE @Rows > 0
                                BEGIN
                                                SET @BatchSize = ISNULL((SELECT Value FROM dbo.DatabaseConfiguration WHERE Property = 'FileEvidenceWriterBatchSize'), 1000000)

                                                DELETE TOP(@BatchSize) 
                                                dbo.InstalledFileEvidence
                                                WHERE NOT EXISTS (      SELECT 'x'
                                                                                                                                FROM #TmpInstalledFileEvidence AS tife
                                                                                                                                WHERE tife.RowNumber = 1
                                                                                                                                AND tife.FileEvidenceID = InstalledFileEvidence.FileEvidenceID
                                                                                                                                AND tife.ComplianceComputerID = InstalledFileEvidence.ComplianceComputerID
                                                                                                                                AND tife.AccessModeID = InstalledFileEvidence.AccessModeID)

                                                SET @Rows = @@ROWCOUNT
                                                SET @DeletedInstalledFileEvidenceCount = @DeletedInstalledFileEvidenceCount + [~comments]
-- BEGIN CUSTOMISATION
                                                -- We are done as soon as fewer than @BatchSize rows got deleted
                                                IF @Rows < @BatchSize
                                                                BREAK
-- END CUSTOMISATION
                                END
                                
                                PRINT N'Removed ' + CAST(@DeletedInstalledFileEvidenceCount AS nvarchar(MAX)) + N' records from InstalledFileEvidence that no longer exist in data sources'