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
>
>
