Invoke-DbaDbDataMasking - Poor performance

[AndersLarzzon]

Verified issue does not already exist?

Yes

What error did you receive?

When having the HasUniqueIndex = true in the datamasking config file that triggers a creation of temptable where column values for columns to mask is generated. for a table of 200 000 rows it took 5 days to generate firstname and last name.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

PS D:\Applications\PowerShell\Scripts\DBATOOLS_commands> Invoke-DbaDbDataMasking -SqlInstance SASSOAPPTEST02 -Database testdb -FilePath D:\Applications\PowerShell_DataMasking_Data2\SASSOAPPTEST01.testdb.DataMaskingConfig_Person_all.json -WhatIf What if: Performing the operation "Masking 171423 row(s) for column [lastname, firstname] in testdb on target "SASSOAPPTES T02".

Are you running the latest release?

Yes

Other details or mentions

I have chatted with @sqlstad about this issue

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.2183
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.2183
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

SQL 2019 Dev Edtition: Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64) Sep 23 2021 16:47:49 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

.NET Framework Version

PS D:\Applications\PowerShell\Script_Center> .\DetermineNetframeworkVersion.ps1 .NET Framework 4.8

[andreasjordan]

@sanderstad - can you have a look at this? Thanks.

[sanderstad]

It's on my radar and haven't had a chance yet

[github-actions[bot]]

🚧🚨 This issue is being marked as stale due to 90 days of inactivity. If you would like this issue to remain open:

• Verify the issue/bug is reproduced in the latest version of the module
• Verify the environmental info provided is still accurate
• Add any additional steps you followed to reproduce if necessary 🚨🚧 ⌛️ This issue will be closed in 30 days ⌛️

[andreasjordan]

This got a bit stale. So let me bring this issue to you attention again...