Steve
Try this test
Backup the database:
Test 1:
Create and run this program
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
del rows from bdnotes where pon in (sel pon from deltable)
SET VAR vTime2 = .#TIME
SET VAR vElap = (.vTime2 - .vTime1)
SET VAR vMessage = ('Time elapsed in trial one:' & CTXT(.vElap) & 'seconds')
PAUSE 1 USING .vMessage=70
Test 2
Restore from backup
Create and run this file
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 noten IN bdnotes
DROP INDEX time_stamp IN bdnotes
DROP INDEX BD_U_P IN bdnotes
del rows from bdnotes where pon in (sel pon from deltable)
CREATE INDEX Noten ON bdnotes(noten)
CREATE INDEX Time_Stamp ON bdnotes(Time_Stamp)
CREATE INDEX BD_U_P ON bdnotes(BD_U_P)
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
What times do you get in the two methods?
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 6:22 PM
Subject: [RBASE-L] - Re: Deleting row question
> So lets say I am trying to delete rows from the BDNotes table structured
as
> below
>
> R>list bdnotes
>
> Table: bdnotes No Lock(s)
>
> No. Column Name Attributes
> --- ------------------ ------------------------------
> 1 noten Type : INTEGER AUTONUMBER
> Index : SINGLE-COLUMN
> 2 POn Type : TEXT 12
> Index : SINGLE-COLUMN
> 3 time_stamp Type : DATETIME
> Index : SINGLE-COLUMN
> 4 notes Type : NOTE 228
> 5 BD_U_P Type : INTEGER
> Index : SINGLE-COLUMN
> Current number of rows:2277914
>
>
>
> Next table is a single column table projected from master based on
specific
> criteria.
>
> 1 POn Type : TEXT 12
> Index : SINGLE-COLUMN
>
>
> Command used for deletion is: del rows from bdnotes where pon in (sel pon
> from deltable)
>
> How does this look. It runs slow. The process is deleting 365,000 records
or
> so.
>
>
>
> -----Original Message-----
> From: A. Razzak Memon [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 26, 2005 4:49 PM
> To: [email protected]
> Subject: [RBASE-L] - Re: Deleting row question
>
>
> At 05:05 PM 4/26/2005, Steve Breen wrote:
>
> >How would you speed up the process?
>
> In a WHERE clause, R:BASE uses only one index (except when
> joining tables). By knowing which indexed column the R:BASE
> optimizer use in a WHERE clause, you can structure your
> conditions so that the optimizer uses the most unique (and
> thus most helpful) index. This is where knowing your data
> helps you to modify command(s) and gain speed.
>
> On the other hand, if you are LOADing many rows of data at
> a time, it is considerably faster to DROP the indexes, load
> the data, and then rebuild the indexes. It is faster for
> R:BASE to build the indexes in one chunk rather than updating
> them as each row is loaded. You can optimize this by using
> CREATE INDEX command.
>
> Tip:
> Always RELOAD database after deleting lots of rows.
>
> Hope that helps!
>
> Very Best R:egards,
>
> Razzak.
>
>
>
