August 21, 2008 at 12:31 am
Hi All,
I have been trying to find some resources on the performance implications of using either a self referencing table or a link table (with ParentID and ChildID pointing back to same table)? A colleague suggested a link table can take better advantage of indexes than a self-referencing table, even if a self-referencing table is cleaner. Performance will be a big consideration, so I have been trying to find any information comparing these two that might answer if link tables are in fact better for performance, but have come up short.
Any help would be most appreciated!
Thanks,
Steve
August 21, 2008 at 4:51 am
Please give the exact scenario as the Performance depends upon many factors.
It depends upon data and requirement.
Thanks,
Amit Khanna
August 21, 2008 at 6:14 am
As amit has said performance largely depends on design. I don't think, and I have done no testing, that a self-join will perform any better or worse than a join on another table. If you have proper indexes on the table in a self-join it should perform just as well as a join to a different table with proper indexes. As far as the Query Processor is concerned you are accessing 2 tables regardless.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 10:03 pm
Hi Jack,
Thanks for those bits of information - that does a lot in answering my questions - some general guidelines / principles were what I needed. I realise performance depends on many factors, but we still have much to do in designing how this part of the application will work, so I can't really provide anything for scrutiny. However, those general principles will help for now!
Thanks again for your replies.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply