#186: Querying age by numerical constraints
-----------------------------------------+----------------------------
Reporter: huhickman | Owner: huhickman
Type: problem | Status: assigned
Priority: major | Milestone: data-domains2
Component: data-stds | Resolution:
Keywords: age valueset obesity-cohort | Blocked By:
Blocking: 67 |
-----------------------------------------+----------------------------
Comment (by huhickman):
In some crude timing experiments with age (judging by the GUI counter in
the i2b2 web client) , it is slower with the CRC_ENABLE_UNITCD_CONVERSION
set to ON, but not substantially so.
||= Query ||= CRC_ENABLE_UNITCD_CONVERSION=OFF ||=
CRC_ENABLE_UNITCD_CONVERSION=ON ||
|| < 10 years || 3.4 seconds || 3.8 seconds ||
|| < 100 years || 28 seconds || 31.9 seconds ||
The query generated in the <100 case is as follows:
< 100 years, CRC_ENABLE_UNITCD_CONVERSION=OFF
{{{
#!sql
insert into BlueHeronData.QUERY_GLOBAL_TEMP (patient_num, panel_count)
with t as (
select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */
f.patient_num
from BlueHeronData.observation_fact f
where
f.concept_cd IN (select concept_cd from BlueHeronData.concept_dimension
where concept_path like '\i2b2\Demographics\Age\Age Range\%')
AND ( modifier_cd = '@' AND
(( valtype_cd = 'N' AND nval_num < 36525 AND tval_char IN ('E','LE'))
OR
( valtype_cd = 'N' AND nval_num <= 36525 AND tval_char = 'L' )) )
group by f.patient_num
)
select t.patient_num, 0 as panel_count from t
}}}
CRC_ENABLE_UNITCD_CONVERSION=ON
{{{
#!sql
insert into BlueHeronData.QUERY_GLOBAL_TEMP (patient_num, panel_count)
with t as (
select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */
f.patient_num
from BlueHeronData.observation_fact f
where
f.concept_cd IN (select concept_cd from BlueHeronData.concept_dimension
where concept_path like '\i2b2\Demographics\Age\Age Range\%')
AND ( modifier_cd = '@' AND (( valtype_cd = 'N' AND case
when units_cd = 'YEARS' then nval_num * 365.25
when units_cd = 'MONTHS' then nval_num * 30.4375
when units_cd = 'DAYS' then nval_num end < 36525 AND
tval_char IN ('E','LE')) OR ( valtype_cd = 'N' AND case
when units_cd = 'YEARS' then nval_num * 365.25
when units_cd = 'MONTHS' then nval_num * 30.4375
when units_cd = 'DAYS' then nval_num end <= 36525 AND tval_char =
'L' )) )
group by f.patient_num
)
select t.patient_num, 0 as panel_count from t
}}}
Turning on the CRC_ENABLE_UNITCD_CONVERSION option allows the storing of
values in heterogenous units - and does the conversion in SQL for the
various possible convertible units in the metadata_xml field.
The default setting is OFF, and i2b2 assumes that the facts are stored in
normalized units.
It would be interesting for another site to repeat the timing experiment
as well.
--
Ticket URL:
<http://informatics.gpcnetwork.org/trac/Project/ticket/186#comment:10>
gpc-informatics <http://informatics.gpcnetwork.org/>
Greater Plains Network - Informatics
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
