DB /Query optimization

Hi Lars,

I am going to spend a bit of time on the optimization blueprint. I
have setup my system to log the full queries created by hibernate, but
already have some questions..

I selected a single data element (BCG coverage) and aggregated this
through a data mart operation.

The query looks something like this..

SELECT * FROM datavaluecrosstab WHERE periodid IN ( 12307, 12308,
12309, 12310, ... ) AND sourceid IN ( 3896, 3912, 3913, 3906 ...)

To understand what is going on there DHIS2 seems to
1) Create the temporary datavaluecrosstab table.
2) Perform a SELECT INTO/INSERT statement to populate the crosstab table.
3 )Perform a series of SELECT statements to get the data out of the
crosstab table, and then aggregate this in memory.
4) Inserts the aggregateddata back into appropriate table
(aggregatedatavalue, aggregatedindicatorvalue, or a report table)

Is the more or less the process that a data mart operation follows?



Jason P. Pickering
email: jason.p.pickering@gmail.com