Subject Re: [ib-support] Mass deletion
Author Woody
From: "Nataraj S Narayanan" <nataraj@...>
> The Tables to delete data are Drug_Bill and Drug_Bill_Item ,where the 2nd
> table has a foriegn key of Drug_Bill by name Bill_Id.
> As said by Helen I gave the command 'Delete from Drug_Bill_Item where
> Bill_id in (select Bill_id from Drug_bill Where Bill_Date between
> '01-JAN-1999' and '31-DEC-1999')
> Unfortunately, the operation takes aeons (7 hours) to complete. The gdb is
> 119
> Mb. These 2 tables are the most populous. I used IB_SQL which seems to be
> developed using IBO.
> Frequently the IB_SQL on NT Workstation stops responding after
> sometime. After resetting i got a deadlock. Sometimes unable to access the
> table.

This should be done in a procedure which should increase the speed

(Excuse any errors please, this is off the top of my head :)

create procedure RemoveBetweenDates(Date1 DateTime, Date2 DateTime)
Declare Variable Bill_ID Int;
For Select Bill_ID from Drug_Bill where Bill_Date between :Date1 and
into :Bill_ID do
Delete from Drug_Bill_Item where Bill_ID = :Bill_ID;
Delete from Drug_Bill where Bill_ID = :Bill_ID;

Woody (TMW)