May 24, 2011 at 4:37 am
hi
I have encrypted values in table which represent
account numbers.like so..
EncryptedTable
EncryptedCol..............UID
0x123456789..............A123-G564-H777-9V77
If my client comes to me with 50,000 non-encryped account numbers.What is the best way (performance wise) to retreive
the unique identifier value?
I have tried a udf. But this seems slow
Create Function getEncryptedValue
(@EncryptedCol varchar(50))
RETURNS UNIQUEIDENTIFIER
select @UID = UID
from EncryptedTable
where convert(varchar,DecryptByPassPhrase('Password',EncryptedCol)) = @EncryptedCol
RETURN @UID
END
Any ideas how to speed this up as currently too slow? (2 secs to process a record)
May 24, 2011 at 5:10 am
There are a few expections , but as a rule of thumb , never use functions on the columnar side of a comparision. SQLServer has to scan each row, process the function , then compare.
This is called 'sargability' , google that phrase and you should find some good content.
so your code ...
select @UID = UID
from EncryptedTable
where convert(varchar,DecryptByPassPhrase('Password',EncryptedCol)) = @EncryptedCol
should be
select @UID = UID
from EncryptedTable
where EncryptedCol = convert(varchar,EncryptByPassPhrase('Password',@Password))
Then , if you have and index on EncryptedCol it can be used.
May 24, 2011 at 7:54 pm
hi,
i get the 'sargability' issue. Thanks for the tip. But surely if I put encryptByPassphrase into my where clause this function generates a different result every time. Hence won't return (match) anything?
May 25, 2011 at 2:38 am
You could try a computed column with an index then, there are a few hoops you have to jump through though
http://msdn.microsoft.com/en-us/library/ms191250(v=SQL.90).aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply