David, -)
In am going to answer my own dumb question. I forgot to take the word master out of the delete line and change it to charges. No wonder this is slow. Testing, testing, testing and double checking can sure make a difference and save you grief. Steve -----Original Message----- From: Steve Breen [mailto:[EMAIL PROTECTED] Sent: Friday, April 29, 2005 11:06 AM To: [email protected] Subject: [RBASE-L] - Re: Deleting row question David, We are not making any structure changes so that is not a issue. Just purging some data and creating an archive file until the new unlimited Database size R-base comes out. We are coming close to a 2 Gig limit. So far we have two tables of six deleting quickly and I implemented the same concept for this table and do not get the same speed results. Would the Foreign Key Reference cause this? Please note this table snap shot was taken after the chargeid index was dropped from charges. Thanks Steve SET VAR vTime1 TIME = NULL SET VAR vTime2 TIME = NULL SET VAR vElap INTEGER = NULL SET VAR vMessage TEXT = NULL SET TIME FORMAT HH:MM:SS SET VAR vTime1 = .#TIME DROP INDEX CHARGEID IN CHARGES Del rows from master where pon in (sel pon from deltable) CREATE INDEX CHARGEID ON CHARGES(CHARGEID) SET VAR vTime2 = .#TIME SET VAR vElap = (.vTime2 - .vTime1) SET VAR vMessage = ('Time elapsed in trial two:' & CTXT(.vElap) & 'seconds') PAUSE 1 USING .vMessage=70 Table: Charges No Lock(s) No. Column Name Attributes --- ------------------ ------------------------------------------------------ 1 ChargeID Type : INTEGER AUTONUMBER Comment: Unique identifier 2 PON Type : TEXT 12 Index : SINGLE-COLUMN Comment: Breakdown Identifier in MASTER table 3 ChargeType Type : INTEGER Consrnt: FOREIGN KEY REFERENCES ChargeTypes 4 Invoice Type : INTEGER 5 CAmount Type : CURRENCY Comment: Amount to charge client Current number of rows:1061895 -----Original Message----- From: David M. Blocker [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 6:46 AM To: [email protected] Subject: [RBASE-L] - Re: Deleting row question Steve OK, now I can tell you the down side of the second method - since you making structural changes to the table in question as well as deleting rows, you need to add some code to LOCK the table because in a multi-user setting the DROP and CREATE commands could fail if another user is attempting to add, delete or update rows in the table in question. SET VAR gError INTEGER = NULL SET ERROR VAR gError LABEL LockTable SET LOCK tablename ON IF gError <> 0 THEN DIALOG 'This process requires exclusive access to the table xxxxx, but + others are using the table. Do you want to try again now?' vAnswer vKey IF vAnswer = 'Yes' and vKey <> '[Esc]' THEN GOTO LockTable ELSE GOTO EndFile ENDIF ENDIF -- now the code to drop indexes, delete rows and recreate indexes LABEL EndFile SET LOCK tablename OFF CLE VAR etc... David David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Steve Breen" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[email protected]> Sent: Tuesday, April 26, 2005 10:08 PM Subject: [RBASE-L] - Re: Deleting row question > > > David, > > The second method is faster by far. > > It seems that the more rows that are deleted the slower it gets. Ran the > second option first and it just flew thru the process the second one is > running now and it is going to hours. > > I am just going to have to stop it. > > Will use the new concept > > > Thanks David and Razzak > >
