Euformatics

A lot of time has passed since I posted the first part of this blog series, which is mostly due to customer projects. Lately, I have also been busy implementing our NGS cancer analysis pipeline and software for representing the results. The product is really exciting, and we will post about it in the near future.

In the first post of this series I implemented an efficient query that calculates a histogram of microarray measurement data. In this post I will show my favourite method of implementing visualization in web environment. In the early days of web development we used to produce lots of code writing HTML tags to a stream to the browser. Today, several frameworks exist to make the life of the developer a lot easier. My favourite tool to build a data-centric web application is Oracle APEX. It is a declarative rapid web application development tool and it executes completely inside the database. Instead of writing the explicit steps how to create a user interface, APEX provides a point-and-click application builder (which is also implemented using APEX) where the developer creates pages using built-in components and plugins. APEX provides configurable components (reports, charts, buttons, input fields, etc) and wizards to help creating pages or regions based on existing database objects, such as tables.

A declarative model is great for productivity in cases where the requirements can be implemented using the provided functionality. The downside is the decreased productivity when something a bit more exotic is needed. If the provided component does not support extending the functionality, then the functionality has to be implemented by printing HTML/javascript/css fragments.

I was reading a blog entry by Jan Aerts where he uses MongoDB to calculate statistics of SNPs from the 1000genomes project in different populations: CEU (European descent), YRI (African) and JPTCHB (Asian). The question was, how many SNPs are in common between those populations. Jan Aerts used Ruby and MongoDB to answer that question. He reported execution time of calculating the statistics to be about 55 minutes in his laptop. That is unreasonable for this smallish data. After all, there are only about 30 million SNP entries, or 800 MB of data. I will demonstrate here how the same thing can be done in about 3 minutes including data loading.

So, I decided to try the same exercise using Oracle XE, the free (and limited) edition of Oracle database. The most important limitation here is memory as Oracle XE can use only total of 1 GB of memory. I gave most of it to PGA, which is program global area and used for sorting and hash operations. Since I am the only user of this database, I changed the memory management of work areas to manual mode and allocated more memory for hash and sort operations: