[odb-users] Using prefetch with Oracle

[Marco Craveiro]

Hi odb-users,

I have managed to do a successful test using ODB against an Oracle
database, and the ORM mapping part is working quite well. However, the
one snag I have is that fetching at the moment is a tad slow. It was
pointed out that I needed to enable prefetching in the Oracle drivers
so I've been investigating how to go about it. I can't see any direct
mentions in the manual; I saw references to bulk loading for
updates/deletes/inserts but not selects. However, I think I'm missing
something as I found this comment[1]:

> For fetches network traffic can be minimized using pre-fetching. ODB enables
> this feature if the underlying database supports it (e.g., Oracle).

This is exactly what I am looking for. But I grepped the code and
found no references to OCI_ATTR_PREFETCH_ROWS[2], so I tried manually
hacking "execute" as follows (statement.cxx in odb-oracle):

      const int prefetchSize(5000);
      sword r = OCIAttrSet (stmt_,
          OCI_HTYPE_STMT,
          (void*)&prefetchSize,
          sizeof(int),
          OCI_ATTR_PREFETCH_ROWS,
          err);

      if (r == OCI_ERROR || r == OCI_INVALID_HANDLE)
          translate_error (err, r);

Largely copied from SO[3]. This did not crash :-) but it didn't make
any performance difference either.

So in conclusion, any ideas on how to enable prefetching / bulk
loading for Oracle, using ODB? If this is not presently supported, how
should one extend the code to support it?

Cheers
-- 
Marco Craveiro

MD, Domain Driven Consulting
about: http://about.me/marcocraveiro
blog: http://mcraveiro.blogspot.co.uk
twitter: https://twitter.com/MarcoCraveiro

That the Ideas are themselves manifestations (of the Idea-Idea) and
that the Idea-Idea is a-kind-of Manifestation-Idea—which is a-kind-of
itself, so that the system is completely self-describing— would have
been appreciated by Plato as an extremely practical joke [Plato]. --
Alan Key

[1] https://www.reddit.com/r/cpp/comments/p4hiq/odb_c_orm_180_released_adds_support_for_sql/
[2] https://docs.oracle.com/cd/B13789_01/appdev.101/b10779/oci04sql.htm
[3] http://stackoverflow.com/questions/26301386/call-ocistmtexecute-with-ref-cursor-and-oci-attr-prefetch-rows