[V4WS] question on deleting records

[Luca Porfiri]

Il giorno 15-03-2003 8:45, Ruslan Zasukhin, sunshine at public.kherson.ua ha
scritto:

> on 3/15/03 2:01 AM, Luca Porfiri at liste at mail.sting.it wrote:
> 
>> Hi Ruslan and everyone,
>> 
>> I am searching for hints on deleting records from a very big BaseObject with
>> a very big index.
>> Let me describe how big:
>> 
>> the db is
>> .blb = 1,57 Gb
>> .dat = 425 Mb
>> .ind = 253 Mb
>> 
>> the main BaseObject has 230.000 record and the amount of index depends on
>> this BaseObject.
>> Every day I update the db and index with 4Mb of data ca.
>> The index is big enough to contain, at now, 98% of words contained in the
>> new records I go to add to db and WS + Valentina update is very fast at
>> least.
>> I use this procedure:
>> 
>> read all the file to import
>> split it by record using \r to know number of records
>> then i repeat for every record:
>> make a cursor with all fields I will populate
>> select with itemAt the record in the list of records I want to process
>> split it in fields (using tab in the text of record as separator)
>> then valSetBlankRecord
>> then valAddRecord and valSetField fields with values of the fields in
>> record to add
>> finally valUpdateRecord
>> and back again to the next record and so on ...
>> It's fast enough to add and update also the indexes ... could we do better?
>> 
>> NOW
>> 
>> I need to delete old records but I fear it because delete records means
>> update also indexes and this operation, I believe, is not so fast as adding
>> records (but why?) I believe, instead, it could be very very slow ... or
>> not?
>> So could it be a way to do this fast?
> 
> Delete must be as fast as Add.
> 
> It is update() command take = time of delete + time of Add.
> So it should be in average 2 times slower.

So you say that for a BaseObject with indexed fields:

select and delete a record
is fast as or more than
add new one and update?

> 
>> Is better to select little cursors and deleteAll or delete record by record
>> or other?
> 
> Well, DeleteAll should be faster in theory.
> 
> If you have so big db of important data, I hope you do backups.
> So after backup you can try DeleteAll for single cursor.
> 
> You need this operation only once?

No. I wish to "update" db adding data of today and deleting data of 3 months
ago. So every day, if possible, I wish to execute this task automatically.
In this way I will have a back-up db with all records but I will publish
also a smart and very fast db with data since 3 months ago to today and the
user will choose if he wishes to query the old db or recent one.
One field of records is "publication date" I will use to decide what and
when delete.

Now I fear for indexes. I have not done a test since now, but I read some
threads on list and it seems very slow deleting indexed records. Every time
I delete a record the process involves indexes also and it has to delete its
words in index. So, this seems very slow, much more then add a record and,
so doing, update index also, isn't it?
I could try to delete a record or more records removing index flag for the
fields in baseobject; so deleting will be very fast but then I will have to
reindex all records!
I hope to be wrong.

Luca