Opened 11 years ago

Closed 23 months ago

#476 closed task (worksforme)

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

Reported by: Nicklas Nordborg Owned by: Nicklas Nordborg
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 (4)

comment:1 by Nicklas Nordborg, 11 years ago

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 11 years ago by Nicklas Nordborg (previous) (diff)

comment:2 by Nicklas Nordborg, 11 years ago

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 by Nicklas Nordborg, 8 years ago

Milestone: Reggie v3.xReggie v4.x

Milestone renamed

comment:4 by Nicklas Nordborg, 23 months ago

Resolution: worksforme
Status: newclosed

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.

Note: See TracTickets for help on using tickets.