June 3, 2009 at 4:12 am
[font="Tahoma"]
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
[/font]
June 3, 2009 at 8:28 am
Can you get an execution plan and attach it? Here's a video on JumstartTV that explains how to upload an execution plan to SSC (free registration required).
By using a UDF on the column you eliminate the possibility of an index seek. I have not worked with encryption, but I would assume you could Encrypt the criteria value and compare encrypted values.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 3, 2009 at 9:52 pm
Try the following query instead
Select * from [MYTABLE] where [MYCOLUMN]= [dbo].[the Function I created for Decryption]('THE VALUE')
This allows SQL to utilise any indexes that it thinks appropriate.
June 4, 2009 at 1:43 am
I think you actually need to use:-
Select * from [MYTABLE] where [MYCOLUMN]= [dbo].[the Function I created for Encryption]('THE VALUE')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply