[odb-users] Using prefetch with Oracle
boris at codesynthesis.com
Mon Feb 13 08:56:52 EST 2017
Marco Craveiro <marco.craveiro at gmail.com> writes:
> I'm going to have a look and see if I can follow the same pattern for
> the bulk parameter so that it uses the pragma value when setting the
> prefetch (and does nothing if bulk is not set). If I do this, would
> you accept it as a patch?
Hm, I am not sure the bulk approach (with a compiler-time pragma) is
right in this case. There we don't really have a choice since we need
to know the "batch buffer" size.
But here it is all runtime. Plus, you may want to have different
prefetch for different queries of the same object. In fact, you
can already customize it for queries (but not for object loads)
by using prepared queries (Section 4.5 in the manual):
1. Create prepared query.
2. Get its statement (statement()).
3. Cast it to odb::oracle::select_statement.
4. Call handle() on the result to get OCIStmt*.
5. Set custom OCI_ATTR_PREFETCH_ROWS.
6. Execute the query.
The problems with this approach are: (1) it is tedious and (2) it
doesn't work for non-query SELECT's (e.g., database::load()). So
perhaps the way to do it is:
1. Provide prefetch() functions on oracle::database() and
oracle::connection() that can be used to modify database-wide
and connection-wide prefetch values. Also set it to some
reasonable default (say 512?)
2. Provide oracle::select_statement::prefetch() to make the
prepared query approach less tedious.
What do you think?
More information about the odb-users