encryption optimization

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.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.../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@.community.nospam> wrote in message
news:CA33D347-57EC-451F-A3BB-A3931D8CFF65@.microsoft.com...
> 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.|||Hi Acsla,
I am interested in this issue. Would you mind letting me know the result of
the suggestions?
I noticed that you bind the query result to a Gridview, so I guess that the
loading time (20s and time out) refers to the whole loading process when
all data has been displayed in your GridView. Does the Gridview have a
paging function? According to my experience, if the query result is also
huge, the render time of the control may also spend a long time.
Basically I agree with Dan's suggestions on SQL. Additionally, I would like
your checking how long it will spend if you separately run the SQL
statement in Query Analyzer.
If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql