We have a table which has a relationship between customers:
ex: Table A
child id | Relationship id
Need to populate a final table with 20 levels.
like A-B-C-D etc
right now query is having 20 left joins on Table A with relationship id = child id.
As the data is very huge in Table A, facing spool space issue. Collect stats also didnt help much on performance.
Need a way to tune the performance.
May be you can use the work tables to join other tables in one sql and use that in final sql for joining with table A.
I have used same type of strategy when I have some repeating joins in my views.
But this adds a maintenence of refreshing the work tables.