I have some procedures which use SQL statements like these:
OPEN SYMMETRIC KEY SymKey_Xyz DECRYPTION BY CERTIFICATE Certificate_Xyz
SELECT DECRYPTBYKEY(column1),DECRYPTBYKEY(column1) from ABC
CLOSE SYMMETRIC KEY SymKey_Xyz
When these procedures are executed for more than 500 users concurrently, the system is locking these procedures.
Is there any better approach to use Encryption/Decryption in SQL Server which will not impact performance so much?