Opened 5 years ago

Last modified 2 years ago

#476 new task

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

Reported by: nicklas Owned by: nicklas
Priority: major Milestone: Reggie v4.x
Component: net.sf.basedb.reggie Keywords:
Cc:

Description

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.

Change History (3)

comment:1 Changed 5 years ago by nicklas

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)
;
Last edited 5 years ago by nicklas (previous) (diff)

comment:2 Changed 5 years ago by nicklas

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!

comment:3 Changed 2 years ago by nicklas

  • Milestone changed from Reggie v3.x to Reggie v4.x

Milestone renamed

Note: See TracTickets for help on using tickets.