store hierarchy information in a MS Access DB for faster queries

Question

I'm trying to figure out how I can store hierarchical type information in a MS Access DB so that queries will be faster. An use case example might make more sense.

• I have a table that has two fields
  1. a name
  2. a hierarchy
• a hierarchy is an X # of level folder structure:
  • \a\b\c\d
  • \a\b\c\d\e
  • \a\b\c\d\f\g
  • \a\b\h
  • \a\b\i\j
  • you get the idea
• the table will be filled with 300,000 rows
• each row will have a name and a hierarchy

At this point:

• if I want to find all the names that are in a hierarchy, including sub-hierarchies I can run a like query: where [hierarchy] like '\a\b\*'
• I can even do wildcard joins even though MS Access's query design GUI doesn't handle it and I have to use the SQL view: join on [hierarchy] like '\a\b\*'.

But it can be very slow. Especially if my joins get complex.

So I thought maybe there is a way to create another table that would all the hierarchies and it would maintain parent/child relationships and the first table would reference a row in it. And then, somehow, I could use it to find rows in the first table that match hierarchies and sub-hierarchies in the second table.

However, I have no clue if this is even possible and how I would go about it. Any advice is appreciated.

Answer 1

In Oracle we use the hierarchal structure where each row has a reference to its parent. Then with the CONNECT BY clause you can connect these rows to each-other.

You should take a look here: simulation of connect-by in sql-server