0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89

1 Answers1

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

Community
  • 1
  • 1
hetOrakel
  • 1,590
  • 1
  • 10
  • 23
  • Sounds like I have to use recursive calls which has bad performance, especially because my hierarchy will have multiple levels. Thank you! – IMTheNachoMan Jul 17 '15 at 14:33
  • 1
    These structure is perfectly capable of storing multiple levels. And the right query gives all the results in one call... So i don't understand what you are saying. – hetOrakel Jul 17 '15 at 14:48
  • if I want all names in a parent hierarchy then I'd have to make multiple calls -- one for each sub folder. right? – IMTheNachoMan Jul 18 '15 at 20:08
  • 1
    No. Read this http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions164.htm and this http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm There are equivalent SQL statements possible in other DB's – hetOrakel Jul 19 '15 at 22:21
  • It looks like MS Access does not have this functionality. Thank you for your help! – IMTheNachoMan Jul 27 '15 at 19:33