RE: Deleting the records from a table

[Bob Showalter]

Mallik wrote:
> Hi Friends,
> 
> I need a help from you in implementation of purging functionality.
> I am given a hash of table(s), each having millions of records. i
> need to delete those records keeping latest 5 days records in the
> tables. 
> 
> I know the delete query like: delete from $tableName where adddtm <
> today ? $numDays ; where $numDays is the variable having number of
> days latest data to be retained. if i give the direct query like
> above it takes a lot of time to delete records from that set of
> tables. 
> 
> Do you have any idea to implement this purging that can be done in
> time for any number of tables containing millions of records in a
> loop. i need to implement this in perl, with DBI-informix interface.

This isn't really a Perl question. The first thing I would ask is what
percentage of the table's rows are you deleting? 1 percent? 50 percent? 90
percent? If the percentage is low, you can improve performance by creating
an index on the date column and using a properly optimizable WHERE clause.
If the percentage is very high, you might offload the rows to keep to a temp
table, truncate the big table (which is usually fast), and reload the rows
to keep.

There isn't going to be a Perl approach that is faster than a simple "DELETE
FROM foo WHERE date < ?" to delete a bunch of rows from the table.

This is a database question, not a Perl question. Have you pursued this on
Informix lists?

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

RE: Deleting the records from a table

[Mallik]

Hi Jose,

Thanks for your prompt reply.

I too thought of same solution, but we don't want to delete the original
table. Any other solution is greatly appreciated.

Thanks & Regards,
Mallik.

-Original Message-
From: José Pedro Silva Pinto [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 14, 2004 5:02 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Deleting the records from a table


Hi,

It songs a funny solutions but...

Create a TEMP table and insert latest 5 days data

After delete the table and rename temp table


José Pinto


-Original Message-
From: Mallik [mailto:[EMAIL PROTECTED]
Sent: terça-feira, 14 de Dezembro de 2004 11:31
To: [EMAIL PROTECTED]
Subject: Deleting the records from a table


Hi Friends,

I need a help from you in implementation of purging functionality.
I am given a hash of table(s), each having millions of records. i need to
delete those records keeping latest 5 days records in the tables.

I know the delete query like: delete from $tableName where adddtm < today ?
$numDays ; where $numDays is the variable having number of days latest data
to be retained. if i give the direct query like above it takes a lot of time
to delete records from that set of tables.

Do you have any idea to implement this purging that can be done in time for
any number of tables containing millions of records in a loop. i need to
implement this in perl, with DBI-informix interface.

Thanks in advance,
Mallik.

--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>

RE: Deleting the records from a table

[Jenda Krynicky]

From: Bob Showalter <[EMAIL PROTECTED]>
> Mallik wrote:
> > Hi Friends,
> > 
> > I need a help from you in implementation of purging functionality. I
> > am given a hash of table(s), each having millions of records. i need
> > to delete those records keeping latest 5 days records in the tables.
> > 
> > 
> > I know the delete query like: delete from $tableName where adddtm <
> > today ? $numDays ; where $numDays is the variable having number of
> > days latest data to be retained. if i give the direct query like
> > above it takes a lot of time to delete records from that set of
> > tables. 
> > 
> > Do you have any idea to implement this purging that can be done in
> > time for any number of tables containing millions of records in a
> > loop. i need to implement this in perl, with DBI-informix interface.
> 
> This isn't really a Perl question. The first thing I would ask is what
> percentage of the table's rows are you deleting? 1 percent? 50
> percent? 90 percent? If the percentage is low, you can improve
> performance by creating an index on the date column and using a
> properly optimizable WHERE clause. If the percentage is very high, you
> might offload the rows to keep to a temp table, truncate the big table
> (which is usually fast), and reload the rows to keep.
> 
> There isn't going to be a Perl approach that is faster than a simple
> "DELETE FROM foo WHERE date < ?" to delete a bunch of rows from the
> table.
> 
> This is a database question, not a Perl question. Have you pursued
> this on Informix lists?

Agreed, this is a DB question :-)

One more thing to try is to limit the number of rows affected by a 
DELETE statement and do some looping.

Eg. it's sometimes faster to use something like:

-- sorry MS SQL syntax, I've never used/seen Informix
Declare @ID int, @chunksize int, @maxid int
SET @ID = 0
SET @chunksize = 1 -- best size depends on your DB

select @maxid = max(id) from TheTable WITH (NOLOCK)

WHILE (@id < @maxid) BEGIN
DELETE FROM TheTable
 WHERE ID >= @ID and ID < @[EMAIL PROTECTED]
   and 
SET @ID = @ID + @chunksize
END


Actually if it's garanteed that the IDs grow over time and you do 
want to delete all that were created before some date you might as 
well find the ID of the newest one you want to delete and then delete 
all that have their ID <= the one you found. Maybe in a loop like the 
one above.

Jenda
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed 
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

RE: Deleting the records from a table

[José Pedro Silva Pinto]

Hi,

It songs a funny solutions but...

Create a TEMP table and insert latest 5 days data

After delete the table and rename temp table


José Pinto


-Original Message-
From: Mallik [mailto:[EMAIL PROTECTED]
Sent: terça-feira, 14 de Dezembro de 2004 11:31
To: [EMAIL PROTECTED]
Subject: Deleting the records from a table


Hi Friends,

I need a help from you in implementation of purging functionality.
I am given a hash of table(s), each having millions of records. i need to
delete those records keeping latest 5 days records in the tables.

I know the delete query like: delete from $tableName where adddtm < today ?
$numDays ; where $numDays is the variable having number of days latest data
to be retained. if i give the direct query like above it takes a lot of time
to delete records from that set of tables.

Do you have any idea to implement this purging that can be done in time for
any number of tables containing millions of records in a loop. i need to
implement this in perl, with DBI-informix interface.

Thanks in advance,
Mallik.

--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]