Efficiency issues in dispensing query

[writetoritu]

looking into it.

[writetoritu]

At CHOP, 7M records (dispensing type drug exposures) get translated to about 840M records to be inserted into the dispensing table (due to the nature of RxNorm -> NDC mapping).

The insertion operation is taking long (yet to time it - haven't been able to finish the execution so far).

[toanong]

It'll take some time to insert 840M records. So I'd wait for it to complete. Does the dispensing table have any foreign key relationship with other tables?

[writetoritu]

prescribingid (from prescribing table) - which we have defaulted as null as of now.

[toanong]

Thanks! Was the foreign key constraint defined in the DDL? I don't think so however, if the constraint is there, you can temporarily disable it by using this command: SET FOREIGN_KEY_CHECKS=0. That'll boost the INSERT operation up a bit.

[mgkahn]

Ritu: "At CHOP, 7M records (dispensing type drug exposures) get translated to about 840M records to be inserted into the dispensing table (due to the nature of RxNorm -> NDC mapping)."

Interested in knowing why there is a 100x expansion going from dispensing records into dispensing tables. I don't understand the "due to the nature of RxNorm --> NDC mapping". You should not be taking a single RxNorm code and inserting every possible NDC code that ls linked to that code. It sounds like this is what is happening. If you have true med dispensing, then you have the actual NDC code in the dispensing record (probably as source_concept_id). If so, that single NDC code goes into the NDC field in the PCORnet CDM dispensing table. Am I off-base regarding the cause of this 100x expansion of records?

I also would want to make sure that you are not attempting to put medication orders records into the PCORnet CDM Dispensing table (don't think you are but want to make sure). They go into the PCORnet CDM V3 Prescribing table.

[writetoritu]

Thanks Michael for looking into this as I wasn't very confident of what's going on here.

Some background:

• yes, we are only inserting dispensing records into the dispensing table see code here
• 7.7M (drug exposure records) ----> 999M (dispensing records): So, this is >100x expansion.

Finding the NDCs

• We have been taking RxNorm code stored in drug concept id and finding every NDC corresponding to it (through the concept relationship table). This is the cause of that huge expansion.
• Thanks for your suggestion - we will directly use the drug source concept id to determine the NDC concept codes.

I will post a PR soon.