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 = 10000 -- 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 <the condition>
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]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
