trying to improve performance of a join in SQL query

Question

I have somewhat of an "ugly" situation and this slows down my query significantly. I have two tables I'm trying to join...

Table1

ID          Val1          Val2         Data1         Data2
1           123           BAL          Apple         California
2           345           NAL          Microsoft     Washington
3           566           HAO          Google        New York

Table2

ID          Val         Ind          Data     
1           123-BAL       Y            Some value
2           566-HAO       N            Other value

My query looks like this:

Select * from Table1 t1 JOIN Table2 t2 on (t1.Val1 + '-' + t1.Val2) = t2.Val and Ind = 'Y'

DESIRED RESULT:

ID          Val1          Val2         Data1         Data2
1           123           BAL          Apple         California

But this query is extremely slow. I cannot change the indexes on the tables, but is there another way I can re-write this query to improve performance. It's not a perfect situation but any improvement would be helpful.

Answer 1

This is your query:

Select *
from Table1 t1 JOIN
     Table2 t2 
     ON (t1.Val1 + '-' + t1.Val2) = t2.Val and t2.Ind = 'Y';

I think this will take advantage of an index on table2(Ind, Val). But, you might also want to try an index in Table1. To do this, define a computed column, create an index, and then use the column in the query:

alter table table1 add val as (t1.Val1 + '-' + t1.Val2) persisted;

create index idx_table1_val on table1(val);
Select *
from Table1 t1 JOIN
     Table2 t2 
     ON t1.Val = t2.Val and t2.Ind = 'Y';