Re: How to bind to a LARGE array
- From: dnm@xxxxxxxxxxxxxx (David N Murray)
- Date: Mon, 1 May 2006 20:01:20 -0400 (EDT)
On May 1, Bealach Na Bo scribed:
Hi folks,
Apologies if this has been asked and resolved - I've spent half a day
searching various archive, but have not found anything.
My problem is the following:
I have a very large postgresql table (ca. 3 mil records) and need to
replace a nested sql query with an "itarative" one so I end up with:
@names is a very large array - of the order of 100000 elements.
sub prep_sql {
my $stmt = <<EOSQL;
SELECT j.job_log_id
FROM job_log j, node n
WHERE n.name in ? <============ (@names) should be here
DBI documentations says
this must be a scalar
AND n.type = ?
AND n.usage = ?
AND n.node_id = j.node_id
EOSQL
my @bind = (@names,'B','LIVE'); <=========can't use @names here
return ($stmt,@bind);
} # end sub prep_sql
I know how to use sql subqueries to do this in one go in postgresql,
but I think this might be a lot faster?
Has anyone tried the same sort of thing?
Regards,
Bealach
Why not create a temp table, insert @names into it, and just do a join?
HTH,
Dave
.
- References:
- How to bind to a LARGE array
- From: Bealach Na Bo
- How to bind to a LARGE array
- Prev by Date: Possible Oracle 9.2.0.7 performance issue with DBD::Oracle's column_info()
- Next by Date: Re: Installing DBD::Oracle on HP/UX
- Previous by thread: How to bind to a LARGE array
- Next by thread: Re: How to bind to a LARGE array
- Index(es):
Relevant Pages
|
