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/
