Speed up Search and Replace Rules

KVM

Are there any tricks or suggestions to speed up search and replace rules?

PlantBasedSQL

Honestly @KVM I can't fault your approach!
I'm engaged with the team now on trying to get the ability to convert Search-Replace rules into split ranges so they can be sped up!
Although to be fair 91 million rows in 1.25 hours still does sound pretty good to me for free text masking, I've seen it go a LOT slower!
Thank you again for YOUR feedback too, it helps us grow as a company and to improve our offerings and naturally if you have any other questions please feel free to let me know!
Kindest Regards

PlantBasedSQL

Hi @KVM thank you for your post!

Would you be able to walk me through what your search-replace rule is doing, just so that I have t he best idea of how compute heavy it is, and if it is indeed the best rule option?

By their very nature, Search-Replace rules can be quite intensive and whilst there are a few tricks to help speed them up:

- Make sure any additional non clustered indexes on that column is temporarily disabled
- Remove the WHERE clause on the rule
- Change the commit frequency in options and push it higher gradually to gauge impact 

In reality there's not a _lot_ that can be done. Thank you very much!

KVM

Hello!

I applied your suggestions as they fit in each case of the 14 Search and Replace rules.  I found some worked with "No Where Clause" and others would error off so those needed "Where Not Null" to be able to run properly.  I also grouped the tables where I could to disable and enable indexes. I had significant improvements when the rules ran independent of the full masking run which I attribute to system overhead etc. So thanks for the suggestions.

I did have one table that has just over 91 million rows that needed "Where Not Null" to be able to run and did not have any additional indexes.  It takes 1.25 hrs to run.  I am applying a "Random Replace" to a column that logs changes to the other tables.  The data could be anything from dates, names, company names, account numbers, SSN etc.
Would you have any suggestion for a different masking approach?

Thanks!