Get list of blood samples where at least one RNA extract exists

[Nicklas Nordborg]

Some time in the future a process to extract DNA from the collected blood samples will be started. But this should only be done for patients if there also exists at least one specimen with RNA extracted.

[Nicklas Nordborg]

To begin with, it is interesting to just count the current number of blood samples that we have and that also have at least one specimen with RNA extracted. Here is an SQL command that can be used to query directly on the MySQL server:

select year(spreg.entry_date), count(distinct blood.id), count(distinct blood.parent_id)  
from `BioMaterials` rna 
inner join `BioMaterials` sp on substring(rna.name, 1, 9)=substring(sp.name, 1, 9)
inner join `BioMaterialEvents` spreg on spreg.biomaterial_id=sp.id and spreg.event_type=1
inner join `BioMaterials` cse on cse.id = sp.parent_id
inner join `BioMaterials` blood on blood.parent_id=cse.parent_id 
where rna.subtype_id=51 and sp.subtype_id=48 and blood.subtype_id=56
group by year(spreg.entry_date)
;

[Nicklas Nordborg]

Some interesting performance information about the above SQL query. Run times on some different servers:

• base2 production server, CentOS, MySQL 5.0.51a (32-bit): 220s
• Windows 7, MySQL 5.0.51a (64-bit), default configuration: 10s
• Windows 7, MySQL 5.0.51a (64-bit), base2 configuration: 30s
• Windows 7, MySQL 5.5 (64-bit), default configuration: 12s
• Windows 7, PostgreSQL 9.1, default configuration: 0.5s

Main surprise is that it takes so long time on the production server. It's old but not that old....

The other surprise is the difference between PostgreSQL and MySQL on the Windows 7 developer computer.

Important note!!! When switching MySQL 5.0.51a between default and base2 configuration it complained about corrupt database files, and the database dump had to be re-imported. Do not modify the configuration on the production server without a backup for safety!

[Nicklas Nordborg]

Milestone renamed

[Nicklas Nordborg]

This should not be too complicated with the help of BASE features that has been implemented since this ticket was created. Use item lists with synchronization filters and the possibility to use columns from parent items in list tables.

[Nicklas Nordborg]

Milestone renamed