January 20, 2012 at 10:54 am
Hello,
This DB was not built by me. SQL 2005 DB.
Basics Employee code, name and certain things are encrypted in this DB.
None of the table has primary key, so the reports are taking more than 3 minutes as we have to decrypt and use.
Encrypted fields are using varbinary(256).
I tried creating primary key but not improving much of the performance but i have to improve it.
Kindly suggest. Kindly let me know if more details required.
Regards
Durai Nagarajan
January 20, 2012 at 11:08 am
durai nagarajan (1/20/2012)
Hello,This DB was not built by me. SQL 2005 DB.
Basics Employee code, name and certain things are encrypted in this DB.
None of the table has primary key, so the reports are taking more than 3 minutes as we have to decrypt and use.
Encrypted fields are using varbinary(256).
I tried creating primary key but not improving much of the performance but i have to improve it.
Kindly suggest. Kindly let me know if more details required.
so are you doing select * from the table, and that takes 3 minutes to decrypt, or are you talking about a single row in the table and getting the data back takes three minutes?
selecting everything and decrypting is gonna take too long, i'd bet, and is a bit of a bad design;
you'd need some sort of ability to see a limited, unencrypted selection of header records, and only allow drilling into a single record instead, I'm thinking.
so much depends on the details on your side.
Lowell
January 20, 2012 at 11:39 am
Does the encryption/decryption take place in the database or in a higher layer?
How many rows at a time are you decrypting?
Does the data have to be pulled from the table, then decrypted, then filtered?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2012 at 11:59 am
Hello,
i have populated two tables using joins for all employees in main table which is taking 3 or more minutes.
This Db has just 5 months data which took me 3 minutes , i cant imagine if the DB has 2 years or more data.
More over we have to build more modules in this and we cant change the ecryption now easily which has some modules built on it.
is there any other way to improve with encyption and primary key on binary data with improved performance.
Regards
Durai Nagarajan
January 20, 2012 at 12:04 pm
It depends on what you're doing and how you're doing it.
It possibly can be made faster, but I can't suggest anything to do that without answers to the questions I already asked.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2012 at 12:43 pm
Hello,
answers are below
Does the encryption/decryption take place in the database or in a higher layer? --- Database
How many rows at a time are you decrypting? --- for some reports all data (hope without primary key will scan everything even if i give condition)
Does the data have to be pulled from the table, then decrypted, then filtered? --- yes
Kindly help now.
Regards
Durai Nagarajan
January 20, 2012 at 1:04 pm
Okay.
Depending on your decryption method, decrypting a whole table all at once is probably going to be slow, no matter what you do in the database. Encryption/decryption is very CPU-intensive. Just the nature of the beast.
What can be done in many cases, if you want just a few rows and have a Where clause you're trying to build, is encrypt the values in the Where clause, and compare those to the encrypted values in the table, instead of decrypting the whole table and then filtering it based on a Where clause on the decrypted values.
Looks like this:
create table #T (
ID int identity primary key,
MyEncryptedColumn varbinary(8000));
insert into #T (MyEncryptedColumn)
values (hashbytes('MD5', 'Hello')),(hashbytes('MD5','World'));
-- Stored proc code
declare @InputParam varchar(100) = 'Hello';
select *
from #T
where MyEncryptedColumn = hashbytes('MD5',@InputParam);
Something like that is likely to run a lot faster than querying the whole table (on a real table with lots of rows), and then decrypting all of it and filtering it.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 24, 2012 at 11:47 am
Hello,
we are using DecryptByKey and EncryptByKey for decyiption and encryption.
i am using employee code to pull the data in that table
but due to encryption in employee code and no primary key (full table scan is happening).
In development server i have tried with primary key but without improvement as encrypted values for same employee code is different for it.
currently we have 3 -4 month data if it takes 3 minutes for my big report to pull data, after 2 or 3 years when the data will be huge it may take up to 10 -15 minutes.
hope my doubt is right. hoping that if i cant change the DB structure i dont have solution as well.
Regards
Durai Nagarajan
January 24, 2012 at 12:50 pm
Do you have a Where clause in your query? Is it against an encrypted column?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 24, 2012 at 1:12 pm
yes , almost all conditions are on encrypted columns.
Regards
Durai Nagarajan
January 25, 2012 at 6:15 am
Are they equality tests? As in @Value = Column. Or are they range/inequality tests, like @Value > Column, or Column between @Value1 and @Value2?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2012 at 6:17 am
It is in @Value = Column types
Regards
Durai Nagarajan
January 26, 2012 at 7:17 am
Encrypt your input parameters using the same encryption method as you data. Compare the encrypted parameter value to the encrypted column value in your Where clause. That should speed the queries up substantially.
I have a sample of doing that earlier in this thread. I used the MD5 hash for encryption, but you can use whatever encryption you want.
The key thing is, encrypt the parameter and compare it to the raw data. Don't decrypt the table and compare to the unencrypted parameter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply