Q's about $dbh->{RowCacheSize} and $sth->{RowsInCache}

[Jonathan Leffler]

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
   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.

 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'.  Is the memory size specified in bytes or some
multiple of 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.

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.  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.

There is also some array fetching technology which can be perhaps be
used, but it requires me to do a rewrite of the code to use 'the other'
dynamic SQL mechanism for describing data.  (DBD::Informix currently
uses the standardized SQL DESCRIPTORS; the other mechanism is known as
'sqlda').  However, the array fetching mechanism does allow the user to
specify the number of rows to be fetched at a time, and the rewrite has
been (still is) on my list of desirable things to do.  There will still
be an upper limit on the number of rows that can be prefetched (based on
the maximum of 32 KB fetch buffer size and the row size).  But the
information about how many rows there are in the cache will be
available explicitly.

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.  And things like
fetchall_arrayref() will be able to use the database handle value of
RowCacheSize.

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?  That is presumably what is
intended; juggling the memory while the statement is active is possible
but unduly hairy.  Should the statement record the RowCacheSize at the
time when the statement is prepared and ignore subsequent changes?  That
is mostly consistent.  Is the RowCacheSize inherited by the statement?
I presume so.  What happens if the statement's RowCacheSize is changed
between the prepare and the execute.  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?"...

-- 
Jonathan Leffler   #include 
STSM, Informix Database Engineering, IBM Data Management Solutions
Phone: +1 650-926-6921  Tie-line: 630-6921
Emai

Re: catalog method arguments, empty strings ("")

[Tim Bunce]

Thanks.

I think the DBI should follow ODBC where it differs from SQL/CLI
in areas where ODBC is not likely to change. This is probably
one of those places.

Tim.



On Fri, Mar 15, 2002 at 12:46:29PM +0100, Steffen Goeldner wrote:
> Steffen Goeldner wrote:
> > 
> 
> > P.S.: It seems I'll have TO DO some 'empty string' research.
> 
> This is yet another case where ODBC and SQL/CLI differ :-(
> 
> ODBC is quite clear about pattern value arguments:
> 
>  
> 
>  | Passing a null pointer to a search pattern argument does not
>  | constrain the search for that argument; that is, a null pointer
>  | and the search pattern % (any characters) are equivalent.
>  | However, a zero-length search pattern - that is, a valid pointer
>  | to a string of length zero - matches only the empty string ("").
> 
> ODBC says not much about empty strings in ordinary arguments in
> general. However, the doc's for special catalog functions say
> (e.g. for SQLColumns):
> 
>  | If a driver supports catalogs for some tables but not for others,
>  | such as when the driver retrieves data from different DBMSs, an
>  | empty string ("") denotes those tables that do not have catalogs.
> 
> ODBC is quite silent about empty strings in identifier arguments.
> 
> In my opinion, ODBC treats empty strings always as restriction
> criteria, i.e. they match the empty string.
> 
> SQL/CLI gives very precise rules how arguments must be handled.
> Attached is a perlified version of these rules for SQLColumns().
> As you can see, arguments of length zero are ignored when building
> the predicate. Thus, empty strings will not restrict the result
> set.
> Matching an empty string is only possible if METADATA_ID is TRUE
> and the argument is an empty quoted identifier ('""').
> 
> 
> Steffen

catalog method arguments, empty strings ("")

[Steffen Goeldner]

Steffen Goeldner wrote:
> 

> P.S.: It seems I'll have TO DO some 'empty string' research.

This is yet another case where ODBC and SQL/CLI differ :-(

ODBC is quite clear about pattern value arguments:

 

 | Passing a null pointer to a search pattern argument does not
 | constrain the search for that argument; that is, a null pointer
 | and the search pattern % (any characters) are equivalent.
 | However, a zero-length search pattern - that is, a valid pointer
 | to a string of length zero - matches only the empty string ("").

ODBC says not much about empty strings in ordinary arguments in
general. However, the doc's for special catalog functions say
(e.g. for SQLColumns):

 | If a driver supports catalogs for some tables but not for others,
 | such as when the driver retrieves data from different DBMSs, an
 | empty string ("") denotes those tables that do not have catalogs.

ODBC is quite silent about empty strings in identifier arguments.

In my opinion, ODBC treats empty strings always as restriction
criteria, i.e. they match the empty string.

SQL/CLI gives very precise rules how arguments must be handled.
Attached is a perlified version of these rules for SQLColumns().
As you can see, arguments of length zero are ignored when building
the predicate. Thus, empty strings will not restrict the result
set.
Matching an empty string is only possible if METADATA_ID is TRUE
and the argument is an empty quoted identifier ('""').


Steffen


SQLColumns.pl
Description: Perl program