RE: perl DBI/DBD: can I pass in an array as parameter?

> As long as you are using bind variables,
> the overhead of multiple executes should
> not be very high.

It's all a matter of degree. On several trace files I've analyzed lately, the whole response time problem was caused by thousands of 'SQL*Net message from client' calls. They had nice little latencies (less than 0.010 seconds), but there are thousands of them. ...And one thousand 0.010-second latencies adds up to 10 seconds.

The only way to get rid of this kind of response time problem is to get rid of the thousands of 'SQL*Net message from client' calls. And because these waits exist between adjacent database calls, the way to get rid of them is to get rid of lots of parse, execute, and fetch calls. It's a good goal.

<idea warning="I haven't confirmed the quality of this with anyone who's decent at writing optimized SQL">One technique that hasn't been mentioned yet: I think one way to reduce the number of executes without introducing dynamic SQL (which can't be shared effectively among users) is to use an inline view in the IN clause:

        SELECT tab1.col1, tab2.col2
        FROM tab1, tab2
        WHERE tab1.ID1 = tab2.ID2
          AND tab1.X IN (
                        SELECT codes FROM table123
                )

</warning>

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney
- Hotsos Symposium 2004, March 7-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
Sarnowski, Chris
Sent: Tuesday, July 01, 2003 6:05 PM
To: Multiple recipients of list ORACLE-L

Responses to 2 emails below:

Alex wrote:
>
> not sure if this is what you want. one sql call
>
> select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X = ?
> and tab1.X = ?
> and tab1.X = ?
> ;
>
> @my_array = (1,2,3);
> sth->execute(@my_array);
>

Errm, no rows will be returned. Think about this one a little more.

Steve Ollig wrote:
> ok - that makes more sense. sorry for misinterpreting the
> question. i've
> never done it, but my first instinct would be to explore
> using an in clause
> in the query -
>
> select tab1.col1, tab2.col2 from tab1, tab2
> where tab1.ID1 = tab2.ID2
> and tab1.X in (1, 2, 3)
>
> can you simply pass an array to the prepared statement that
> way? i'd try it
> but don't have a sandbox with the DBI/DBD modules handy.
>
> perhaps one of the great Perl gurus of the list will offer
> some insight...
>

I'm not a Perl guru, but I can think of 2 solutions:

#build an array with the keys you want to look for: my @my_array = (1, 2, 3);
# then add that many ?s to the query

#the 'in' solution:
my $query = '
  select tab1.col1, tab2.col2 from tab1, tab2

        where tab1.ID1 = tab2.ID2
        and   tab1.X in (' . join(',',('?') x @my_array) . ')';

# or the 'union' solution
my $subquery = '
  select tab1.col1, tab2.col2 from tab1, tab2

        where tab1.ID1 = tab2.ID2
        and   tab1.X = ?';

my $query = join(' union ', ($subquery) x @my_array);

# pick only one of the above!
# and then

my $sth = $db->prepare($query);
$sth->execute(@my_array);

# then get the data back your favorite way: fetchall_arrayref, fetch_array, etc

But is it really worth the trouble? As long as you are using bind variables, the overhead of multiple executes should not be very high.

warning: these are typed from memory - I may have typos in the perl code. But the concept should work.

-Chris

LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).