Thanks for the assistance, I will use this as a base with some tweaks for my solution.

Thanks for taking the time to understand the issue and assist me on this.

I have just noticed that my script does not work for the scenario, where ID 198 is passed in, it misses out ID 129.

This is because of the where clause...

Hi Steve,

Sorry, my explanation on this might not have been adequate. Let me try to explain again.

From the table BenID and BenIDNew are the movement between Beneficiaries.

What I need to...

Hi Luis,

Yes, there can be duplicates and circular references, that is the complexity.

The business case is as follows.

The table is basically a list of actions that cause movement between beneficiaries,...

Hi Steve,

Thanks for taking a look and the feedback. I did not think of that approach.

I get exactly the same IO numbers as yours.

Looking at the execution plans side by...

You might also want to look at the Plan Cache, I am sure the express version allows you to make use of the Dynamic Management Views and Functions.

As a start, make sure that you have the latest service packs installed.

Have you looked through the execution plan and query statistics?

This could be due to a number of issues...

seems to be happening on tables with cascading deletes on the relationship. when i go into the diagram the relationship disappears. when i take off the cascading deletes it stays.

...