sql - How to optimize join

Question

I am writing SQL like below. It is causing scanning of table "TBLA" every time which is a big table. I don't have any option to use indexing due to table design. How can i optimize the below one so that i don't have to scan tables again and again. Also I need to keep conditions for TBLB i.e. there are different condition for TBLB in each join. Appreciate any help

Thanks in advance :)

Sel SUB1.COLX,
    SUB1.COLY,
    SUB1.COLZ,
    SUB2.COLX,
    SUB2.COLY,
    SUB2.COLZ,
    SUB3.COLX,
    SUB3.COLY,
    SUB3.COLZ
FROM    
TBLA 
LEFT  JOIN
(SELECT COLX , COLY, COLZ FROM TBLB WHERE rec='123' )  SUB1
ON TBLA.SK=SUB1.SK
LEFT  JOIN
(SELECT COLX , COLY, COLZ FROM TBLB WHERE rec='456' )  SUB2
ON TBLA.SK=SUB2.SK
LEFT  JOIN
(SELECT COLX , COLY, COLZ FROM TBLB WHERE rec='789' )  SUB3
ON TBLA.SK=SUB3.SK

Answer 1

At a guess, this should lower the query to a single scan/seek rather than 3. Tbhis does, however, assume that your lateral joins only return 1 row per value of SK:

SELECT {Table A Columns},
       oa.Sub1,
       oa.Sub2,
       oa.Sub3
FROM dbo.TableA A
     OUTER APPLY (SELECT MAX(CASE B.rec WHEN '123' THEN COLX END) AS Sub1, --If rec is actually a numerical datatype, don't wrap it in single quotes (')
                         MAX(CASE B.rec WHEN '456' THEN COLX END) AS Sub2, --If rec is actually a numerical datatype, don't wrap it in single quotes (')
                         MAX(CASE B.rec WHEN '789' THEN COLX END) AS Sub3  --If rec is actually a numerical datatype, don't wrap it in single quotes (')
                  FROM dbo.TableB B
                  WHERE B.SK = A.SK) oa;

Of course, you'll still need the relevant indexes here; for TableB, that would be on the column SK and rec and COLX would want to at least be in the INCLUDE.