On Thu, 9 Dec 1999, Alex Menendez wrote:
> hello, all
>
> Given the following situation what is more efficient:
>
> -I have 10 id numbers I want to query on numbers 1,2,3,4,5,6,7,8,9,10
> respectively. However, this list is not fixed and could be any number of
> ids up to 20.
>
> -Is it faster to create a global variable with a prepared sql statement
> at the begining of the mod_perl script as follows:
> $sth = $dbh->prepare('SELECT name FROM table_name WHERE id = ?');
> then iterate through n number of ids later in the script for the
> $sth->execute(1);
>
> -Or is it faster to prepare the statement each time the script is
> called with the following sql statement and execute once:
> $sth = $dbh->prepare('SELECT name FROM table_name WHERE id IN
> (1,2,3,4,5,6....)';
> $sth->execute;
>
I would definately say this last one. but you're limited to 256
items. and to save you some time, also consider nesting INSERT (or
REPLACE) with something like this:
INSERT INTO table_name (col1,col2) VALUES (val1,val2), (val1,val2), ...
> Basically, A persistent sql handle with many executes VS a
> non-persistent sql handle with one execute.
> The dbh is cached and global for both...
>
connect is _very_ expensive operation, so persistent is a Good Thing [tm],
whenever possible use them. get lots of RAM and also play with caching
data, but that's a totally different psalm.
> thanx,
> -amen
>
god bless you my son :>
