1
\$\begingroup\$

I'm hoping I can find a better way to do this. I've tried this with a join with multiple 'OR' statements (I know it's terrible), and with multiple joins as shown below.

SELECT  ICD_CODE1, ICD_CODE2, ICD_CODE3, icd1.DESCRIPTION AS ICD_DESCRIPTION1,
        icd2.DESCRIPTION AS ICD_DESCRIPTION2, icd3.DESCRIPTION AS ICD_DESCRIPTION3
FROM ICD_CODES
LEFT OUTER JOIN ICD_DECRIPTIONS AS icd1
    ON ICD_CODES.ICD_CODE1 = icd1.ICD_CODE
LEFT OUTER JOIN ICD_DECRIPTIONS AS icd2
    ON ICD_CODES.ICD_CODE2 = icd2.ICD_CODE
LEFT OUTER JOIN ICD_DECRIPTIONS AS icd3
    ON ICD_CODES.ICD_CODE3 = icd3.ICD_CODE

This takes place in a much larger dataset and is terribly slow. There are other fields that need a similar style join so I'm hoping someone can help me optimize.

\$\endgroup\$
3
\$\begingroup\$

Classic de-normalized data. I've seen this often in healthcare. You can create a function to do this for you. It's unlikely to be faster but will save your some typing. You'd have to test each to see which is quicker. But the bottom line is there isn't a "great" improvement method.

create function dbo.get_icd_code (@icd varchar(16))
returns varchar(256)
as 
begin
    declare @return varchar(256)
    select @return = DESCRIPTION
    from ICD_DESCRIPTIONS
    where ICD_CODE = @icd
    return @return
end

Then call it...

SELECT  
    ICD_CODE1,
    dbo.get_icd_code(ICD_CODE1) 
    ICD_CODE2,
    dbo.get_icd_code(ICD_CODE2) 
    ICD_CODE3,
    dbo.get_icd_code(ICD_CODE3) 
FROM ICD_CODES

Another way to speed it up is to stage your data first since there are so many ICD10 codes, but you'll need to provide the entire query for me to determine if this will work--primarily what other tables you are joining to and limiting from.

Lastly, you could try to un-pivot your data, join to the description field once, and then either keep it in this vertical fashion or pivot it back up. Again, hard to benchmark this against the multiple joins without being in your environment.

\$\endgroup\$
  • \$\begingroup\$ Thank you for the reply. I previously attempted the scalar function route and it was significantly less performant. I think the best way would probably be the unpivot/pivot route but it's so messy since the are 8 ICD codes and 4 CPT/HCHC codes. \$\endgroup\$ – Chandler Turner Jun 20 '17 at 21:37
  • \$\begingroup\$ I understand that. We did something similar in our HIS and there are other places where this logic has to exist unfortunately. If you are only doing this for a set amount of visits you can get some performance increase by loading those visits into a temp table and then using that to table as your base table joining to the CPT code description. It really just depends on the complexity of your over all query \$\endgroup\$ – scsimon Jun 20 '17 at 21:44
  • \$\begingroup\$ Yeah, this particular query is quite complex. I went ahead and accepted your answer even though it isn't the 'magic solution I was hoping for. Thank you again for your input. \$\endgroup\$ – Chandler Turner Jun 21 '17 at 14:21
  • \$\begingroup\$ Thanks. Unfortunately I don't think there is a magical solution aside from creating a view and indexing it which will put it on disk. I'm not a fan of this though. \$\endgroup\$ – scsimon Jun 21 '17 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.