>For example, query lookup tables to local cursors and then run your main query joining with those.

Or query the biggest table with just those conditions that relate to it, then join the result with the lookups. Back in 2000, I got a speedup of up to 20x on that alone.