Sir, this application has nolock hints everywhere because this is a very very heavy gaming database and this is what the management wants to stick to.

Also, you are seeing so many records returned because the execution plan that I sent you is from a query for the partner which had most number of players.

I think a lot of such joins occur because of bad schema during the initial phase and not many predict how the data is going to behave 1 year later. also, in this case any indexing is not helping.

Thanks

Chandan