Performance Issue with Inner Join

[xcaliber2222]

Hello, here is the join in my stored proc:

dbo.TEST AS o INNER JOIN
dbo.TEST2 AS i ON LTRIM(RTRIM(o.ICD9_PC1)) = LTRIM(RTRIM(REPLACE(i.ICD9, '.', ''))) OR
LTRIM(RTRIM(o.ICD9_PC2)) = LTRIM(RTRIM(REPLACE(i.ICD9, '.', ''))) OR
LTRIM(RTRIM(o.ICD9_PC3)) = LTRIM(RTRIM(REPLACE(i.ICD9, '.', '')))

Obviously this join will contribute to performance issues. My understanding is that if I can cast this to a numeric data type it should help. Can someone please show me an example of how I would do this in this case, with the replace statements?

Another issue is one table has 11,000 records, no big deal. But the other table has 707,233 records, so this I know is a problem as well.

I have clustered/non-clustered indexes on the joined fields accordingly.

I ran an execution plan and the clsutered index scans are returning about 50% each.

Any help would be greatly appreciated.

Thanks,
Alejandro

 

[gmmastros]

I have clustered/non-clustered indexes on the joined fields accordingly.

Indexes on the joined columns will not help you because you are using functions on the columns. As soon as you use a function, indexes are pretty much ignored. For some interesting reading on this [google]SQL Server Sargable[/google].

There are things you can do to speed up this query, but it's likely going to cost you database space. What I'm thinking is.... You could add computed columns for those involved in this join, then you could index the computed columns.

Ex:

Alter Table Test Add ICD9_PC1_Trimmed As LTrim(RTrim(ICD9_PC1))

Alter Table Test Add ICD9_PC2_Trimmed As LTrim(RTrim(ICD9_PC2))

Alter Table Test Add ICD9_PC3_Trimmed As LTrim(RTrim(ICD9_PC3))

Create Index idx_Test_ICD9_PC1_Trimmed On Test(ICD9_PC1_Trimmed)

Create Index idx_Test_ICD9_PC2_Trimmed On Test(ICD9_PC2_Trimmed)

Create Index idx_Test_ICD9_PC3_Trimmed On Test(ICD9_PC3_Trimmed)

Alter Table Test2 Add ICD9_Trimmed As LTRIM(RTRIM(REPLACE(ICD9, '.', '')))

Create index idx_Test2_ICD9_Trimmed On Test2(ICD9_Trimmed)

Then, you could change your query to:

dbo.TEST AS o INNER JOIN
     dbo.TEST2 AS i 
         ON o.ICD9_PC1_Trimmed = i.ICD9_Trimmed
         OR o.ICD9_PC2_Trimmed = i.ICD9_Trimmed
         OR o.ICD9_PC3_Trimmed = i.ICD9_Trimmed

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom