My SQL Server CLR function is very slow

Question

I have created two methods in C#:

public static string Encrypt(string clearText, string encryptionKey)
{
        byte[] clearBytes = Encoding.Unicode.GetBytes(clearText);

        using (Aes encryptor = Aes.Create())
        {
            var pdb = new Rfc2898DeriveBytes(encryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            encryptor.Key = pdb.GetBytes(32);
            encryptor.IV = pdb.GetBytes(16);

            using (var ms = new MemoryStream())
            {
                using (var cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
                {
                    cs.Write(clearBytes, 0, clearBytes.Length);
                    cs.Close();
                }

                clearText = Convert.ToBase64String(ms.ToArray());
            }
        }

        return clearText;
    }

    public static string Decrypt(string cipherText, string encryptionKey)
    {
        try
        {
            byte[] cipherBytes = Convert.FromBase64String(cipherText);

            using (Aes encryptor = Aes.Create())
            {
                var pdb = new Rfc2898DeriveBytes(encryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
                encryptor.Key = pdb.GetBytes(32);
                encryptor.IV = pdb.GetBytes(16);

                using (var ms = new MemoryStream())
                {
                    using (var cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(cipherBytes, 0, cipherBytes.Length);
                        cs.Close();
                    }

                    cipherText = Encoding.Unicode.GetString(ms.ToArray());
                }
            }
        }
        catch (Exception)
        {
        }

        return cipherText;
    }

By following the steps given in This Link, I have created a CLR function in SQL Server and I am trying to call it like:

SELECT dbo.Decrypt(MyEncrypted, EncryptionKey)
FROM   MyTable

The problem is, it is taking TOO MUCH TIME. Like for only 1000 rows, it took 1.5 minutes. If I call my query without the CLR function, it took less than 1 second.

Is there any thing I can do to improve the performance of the CLR functions?

can you use sql server's encryption? why are you saving it as a string when you could save the raw bytes? — Daniel A. White, Aug 16 '15 at 11:28
What is the size of data in the "MyEncrypted" and "EncryptionKey" columns? How long does that code take when running in a C# console app with the exact same inputs? — Martin Smith, Aug 16 '15 at 12:16
Size of "MyEncrypted" is NVARCHAR(1000) and EncryptionKey is NVARCHAR(256)... — Usman Khalid, Aug 16 '15 at 12:18
0.09 seconds a row is slow? Commands have to be sent between database and c# application which takes time. There could be a firewall or virus checker that is validating contents which could add time. You may not be able to get better performance. You may be able to improve performance by make so objects static and calling the constructor only once rather than every time the method is called. — jdweng, Aug 16 '15 at 13:19
@jdweng the OP is using this code as a CLR function, so it runs on the sqlserver, in the same process but in a separate app-domain, no firewalls are involved... — rene, Aug 16 '15 at 13:30
rene : Not necessarily true. Is the transfer of data a virtual connection? — jdweng, Aug 16 '15 at 13:47

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

I've analyzed your Decrypt method with the Performance Analyzer in VS2010 by running it 100 times:

As you can see the GetBytes method of the Rfc2898DeriveBytes instance take the most time.

I'm not sure why you have these specific encryption/decryption requirements but one way influence the time the GetBytes method takes is to instantiate the Rfc2898DeriveBytes using the constructor that takes iterations as the third parameter. Default it is on 1000, I can set it as low as 1. BUT THIS IS STRONGLY ADVISED AGAINST

var pdb = new Rfc2898DeriveBytes(encryptionKey, salt, 10);

This iterations does need to be the same for both the Encrypt and Decrypt so you'll have to Decrypt\Encrypt your current values if you want to change that.

One other option might be to cache the IV values as seems to recommended in this answer. I'm not enough of an expert if they talk there about using the same Key as well but if that is an option you might cache the call to GetBytes for the key as well.

All the described change have impact on how your data is encrypted and the strength of your encryption. Consider both impacts when testing the solutions.

edited May 23 '17 at 12:29

Community

1
1

answered Aug 16 '15 at 14:59

rene

41,474
78
114
152

Reducing the number of iteration on key derivation functions has cryptographic consequences. Don't recommend it for perf gain. – Remus Rusanu Aug 16 '15 at 15:05
OK noted. and the caching part, store the IV in the table and read the KEY from a config file/ registry @RemusRusanu so you won't need the GetBytes calls on decrypt? – rene Aug 16 '15 at 15:08
... or use SQL Server built-in [`ENCRYPTBYPASSPHRASE`](https://msdn.microsoft.com/en-us/library/ms190357.aspx) – Remus Rusanu Aug 16 '15 at 15:29
@rene Thanks for your effort but changing the algorithm is not an option for us. – Usman Khalid Aug 17 '15 at 04:42
@RemusRusanu Also database encryption is not an option. :( – Usman Khalid Aug 17 '15 at 04:45
@rene Your statistics helped me a lot. Now I have changed my algorithm according to new algorithm and it is working fine. Here is the solution given in another link : http://stackoverflow.com/questions/10168240/encrypting-decrypting-a-string-in-c-sharp – Usman Khalid Aug 17 '15 at 07:06

My SQL Server CLR function is very slow

1 Answers1