The simple, generic database schema used by i2b2 has very much contributed to its success, as it simplifies the loading of data and does not require changes to its table structure when new data elements are added. However, with large datasets, the placement of all fact data in a single table can be detrimental to query performance. The goal of this best practice guide is to summarize possible performance optimizations which do not require changes to the i2b2 application code. The measures described include database-level modifications (indexing, partitioning) as well as query strategy. During our tests, performance gains of up to 80x could be reached by applying these measures.
The optimizations described were developed and tested on i2b2 v1.6 with an Oracle 11g database