On Fri, Mar 15, 2002 at 04:31:11PM -0800, Jonathan Leffler wrote:
> Dear Tim,
>
> I spotted the discussion about using these attributes to improve the
> performance of DBD::Oracle, and woke up since I don't remember them
> being discussed before.
>
> Running 'perldoc DBI' for DBI 1.21 gives:
>
> [...]
> "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 "RowCacheSize" 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.
>
> [...]
> "RowsInCache" (integer, read-only)
> If the driver supports a local row cache for "SELECT"
> statements, then this attribute holds the number of un-
> fetched rows in the cache. If the driver doesn't, then
> it returns "undef". Note that some drivers pre-fetch
> rows on execute, whereas others wait till the first
> fetch.
>
> See also the "RowCacheSize" database handle attribute.
>
> I assume that we aren't expected to deal in negative memory, and
> the comments for RowsInCache <0 should make some comment about
> 'absolute value'.
Guess so :)
> Is the memory size specified in bytes or some
> multiple of bytes?
Bytes.
> I've got some questions for you - how could I implement any support for
> that in DBD::Informix given the constraints imposed by Informix? I'd
> like to do it for the same reasons other drivers do it, but I can't
> easily match the semantics of the RowCacheSize and RowsInCache
> attributes.
Typical! [grin]
> What do I get to play with? There's a fetch buffer size variable that
> is used when the (ESQL/C, equivalent to ODBC) FETCH statement is
> executed; it defaults to a value such as 4 KB. Within broad limits, as
> many rows as can fit will be fetched into that buffer.
That fits the RowCacheSize < 0 case very well.
For RowCacheSize > 0 you just need to fudge something that'll work
reasonably well:
> One of the
> exceptions is when the rows are locked because the cursor is qualified
> FOR UPDATE; then you get one row at a time. Even if I set the value
> (which I'd cheerfully do), I won't really be able to say how many rows
> are in the cache. I can guess, but the precise information is not
> revealed to the client code.
Neither is it for Oracle, so I just guess! ["Lies, Damn Lies, and
Performance Tuning."] Basically I walk along the selected column
types and make a guess at a typical average width for each.
> Judging from the specification, if the first row has not been fetched,
> then the RowsInCache attribute should return undef; otherwise, its best
> estimate of the number of rows available in the cache.
Yes, but feel free to always return undef regardless. The RowsInCache
attribute isn't of much (reliable) practical purpose.
> And things like
> fetchall_arrayref() will be able to use the database handle value of
> RowCacheSize.
It's a database handle attribute because it's expected to only take
effect at prepare() time (when setting up buffers etc), or maybe
execute(). Changes in the database handle RowCacheSize certainly
shouldn't affect pre-prepared statement handles.
> There are some interesting twists and turns. If the user modifies
> $dbh->{RowCacheSize} after the first fetch op, the array size can't
> change until the next FETCH operation. Or should the size when the
> first fetch is performed remain constant?
As above. Setup at prepare/execute time and remain constant after that.
(The the whole philosophy of the DBI, get as much setup as early as
possible so fetching data has at little to thing about as possible.)
> That is presumably what is
> intended; juggling the memory while the statement is active is possible
> but unduly hairy.
Yeap.
> Should the statement record the RowCacheSize at the
> time when the statement is prepared and ignore subsequent changes?
Yes.
> That
> is mostly consistent. Is the RowCacheSize inherited by the statement?
> I presume so.
In effect, but not visibly, it's just a dbh attribute, not sth.
> What happens if the statement's RowCacheSize is changed
> between the prepare and the execute.
It can't be as it's just a dbh attribute, not sth.
> Are these attributes stored
> automatically by DBI? If so, what mechanism should be documented in the
> DBI::DBD manual so that driver writers can access the fields. Or do the
> drivers have to add the support themselves?
>
> ....hmmm...maybe the real question is "When am I going to find time to
> actually rewrite the code to use sqlda?"...
:-)
Tim.
