Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE

Just a TIMTOWTDI thought. I do not have time to benchmark now, but will get to it later if nobody does it before I get the chance. The "right" way example presented by jZed prepares the statement once then executes multiple times for each value. In such situations, I tend to do this another way. I form a statement that will do all changes in one blow, then run that through. My method of doing it the "right" way is as such:

my $sta = 'UPDATE foo SET bar=? WHERE ';
$sta .= (join ' OR ', ('baz=?') x @values);

my $sth = $dbh->prepare($sta);
$sth->execute(7, @values);
[download]

Comment on Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE Download Code

Replies are listed 'Best First'.
Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE by runrig (Abbot) on Nov 15, 2003 at 13:51 UTC
Making fewer database calls when possible is good. You can also do (if there are not too many @values): `my $sta = 'UPDATE foo SET bar = ? WHERE baz in (", join(',', "?" x @va +lues), ")";` [download] But I think jZed was just offering a generic example, and on these sorts of updates, I think you often want to set 'bar' equal to something different for each 'baz', so the multiple execution version is more appropriate in that case.	[reply] [d/l]
Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE by etcshadow (Priest) on Nov 15, 2003 at 17:24 UTC
This can be a good trick... but it is not generally applicable... since it won't work, in general, for multiple inserts. I say "in general" because there are cases where you can do things like: `insert into table1 (val1,val2,val3) select val1,val2,val3 from table2 where id in (?,?,?,?,...)` [download] but... again, that will only be an available option some of the time. ------------ :Wq Not an editor command: Wq	[reply] [d/l]
Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE by mpeppler (Vicar) on Nov 16, 2003 at 16:37 UTC
That might work reasonably well for a fairly limited number of arguments. If your @values array is large (500+, say), then hitting the database 500 times may be faster than having a query with 500 ORs in the WHERE clause, because the optmizer is going to have a hell of a time trying to figure out the best access path, and because there may be a limit to the number of placeholders that a single statement can have (AFAIK for Sybase this limit is 256, btw). Michael	[reply]