The procs call other procs which call some more procs. A join to a large table (+/-2million records) gets repeated in several of these procs.
So, I changed the deepest nested proc to return all the data which will be needed by the other procs. I removed the repeated join; and the performance problem is solved.
So, two very important tips for performance tuning queries:
- Only return the records you are interested in; and
- Only touch a record once if possible.