While trying to look at the data model of dhis2, I was surprised to see that a table called “datavaluecrosstab” is holding 185 columns (basically the number of data elements).
A cross tabulated query must have created this pivoted, denormalized output, but why is it still existing in the database?? Basically the cross tabulated table should have been a temporary table generated by a query on data mart processing and should have been dropped when the query processing is done. Either that, or we should use multiple threads to process different parts of the data marts tree…
Is this a bug?? Or do we have some explanation why this table is still in the database?? Or is it a point where we can optimize code??
Another observation is that we have a few tables that are using MyISAM Engine and most others are InnoDB Engine. I don’t see why we are using MyISAM for only a few tables. If we are thinking that MyISAM will increase performance, then we are following a myth that has been dismissed many times when using latest versions of MySQL.
···
Regards,
Saptarshi PURKAYASTHA
Director R & D, HISP India
Health Information Systems Programme
On Sat, Mar 28, 2009 at 2:46 PM, Saptarshi Purkayastha sunbiz@gmail.com wrote:
While trying to look at the data model of dhis2, I was surprised to see that a table called “datavaluecrosstab” is holding 185 columns (basically the number of data elements).
A cross tabulated query must have created this pivoted, denormalized output, but why is it still existing in the database?? Basically the cross tabulated table should have been a temporary table generated by a query on data mart processing and should have been dropped when the query processing is done. Either that, or we should use multiple threads to process different parts of the data marts tree…
Is this a bug?? Or do we have some explanation why this table is still in the database?? Or is it a point where we can optimize code??
The datamart removes the datavaluecrosstab after the export process finishes. This must be caused by an aborted or failed export process, you can just delete it.
Another observation is that we have a few tables that are using MyISAM Engine and most others are InnoDB Engine. I don’t see why we are using MyISAM for only a few tables. If we are thinking that MyISAM will increase performance, then we are following a myth that has been dismissed many times when using latest versions of MySQL.
This must be caused by someone in India modifying the database engine of these tables. Innodb is default and all my databases from SL, SA etc use only innodb tables.
At the start of a new datamart process there is a function that drops this table if it exists, and then populates it. So we could do that, but it wouldn’t really give us anything…