Performance of large boolean expressions in where clause #1777
Comments
|
Hi @paulolaup, thanks for sending us this issue! Would you mind also sending us the version of the query that uses subsumes? |
|
Sure. This is just a simple one querying the Condition resources but it should suffice. |
|
Hi @paulolaup, This particular style of query doesn't perform very well in the main release of Pathling, but the refactoring that we are working as part of our SQL on FHIR implementation has actually resolved this, and it seems to perform very well. This implementation is still WIP, and can be found here: #1775 This style of query is not ideal because you have to maintain a static set of codes inline within the query. I would suggest either putting the codes in a value set and using a terminology server, or actually rendering RxNorm as a CodeSystem that you can query in arbitrary ways. We have done some work on rendering RxNorm in FHIR - if you are interested in this I can enquire about whether we can open source this or provide it to you some other way. |
|
Thx for the information. This will be very helpful in the future, although I won't be able to integrate it into the project I'm currently working on. I considered the alternative of using value sets but ultimately decided against it, as currently, I would probably have to generate them for each occurrence in the queries I'm using. Unfortunately, I currently have to operate under the assumption that features of the FHIR Terminology API are not necessarily available in the environment I run the queries. Nevertheless, being able to expand implicit value sets as is currently possible for SNOMED CT concepts would indeed be the best solution. Thank you for the offer. I would indeed be interested. Am I correct in assuming you refer to an extension of the capabilities of the Ontoserver? |
|
No, it wouldn't be an extension of Ontoserver. I am talking about a standard FHIR CodeSystem rendering of RxNorm that uses standard features of FHIR to represent the various attributes of each code using properties and designations. Any FHIR terminology server that supports these basic features should be able to support their use in value set definitions. For example, here is a snippet of what the RxNorm CodeSystem might look like: {
"resourceType": "CodeSystem",
"id": "rxnorm",
"url": "http://www.nlm.nih.gov/research/umls/rxnorm",
"version": "20220703",
"name": "RxNorm",
"title": "RxNorm",
"status": "draft",
"experimental": "true",
"valueSet": "http://www.nlm.nih.gov/research/umls/rxnorm/vs",
"content": "complete",
"property": [
{
"code": "NDC",
"type": "string"
},
{
"code": "ORIG_CODE",
"type": "string"
},
{
"code": "ORIG_SOURCE",
"type": "string"
},
{
"code": "RXN_ACTIVATED",
"type": "string"
},
{
"code": "RXN_AI",
"type": "string"
},
{
"code": "RXN_AM",
"type": "string"
},
{
"code": "RXN_AVAILABLE_STRENGTH",
"type": "string"
},
{
"code": "RXN_BN_CARDINALITY",
"type": "string"
},
{
"code": "RXN_BOSS_FROM",
"type": "string"
},
{
"code": "RXN_BOSS_STRENGTH_DENOM_UNIT",
"type": "string"
},
{
"code": "RXN_BOSS_STRENGTH_DENOM_VALUE",
"type": "string"
},
{
"code": "RXN_BOSS_STRENGTH_NUM_UNIT",
"type": "string"
},
{
"code": "RXN_BOSS_STRENGTH_NUM_VALUE",
"type": "string"
},
{
"code": "RXN_HUMAN_DRUG",
"type": "string"
},
{
"code": "RXN_IN_EXPRESSED_FLAG",
"type": "string"
},
{
"code": "RXN_OBSOLETED",
"type": "string"
},
{
"code": "RXN_QUALITATIVE_DISTINCTION",
"type": "string"
},
{
"code": "RXN_QUANTITY",
"type": "string"
},
{
"code": "RXN_STRENGTH",
"type": "string"
},
{
"code": "RXN_VET_DRUG",
"type": "string"
},
{
"code": "RXTERM_FORM",
"type": "string"
},
{
"code": "TTY",
"type": "string"
},
{
"code": "consists_of",
"type": "code"
},
{
"code": "constitutes",
"type": "code"
},
{
"code": "contained_in",
"type": "code"
},
{
"code": "contains",
"type": "code"
},
{
"code": "dose_form_of",
"type": "code"
},
{
"code": "doseformgroup_of",
"type": "code"
},
{
"code": "form_of",
"type": "code"
},
{
"code": "has_dose_form",
"type": "code"
},
{
"code": "has_doseformgroup",
"type": "code"
},
{
"code": "has_form",
"type": "code"
},
{
"code": "has_ingredient",
"type": "code"
},
{
"code": "has_ingredients",
"type": "code"
},
{
"code": "has_part",
"type": "code"
},
{
"code": "has_precise_ingredient",
"type": "code"
},
{
"code": "has_quantified_form",
"type": "code"
},
{
"code": "has_tradename",
"type": "code"
},
{
"code": "ingredient_of",
"type": "code"
},
{
"code": "ingredients_of",
"type": "code"
},
{
"code": "part_of",
"type": "code"
},
{
"code": "precise_ingredient_of",
"type": "code"
},
{
"code": "quantified_form_of",
"type": "code"
},
{
"code": "reformulated_to",
"type": "code"
},
{
"code": "reformulation_of",
"type": "code"
},
{
"code": "tradename_of",
"type": "code"
}
],
"concept": [
{
"code": "1000000",
"display": "TRIBENZOR 40 MG / 5 MG / 12.5 MG Oral Tablet",
"designation": [
{
"use": {
"code": "PSN"
},
"value": "TRIBENZOR 40 MG / 5 MG / 12.5 MG Oral Tablet"
},
{
"use": {
"code": "SBD"
},
"value": "amlodipine 5 MG / hydrochlorothiazide 12.5 MG / olmesartan medoxomil 40 MG Oral Tablet [Tribenzor]"
},
{
"use": {
"code": "TMSY"
},
"value": "amLODIPine 5 MG / hydroCHLOROthiazide 12.5 MG / olmesartan medoxomil 40 MG Oral Tablet [Tribenzor]"
},
{
"use": {
"code": "SY"
},
"value": "Tribenzor 40/5/12.5 (olmesartan medoxomil / amlodipine (as amlodipine besylate) / HCTZ) Oral Tablet"
},
{
"use": {
"code": "TMSY"
},
"value": "Tribenzor 40/5/12.5 (olmesartan medoxomil / amLODIPine (as amLODIPine besylate) / HCTZ) Oral Tablet"
},
{
"use": {
"code": "SY"
},
"value": "Amlodipine 5 MG / HCTZ 12.5 MG / Olmesartan medoxomil 40 MG Oral Tablet [Tribenzor]"
},
{
"use": {
"code": "TMSY"
},
"value": "amLODIPine 5 MG / HCTZ 12.5 MG / Olmesartan medoxomil 40 MG Oral Tablet [Tribenzor]"
}
],
"property": [
{
"code": "NDC",
"valueString": "65597011507"
},
{
"code": "NDC",
"valueString": "65597011510"
},
{
"code": "NDC",
"valueString": "65597011530"
},
{
"code": "NDC",
"valueString": "65597011570"
},
{
"code": "NDC",
"valueString": "65597011590"
},
{
"code": "RXN_AI",
"valueString": "{316047} 5487"
},
{
"code": "RXN_AI",
"valueString": "{329528} 104416"
},
{
"code": "RXN_AI",
"valueString": "{359114} 118463"
},
{
"code": "RXN_AM",
"valueString": "{316047} 5487"
},
{
"code": "RXN_AM",
"valueString": "{329528} 17767"
},
{
"code": "RXN_AM",
"valueString": "{359114} 321064"
},
{
"code": "RXN_AVAILABLE_STRENGTH",
"valueString": "5 MG / 12.5 MG / 40 MG"
},
{
"code": "RXN_BOSS_FROM",
"valueString": "{316047} AI"
},
{
"code": "RXN_BOSS_FROM",
"valueString": "{329528} AM"
},
{
"code": "RXN_BOSS_FROM",
"valueString": "{359114} AI"
},
{
"code": "RXN_HUMAN_DRUG",
"valueString": "US"
},
{
"code": "RXTERM_FORM",
"valueString": "Tab"
},
{
"code": "TTY",
"valueString": "PSN"
},
{
"code": "TTY",
"valueString": "SBD"
},
{
"code": "TTY",
"valueString": "SY"
},
{
"code": "TTY",
"valueString": "TMSY"
},
{
"code": "consists_of",
"valueCode": "316047"
},
{
"code": "consists_of",
"valueCode": "329528"
},
{
"code": "consists_of",
"valueCode": "359114"
},
{
"code": "consists_of",
"valueCode": "999998"
},
{
"code": "has_dose_form",
"valueCode": "317541"
},
{
"code": "has_ingredient",
"valueCode": "1372732"
},
{
"code": "parent",
"valueCode": "1182147"
},
{
"code": "parent",
"valueCode": "1182686"
},
{
"code": "parent",
"valueCode": "999970"
},
{
"code": "tradename_of",
"valueCode": "999996"
}
]
},
{
"code": "1000001",
"display": "olmesartan medoxomil 40 MG / amLODIPine 5 MG / hydroCHLOROthiazide 25 MG Oral Tablet",
"designation": [
{
"use": {
"code": "PSN"
},
"value": "olmesartan medoxomil 40 MG / amLODIPine 5 MG / hydroCHLOROthiazide 25 MG Oral Tablet"
},
{
"use": {
"code": "SCD"
},
"value": "amlodipine 5 MG / hydrochlorothiazide 25 MG / olmesartan medoxomil 40 MG Oral Tablet"
},
{
"use": {
"code": "SY"
},
"value": "amlodipine (as amlodipine besilate) 5 MG / HCTZ 25 MG / olmesartan medoxomil 40 MG Oral Tablet"
},
{
"use": {
"code": "TMSY"
},
"value": "amLODIPine (as amLODIPine besilate) 5 MG / HCTZ 25 MG / olmesartan medoxomil 40 MG Oral Tablet"
},Then you could do queries like: |
|
Very interesting. This would indeed be a good solution. Although the query expression would probably get more complex if one tries to get all (however distantly) related concepts. Would the FHIR Terminology API allow the resolution of ValueSets along RxNorm Default Paths used by the RxNorm-API, as well as RxNav? This would save multiple API calls, when trying to get related concepts, for instance, when trying to get products, product names, their synonyms etc. containing some specific ingredient. |
|
@paulolaup I think that this solution is limited to the immediate relationships, transitive relationships are not included. If you want to play around with this more or modify, here are some links to some work that @jmandel, @lawley and others have been working on as a proof of concept:
Something else that is worth keeping an eye on is the development of VCL (ValueSet Compose Language), which will be a way of doing the types of queries that you are referring to. Here is a link to a presentation about it at last year's DevDays: https://www.youtube.com/watch?v=f2gRFXwuoTE |
|
I've recently moved scripts to https://github.com/jmandel/fhir-concept-publication-demo/tree/main/scripts/rxnorm as part of regenerating and updated https://github.com/jmandel/fhir-concept-publication-demo/blob/main/CodeSystem-rxnorm-03042024.ndjson.gz from the latest release. |
|
Closing this now, feel free to re-open or send us any further issues! |
Describe the bug
I'm working with Synthea data and while terminologies like SNOMED CT can be easily queried using dedicated Pathling FHIRPath terminology functions (subsumption testing), this is not an option when querying medication data (e.g. MedicationAdministration) where RxNorm codes are used. In my particular situation I resolve a RxNorm ingredient concept identifier to concept identifiers for concepts which represent medicinal products containing said ingredient to query the data. Since the terminology functions (as far as I'm aware) do not provide the means to resolve this relationship I fell back to providing the associated codes explicitly in the query:
code.coding.where(system='...' and (code='...' or code='...' or ...))When using this approach with larger sets of codes, query execution takes an unexpected amount of time compared to queries using the subsumes function even with small data sets (1000 patient records).
To Reproduce
Expected behavior
I would have expected similar performance to queries using the subsumes terminology function as, either which way, I assume the coding values in the FHIR instance data would be compared to a set of codes.
Please let me know, if this is just an issue caused by how I wrote the FHIRPath expressions and there are other arrive at the answer I'm seeking.
Pathling version: 6.4.0
Example query:
query.json
The text was updated successfully, but these errors were encountered: