We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred different research projects, and each project needs its own version of the data, with certain columns of the data encrypted to a project-specific value, as well as other restrictions (like only providing certain rows and/or columns that a project has permission to us).
Here is a simplified example of some of the data:
Hospital_spell persion_id spell_id hospital_id admission_dt discharge_dt 1234 9023 12 2012-09-04 2012-09-05 1234 1111 12 2014-01-01 2014-01-01 9876 5432 8 1999-04-27 2000-02-29 Hospital_diagnosis spell_id hospital_id diag_num diag_code 9023 12 1 A12 9023 12 2 C45
These two tables link on spell_id and hospital_id. For each project that uses this data, we need to provide a version of the data with person_id, spell_id, and hospital_id encrypted to new values (this restriction limits unauthorised combination of data from different projects). Also, a given project might only get data from certain years or for certain people (however many projects get the entire dataset).
How can we provide these multiple versions of the data efficiently, in terms of performance and storage? Ideally this would mean not copying all of the data for each project, and having only a limited performance hit for each project's use of the data (30% slower than querying the original tables would be fine, but not 300% slower).
Options we have considered:
- Making a copy of the data for each project. This results in no performance hit, but when each project has potentially billions of rows, it takes up a lot of storage space with mostly duplicated data.
- A view of the original table that encrypts on the fly with a scalar function. Uses the least storage space, but performance is not acceptable, particularly when the encrypted columns are often used for joins (as above).
- A view of the original table that joins to an additional table with precomputed encrypted values. This has been our preferred option, but it turns out to also perform quite poorly. Queries against these views are many times slower than against the original table. We have worked on optimising indexing, but it hasn't solved this.
Example of the third option:
Additional table: person_id_encrypted person_id person_id_e 1234 1A30495810293D0293CB2093A3FED2B View: create view hospital_spell_encrypted as ( select person_id_e, spell_id_e, hospital_id_e, admission_dt, discharge_dt from hospital_spell join person_id_encrypted on hospital_spell.person_id = person_id_encrypted.person_id join spell_id_encrypted on hospital_spell.spell_id = spell_id_encrypted.spell_id join hospital_id_encrypted on hospital_spell.hospital_id = hospital_id_encrypted.hospital_id )
Does anyone have any thoughts on how we can get better performance? So far, just copying the data for each project looks like the only workable solution.
We are using DB2 9.7 LUW, running on an IBM Blue C Supercomputer with multiple parallel servers.