Re: encryption optimization

The bottom line is that you can't efficiently search (use indexes) on these
encrypted key columns because you are searching using the decrypted value
and this value is not stored in the database. During the search, not only
must each value be decrypted, a scan of all table rows is required. This is
a very expensive operation.

For an equality search, you can store a hash in clear text, index the hash
value and add the hash search criteria to your search. This will greatly
reduce the number of qualifying rows and usually result in acceptable
performance. See Laurentiu Cristofor's blog
(http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx) for a more
complete discussion.

Unfortunately, there is no way to perform efficient wildcard/range searches
on encrypted data. If you must have this functionality, you'll need to take
a different approach like encrypting at the file level (EFS) rather than
column level. I don't know if that's an option in your environment.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Gerhard" <acsla@xxxxxxxxxxxxxxxx> wrote in message
news:CA33D347-57EC-451F-A3BB-A3931D8CFF65@xxxxxxxxxxxxxxxx
Hi,

I have an application that requires the storing of personal data, name,
address, ssn with a requirement that key fields, first name, last name,
address1, city, zip, ssn be encrypted. Currently, there are over 100,000
records, and growing fast. Searches need to be done based on these key
fields (lastname like 'A%' for instance). I am loading the return set
into
SqlDataSource and GridView using .net version 2.0.

I'm using a stored procedure to return the data.

When the page first loads (which filters on lastname = 'A%', it loads
slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it
times
out.

Is there a best practice to follow in a case like this that would result
in
the best performance possible. I realize with all the encryption that it
is
very processing intensive, but with a first load at least usable, but the
second load timing out, there may be some things I should do (clear
buffers
or something) that I do not know about doing.

Can you help on this?

Thanks.


.