1>.
Make a computed column (with persisted) named "ShipTranNo"on
the third table "ILSS"
Alter Table ILSS
Add
ShipTranNo as LEFT(SHIPMENT_ID,) PERSISTED
2>.
Now create an index on this column "ShipTranNo"
Create Index Idx_ILSS_ShiptranNo ON ILSS(ShipTranNo)
3>....