On Thu, May 12, 2005 at 07:43:41PM +0530, Divya wrote:
> a) We are using Oracle 8i DB. The query (or the DBMS) takes only 6 seconds
> to return data to Perl.
>
> c) So we conclude that it is mainly an issue with fetchrow especially when
> the number of records are high. We tried other possible options like
>
> $stmt_handle->fetch()
> $stmt_handle->bind_columns()
> $stmt_handle->fetchall_arrayref()
> $stmt_handle->fetchrow_array()
> $stmt_handle->fetchall_hashref()
> @records = @{ $db_handle->selectall_arrayref($sql_stmt) };
Have you tried setting the RowCacheSize database handle attribute to a
large number. From the excellent DBI documentation:
Database Handle Attributes
This section describes attributes specific to database handles.
...
"RowCacheSize" (integer)
A hint to the driver indicating the size of the local row cache that
the application would like the driver to use for future "SELECT"
statements. If a row cache is not implemented, then setting "Row-
CacheSize" is ignored and getting the value returns "undef".
Some "RowCacheSize" values have special meaning, as follows:
0 - Automatically determine a reasonable cache size for each
C<SELECT>
1 - Disable the local row cache
>1 - Cache this many rows
<0 - Cache as many rows that will fit into this much memory for
each C<SELECT>.
Note that large cache sizes may require a very large amount of memory
(cached rows * maximum size of row). Also, a large cache will cause a
longer delay not only for the first fetch, but also whenever the
cache
needs refilling.
See also the "RowsInCache" statement handle attribute.
So your initial fetch may be slower, but future ones should be fast.
dd
--
David Dooling
