in reply to Fast Building of SQL Statements

I don't see this as an optimization so much as a style question. As others have mentioned, the query building speed is unlikely to matter. I'd prefer this: 
my @FIELDS = qw( field1 field2 field3 );
my @PLACEHOLDERS = ('?') x @FIELD;
my $sql = do {
     local $" = ',';
     "INSERT (@FIELDS) INTO foo VALUES (@PLACEHOLDERS)";
};

[download]
WRT optimization, since you're using a closure to keep a constant list of field names inside of, what's the point of rebuilding the SQL for every query? 
{
    my $SQL;

    BEGIN {
        my @FIELDS = qw( field1 field2 field3 );
        my @PLACEHOLDERS = ('?') x @FIELD;
        local $" = ',';
        $SQL = "INSERT (@FIELDS) INTO foo VALUES (@PLACEHOLDERS)";
    }

    sub run_insert {
        my $dbh = shift || return;
        my $sth = $dbh->prepare($SQL);
        $sth->execute(@_);
        $sth->finish;
    }
}

[download]

Makeshifts last the longest.

Replies are listed 'Best First'.
Re: Re: Fast Building of SQL Statements
by DapperDan (Pilgrim) on Aug 28, 2003 at 12:36 UTC
    ++. This is the best one, for my money.

    Very readable, and probably could be pulled out to a general sub which generates the run_insert sub for you. Nice use of a closure as well. I like this a lot as its a piece of code I have written badly over and over.

    (Rushing back to work after lunch, so not doing it myself (yet).)

[reply]
      Yep, that's easy. 
      sub make_insert_func {
    my @PLACEHOLDERS = ('?') x @_;

    local $" = ',';
    my $SQL = "INSERT (@_) INTO foo VALUES (@PLACEHOLDERS)";

    return sub {
        my $dbh = shift || return;
        my $sth = $dbh->prepare($SQL);
        $sth->execute(@_);
        $sth->finish;
    }
}

      [download]

      Makeshifts last the longest.

[reply]
[d/l]

In Section Meditations