Alex,
You're able to alter the autocommit value during processing:
$dbh->{AutoCommit} = 0;
$dbh->do( qq{insert into yucky values ('A')} );
$dbh->commit;
$dbh->{AutoCommit} = 1;
$dbh->do( qq{truncate table yucky} );
$dbh->{AutoCommit} = 0;
$dbh->do( qq{insert into yucky values ('A')} );
$dbh->commit;
Do you need the use of a temp database table? If so have you
looked at using a memory resident table (DBD::AnyData)?
Another thing I've found that helps is to cache reference or lookup data.
Memoize is an easy way to cache. This only works for data that doesn't
change between queries select x from y where z = 1 always returns 'F' ...
Tom
On Fri, Aug 23, 2002 at 11:21:22PM +0900, Alex Paulusberger wrote:
> Tom,
> thanks. I do use DELETE FROM since truncate is not an option in
> trnansaction blocks
> and within DBI , autocommit has to be turned off when connecting to the DB.
>
> But maybe you are right, and the overhead not being able to truncate
> tables is bigger than
> not using transaction blocks.
>
> Regards
> Alex
>
> Tom Lane wrote:
>
> >Another thought...
> >
> >Alex Paulusberger <alexp@meta-bit.com> writes:
> >
> >>The whole process loops 4,500 times.
> >>For every loop
> >>- a temp table is cleared
> >>
> >
> >How exactly are you clearing the temp table? DELETE FROM isn't a good
> >plan because you'll still have dead tuples in there. You could do a
> >DELETE FROM and then VACUUM, but I'd suggest TRUNCATE instead.