Multiple table aliases performance problem

[prevost999]

I am attempting to write a stored procedure via SQL:

The problem is this:
I am using 12 table aliases(instances) of the same table to get my results, and it is really slowing down the processing time of the query.

Details:
In the table I am using there is a field called Item and a second field called SubItem. For the first item, there may be 5 sub-items, all 5 of which also have unique records in the table, so I have to tie the Sub-Item to the Item of the same table to get the details of the 2nd level. Because this chain can go as far as 12 levels, I have to create 12 table aliases to drill down to the level I want.

Question:
Is there any better way of doing this, so the performance time can be increased? Any help is appreciated.

 

[fluteplr]

Make sure you are indexed properly on the item to sub item join. You should have indexes on item and subitem.


You might also try creating a view that has all the joins in it and selecting from that. It may not be any faster but it beats the heck out of coding those joins more than once. I know I have a very similar set up on one of my projects (only nine levels deep though).

Thinking about this some more I also created mine the other way (item and parentitem) bottom up instead of your top down. This allowed me to create a clustered index (MS SQL Server) on parentid which seemed to speed things up quite a bit. I am not sure in your situation if creating subitem as a clustered index would help but it is probably worth trying.