Steve,
Yes, the FK can have an impact. Rules and triggers can also slow a delete.
What R:Base has to do to delete even one row is check rules, then execute any before delete trigger, then delete the row, then execute any after delete trigger, then rebuild each index that is affected, (including possibly the PK index in chargetypes, if it is no longer a "referenced" primary key). Then R:Base can go on to the second row.
The frequency of duplicates in your indexed data also affects the speed of the index adjustments, since R:Base might have a lot of steps to go through in a linked list for any one row. ChargeID, because it is unique, may be the fastest index to update.
Even disk fragmentation and database fragmentation can affect performance with huge tasks like this. Database fragmentation is cleaned up when you do a "RELOAD." Rows from within the same table are moved together physically within the database. Disk fragmentation is repaired under Windows Accessories/System Tools (when nobody is using the database, obviously).
No promises, but you might try using a DECLARE CURSOR loop based on the values in deltable.pon, and doing DELETE FROM MASTER WHERE PON = .VPON once for each loop, rather that using the IN clause.
Bill
Steve Breen wrote:
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
