Is there an effcient hierarchy method for storing a large tree in a SQL table?

I have a table with well over 5 millions rows, that contains hierarchical data (~20 levels). The table is growing exponetia开发者_运维问答lly every year and the recursive method for CRUD operations from the table is becoming slow. The table recieves a high volume of updates, reads and deletes. Does any one know of any data models that would be suitable to replace the current Adjacency List Model, or what steps if any to speed up the table?

Have you looked at the HierachyID data type which is available in SQL Server 2008 onwards. http://technet.microsoft.com/en-us/library/bb677290.aspx

There's a good section on it's use in this free e-book from MS Press

http://blogs.msdn.com/b/microsoft_press/archive/2009/11/16/free-e-book-introducing-microsoft-sql-server-2008.aspx

1. Five million rows is nothing.

2. There is a difference between a well-designed Adjacency List model and a badly-designed one. If you post your DDL maybe we could improve it, rather than you throwing out the whole concept, because th eimplementation is poor.

3. In any case, I would not implement a tree structure or an hierarchy in a Relational database using such a model. I have use the following (ignore the History), hundreds of times, and it is very fast. If you provide the DDL for the table and all indices, I can provide a model specifically for it.

Data Model

▶Tree Structure Data Model◀

Readers who are unfamiliar with the Relational Modelling Standard may find ▶IDEF1X Notation◀ useful.

Maybe a hierarchical or graphical database would be better choices. SQL isn't always the answer - that's why NoSQL is a viable niche.