in reply to which method is more faster?

Preparing a query that is used multiple times is almost ALWAYS faster and more efficient than ->do statements. The database has to parse and preprocess EVERY DO statement. Prepare gets the parsing and preprocessing done for you, and spends more time on work than overhead.

BTW... in a prepared statement, d='?' isn't needed. you should use d=? (Binding variables SHOULD quote it for you).

Replies are listed 'Best First'.
Re^2: which method is more faster?
by Hercynium (Hermit) on Jul 25, 2007 at 17:23 UTC
    My understanding is that Perl extracts the hash keys into a list in-memory when you use the keys operator (function?).

    When I know I'll be working with large hashes like this, I tend to use each, so the key/value pairs are fetched from the hash one-at-a-time.

    If you're using lots of memory, give this a try: 
    my $dbh=DBI->connect("xxxx"); # My DB is SQLSERVER 2005
my $sth=$dbh->prepare(qq{    UPDATE tab SET d=? WHERE id=?});
for (my ($key,$val) = each %hash) {
    $sth->execute($hash{$key},$val);
}
$sth->finish;
$dbh->disconnect;

    [download]
[reply]
[d/l]
Re^2: which method is more faster?
by oha (Friar) on Jul 26, 2007 at 14:48 UTC
    just to extend it a bit, using "bind variables" will not guarantee that the database will not preprocess it, but it'll do it often. the optimizer will optimize every query if there is not a cached optimization, and cacheing is performed on the query string itself, so bind variables allow you to have the same statement string (same optimization then) for every different "variable value". Note: a backdraw is that optimization over a bind variable could not be always efficient as using the real value (think about range-scan) but usually it's never encouraged 'cauz the gain is very low, and will cache-out something that could be usefull. Oha
[reply]

In Section Seekers of Perl Wisdom