Paul, Take a look at your rules. Do you have any rules on those large tables your are inserting into? Rules and constraints on large tables can cause some serious performance problems. Steve already addressed below the overhead of indexes.
Troy Sosamon ===== Original Message from [EMAIL PROTECTED] at 8/26/02 7:59 am >Paul, I think I can help w/part of your problem. I often have to do INSERTs >using SELECT or LOADs fm mainframe extracts, a goodly number at the scale >you describe, and have encountered that "... the process has slowed >considerably". The cause is this, assuming that INDEXes are defined f/the >table that is being INSERTed INTO, when RBase performs an INSERT/LOAD, it >builds the INDEX "on the fly", which greatly multiplies the disk I/O. This >really bogs down the performance. > >So, in order to get a more satisfactory cycle-time f/such large loads, I do >a couple of things, some of which you might be able to try. Firstly, I >create a command file to do my INSERTs/LOADs. Yea', so what? Well, and >secondarily, the what is this, I include, in addition to my INSERT/LOAD >statement, a set of DROP INDEX statements and, more rarely, a DELETE ROWS >statement. Each of these is placed prior to the INSERT/LOAD statement. I >also issue a SET RULES off command. It might also be better to do this with >SET MULTI off, as well - I'm sure there are many other folks on this list >who can go more deeply than I into the nuance of these statements. Now, >lastly, I issue a set of (controverting) CREATE INDEX statements which >re-build those INDEXes DROPped prior to the INSERT/LOAD. > >Now, this might seem redundant, but trust me, all else being equal, this >will reduce aggregate cycle-time by a "fer piece". I go back a long way >w/RB and I recall some programmer fm Colorado who sold his own RB indexer >utility, the source code of which, according to my recollection, was >subsequently licensed by M'rim. At this point, I learned that, f/large >INSERT/LOAD operations, it was more time efficient to explicitly issue >CREATE INDEX statements rather than let RBase build the indices implicitly, >according the DDL/metadata, that is, what would be defined with the TABLE. >Believe me, (re-)building an index explicitly is WAY FAST! (This is >probably true f/most decent DBMS packages.) I'm sure you could come up >w/some simple tests to do comparisons. > >So, in short : > >-- DATE & TIME FORMAT/SEQUENCE settings according to extract definition (as >necessary &&|| applicable) >-- DROP INDEX statements >-- DELETE ROWS (as necessary &&|| applicable) >-- LOAD/INSERT statements >-- CREATE INDEX statements >RETURN > >As f/the workstation lock-up, can't offer any ideas there, short of some >sort of local/server-side timeout which is due to the excessive length of >time f/the INSERT/LOAD operation to complete. > >HTH, >Steve in Memphis > > >----- Original Message ----- >From: <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Saturday, August 24, 2002 9:45 PM >Subject: Insert Problem > > >> >> >> I am having a problem with a 4.5++ database when doing inserts to >tables. >> It started with workstation lock ups when inserting rows using "insert >into >> ... select ...". Now when I am running an old program that inserts rows >into >> a table, the process has slowed considerably. I recently did a pack and >> reload without a problem. However, there are several tables containing >> nearly a million rows and one with nearly 2.5 million rows. Any ideas? >> >> Paul Hill >> ================================================ >> TO SEE MESSAGE POSTING GUIDELINES: >> Send a plain text email to [EMAIL PROTECTED] >> In the message body, put just two words: INTRO rbase-l >> ================================================ >> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >> In the message body, put just two words: UNSUBSCRIBE rbase-l >> ================================================ >> TO SEARCH ARCHIVES: >> http://www.mail-archive.com/rbase-l%40sonetmail.com/ > >================================================ >TO SEE MESSAGE POSTING GUIDELINES: >Send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: INTRO rbase-l >================================================ >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: UNSUBSCRIBE rbase-l >================================================ >TO SEARCH ARCHIVES: >http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
