In lookup to get the maximum performance benefit we use Full cache or partial cache depending on the the table size... if table size is huge then partial cache would do.. but if it is small then why not use full cache mode....

It might or might not help but check if you can have intermediate table and apply join using that in SQL script.. later you may drop it ...