Knowledge Base

Search Knowledge Base

KB #240031: Considerations When Implementing Column Encryption and the Impact on Performance

Type: Information
Summary:
When considering column-level encryption, you must be aware of how encryption affects query performance, particularly if you are encrypting columns that participate in an index.
Additional Information:
When considering column-level encryption, you must be aware of how encryption affects query performance, particularly if you are encrypting columns that participate in an index. This is true whether you are using Encryptionizer or any other column encryption product. If the table is small (a few thousand rows) and your server has a lot of memory, you may notice little or no performance differences. However, if you are querying large tables (tens of thousands or even tens of millions of rows) it can have a profound impact.An exact lookup on a unique index value will continue to be extremely fast. For example, say you are looking up an exact SSNO ‘123456789’. If you search for the encrypted value of the SSNO, this will be as fast as ever; for example:

— additional parameters to API are omitted
select * from employees where ssno = fn_n_encrypt_char(@cSSno)

However, any query that requires that SQL look through a range of rows will be extremely slow, since SQL will have to decrypt each row. For example:

select * from employees where ssno like ‘1234%’

Even if two SSNO are almost the same, e.g., ‘123456789’ and ‘12345678’, their encrypted values will be completely different. Therefore, SQL will have no choice but to decrypt every SSNO to compare the values.Remember, that this only applies to column-level encryption, not whole database encryption. The recommendation would be to not encrypt columns that require those kinds of lookups. As an alternative, perform a first-level query that decrypts the evaluated data and then query the result set.

Any clause that does not look for a unique index value has the potential to be very slow on a large table. These clauses include, but are not limited to:

  • CONTAINS
  • LIKE
  • GREATER THAN, GREATER THAN OR EQUAL TO (>, >=, !>)
  • LESS THAN, LESS THAN OR EQUAL TO (<, <=, !<)
  • BETWEEN
  • SORT BY
  • GROUP BY.
Related Topics:
240032 Implications of Column Encryption on Queries When Using Views
240024 Tip for improving performance of column encryption APIs
240005 Cannot Perform Case-Insensitive Compare on Encrypted Strings Using APIs

 

Top