SQL Server Performance

Query is performing so slow after encryption

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Hossam, Jun 3, 2009.

  1. Hossam New Member

    Hi all,


    I using encryption to encrypt one column in my database but after I encrypte and try to select with condition as following syntax



    Select * from [MYTABLE] where [dbo].[the Function I created for Decryption]([MYCOLUMN])='THE VALUE'


    it take around 15 Minutes !!!!!

    My table rows are 200,000 rows
    has anyone had idea what can I do to enhance my query performance

    Thanks in Advance
  2. Adriaan New Member

    This is one of the common situations where a UDF kills performance. Since you are instructing SQL Server to apply a function to the contents of a column, it cannot use any index on your column, and so it has to process all rows from the table.
    Now if you can use an encryption UDF on the search expression ('THE VALUE'), that would solve your problem.
  3. Sandy New Member

    Why don't you use this.
    Select * from [MYTABLE] where MYCOLUMN = SELECT [dbo].[the Function I created for Decryption]('THE VALUE')
    Instead of [quote user="Hossam"] Select * from [MYTABLE] where [dbo].[the Function I created for Decryption]([MYCOLUMN])='THE VALUE'[/quote]
    Thank you,
    Sandy.
  4. Hossam New Member

    I have figuerd out what is the problem , i have to open the Master key before I select
    but i try to put the syntax in the function but give me error
    the function is
    CREATE FUNCTION [dbo].[Cards_decrypted_Helper] ( @SecretData VARBINARY(256))
    RETURNS NVARCHAR(50)
    WITH EXECUTE AS 'DBO'
    AS
    BEGIN
    RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData ))
    END
    go
    and the syntax i have to putin fuction is
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Test'
  5. Zippy New Member

    Seen something similar recently but I needed to use a LIKE, not =. I ended up placing the encrypted field in it's own index, not as the key as SQL won't let you index it but index on the primary key column and INCLUDE the encrypted column. This means SQL has a lot less reads to scan all the values as it scans the narrow index file to find the value. I had a wide table to start with and this made a big difference.
    Other idea for improving the speed of a direct lookup is to use a checksum column. Store the encrypted value and also a checksum of the value. If you index in the checksum and optionally INCLUDE the encrypted column in the index it can speed up the query further as it can hunt down the value with less reads.

Share This Page