Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Design Question(long and very complicated)

Design Question(long and very complicated)

From: ME <me_at_home.now>
Date: Sat, 30 Mar 2002 06:32:58 GMT
Message-ID: <vnmaauc7e41svq2hc93oto5897be1b98hk@4ax.com>


I am a programmer and have come across a project which is something I have not dealt with before. I am a wannabe DBA and this design is pushing my limits of knowledge and skill. I would appreciate any help from someone who has done something similar or has any ideas they would like to share. This is long and tricky, so please bear with me.

Now the situation. I am basically creating a record management type system, and in this system records will be loaded daily into the system. Quantities ranging from hundreds to thousands, with lengths of records being a max length of about 100k bytes. These records are in many formats, ASCII, EBCDIC, packed decimal, binary, etc. Before the record can enter the system, via a batch program, a layout for the record must be defined. This layout details how to decode the record. The record needs to be decoded from whatever format it entered the system as, into a viewable format. Meaning, ASCII text, readable Dates, readable Numbers, etc. Modifications can be made to the records when displayed, then saved back to the original layout. At a certain time, another batch utility is run and it needs to remove any records that have not been modified and records that have been modified, but at seperate times.

So this boils down to the need to insert many records into the db with binary data. Then at a later time, selectively retrieve and remove records. My simplified schema is like this:

CREATE TABLE LAYOUT
(

  LAYOUTID INTEGER NOT NULL,
  LAYOUT_NAME VARCHAR(25) NOT NULL,
  PRIMARY KEY(LAYOUTID)
)

CREATE TABLE LAYOUT_FIELD
(

  LAYOUT_FIELD_ID INTEGER NOT NULL,
  /* assume some fields describing a layoutfield */   LAYOUTID INTEGER NOT NULL,
  PRIMARY KEY(LAYOUT_FIELD_ID),
  FOREIGN KEY(LAYOUTID) REFERENCES LAYOUT(LAYOUTID) )

CREATE TABLE RECORD
(

  RECORDID INTEGER NOT NULL,
  LAYOUTID INTEGER NOT NULL,
  RAW_DATA BLOB NOT NULL,
  PRIMARY KEY(RECORDID),
  FOREIGN KEY(LAYOUTID) REFERNCES LAYOUT(LAYOUTID) )

I decided to go with BLOB's for storing the records in their original format. On a side not, I also attempted using VARCHAR for the raw data in the record, and then convert the binary to Base64 then insert it. Dropped, that idea, performance was horrible. So in any case, The RECORD table will be getting large volumes of records inserted and deleted daily. Performance to select on the RECORD table and then decode each record to a displayable format was pretty bad. So what I did was create a dynamic table based on the layout, and decode the raw data one time when it enters the system, and then insert it into readable format in the dynamic table. There is a dynamic table for each layout. Believe it or not, this works very well for viewing decoded records. So well in fact that we added the ability for rules that are executed on the dynamic tables so the user doesn't have to view all the records at once.

The problem lies in the insert and remove of large volumes into the RECORD table. The original requirement was for an all or none input and delete of records. This quickly became impossible. When records in the thousand began entering the system the rollback segments took a beating trying to hold it all for one transaction. Not to mention when another user was attempting to insert or delete records at the same time. So the best I could do was reduced the transaction to do 100 inserts or deletes per transaction. That sped the system up very nicely. Now records can be inserted and deleted simultaneously with decent performance. Not the ideal situation, but it happens. Testing with volumes of 50k records with the db running on a dual 1GHZ windows NT box with 1G of RAM is pretty good. But when upping the volumes to 100K or more, performance starts to die off. I have basically come to my limit with this system. I did all I know what to do to make it perform, but it isn't enough. Do you see any flaw in my design? Any ideas for improvements? Or am I just at the limit of the machine that the db is running on and I just need more hardware.

Any feedback would be greatly appreciated.

ME Received on Sat Mar 30 2002 - 00:32:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US