Re: Hierarchical design question

->From: connolly@stimpy.eecis.udel.edu (Thomas Connolly) ->Subject: Hierarchical design question ->Date: 28 May 1994 00:47:46 GMT ->Reply-To: connolly@stimpy.eecis.udel.edu (Thomas Connolly) ->Organization: University of Delaware, Newark -> ->Attention database gurus: -> ->I am currently investigating how to migrate a rather large hierarchical ->based application running on a DEC VAX to a relational DBMS. The [1] ->application consists of around 3 million lines of PL1/Fortran code ->going against a Codasyl (NEXUS) database. It is extremely fast and ->complex. -> ->I would like to hear from anyone that feels qualified to comment [2] ->on this effort in general and specifically on the following approach ->that we are considering: -> ->As it stands today, the application accesses the NEXUS database ->through a relatively small set of IO routines (between 6 and 10). [4] ->For this reason, we are thinking about trying to leave the application ->unchanged and more or less slip NEXUS out and slip the RDBMS in its ->place. Perhaps the biggest concern with this idea is in the area ->of performance - assuming for a moment that we could mimmick the [5] ->existing hierarchical, record-at-a-time architecture - I do not ->have a good feel for how this will perform (I guess I'm scared to ->death that it will choke on the heavy volume of transactions). -> ->There are several other serious issues which may doom any approach ->other than a complete re-design, but the cost savings ->in this approach is potentially enormous and we are committed to ->at least trying to build and analyze some sort of reasonable ->benchmark. -> ->Anyone thoughts/ideas/suggestions/advice or whatever that might [3] ->help enlighten us would be greatly appreciated. Please direct ->responses directly to me at connolly@udel.edu - I will summarize ->and post the results. -> ->Thanks, -> ->Tom Connolly ->e-mail: connolly@udel.edu ->voice: (215)359-9858 Tom, [1] Are you considering DEC's RDB, or are you planning to go to a third party DBMS, such as Oracle or Informix? Is this under VMS or Ultrix? [2] I have had reasonable success migrating applications from a true hierarchical (IBM's IMS) database to relational. Performance was satisfactory on the relational system. Of course, almost anything is better than IMS! ;-) [3] Some Codasyl network databases convert to relational better than others. If your DB is truly hierarchical in structure, then you should have no problems. If your owner-record -> group -> member-record structures are used to implement true hierarchies, then you can reproduce this by: a. defining tables for the owner-records (master) and member-records (detail), b. copying the master id key into the detail record, before the detail id key, c. if you were using ordered groups, then you probably need to add a sequence number to the detail id key. If your owner-record -> group -> member-record structures implement m-to-n relationships, then you can reproduce this by: a. defining tables for the owner-records and member-records, presuming each has some unigue key, b. defining a third table to contain one row for each owner-member link; this table would contain only the keys for each record: two columns in the simple case, more if either/both keys are composite. [4] It sounds as tho' you have done a good job of isolating your task logic from your I/O logic, so this is feasible. Your level of success will depend in part on your structure, as described in [3]. [5] You will need to define numerous indexes on your tables, to improve performance. If you heavily update key fields, then this could cause the relational version to update more slowly than the Codasyl version, due to index rebalancing. If you do not update key fields often, then relational DB performance should equal or exceed network DB performance on update, especially when updating records several layers down in the hierarchy. For queries and reports, the relational performance will be the same or slightly poorer than Codasyl if you use a lot of information from the owner records. However, if you only want owner id info along with member detail info, then relational can out-perform Codasyl by only reading the member records (which already contain the owner keys) and bypassing reading down thru the hierarchy. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\