Multiple joins for fields that need the same data. SQL Server 2008 R2

Question

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.

Answer 1

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.

• Chandler Turner: 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.
• S3S: 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
• Chandler Turner: 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.
• S3S: 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.