Oracle clob performance and CopyLocator

[MJFShark]

Hi all,

I've been doing some testing on Clobs and found that with cache modes of None or OnAccess that retrieving clobs is three to four times slower than when using OnLoad caching. I managed to gain a little performance by consolidating some of the OCILobGetLength2 calls, but it took a while to figure out what else was happening. I managed to track it down to the CopyLocator function which uses OCILobLocatorAssign. Removing the call appears to fix the performance issue completely, but, of course, I'm hesitant to just remove something that I don't understand the purpose of. In my limited testing I haven't seen any differences with the change (besides it being three times faster lol.) I do notice that another Oracle library I have access to doesn't use OCILobLocatorAssign at all. The documentation seems to indicate that lob locators (or more appropriately pointers to lob locators) can be directly copied (which seems to bear out in my testing.) Anyone have any knowledge on this? Thanks!

-Mark

[aehimself]

I have absolutely no knowledge on this topic, so excuse me if I say something stupid. But is it possible that it's for backwards compatibility? Maybe older oci.dlls and/or Oracle servers did not support this, only via the slow way?

[MJFShark]

Fascinating! The copy is absolutely needed (at least for now.)

Zeos uses the same allocated loblocators for each "rowbatch" fetch. In OnLoad cache mode the loblocators are used directly to fetch the individual row clobs (before they're overwritten by the next "rowbatch" fetch) so they avoid needing the copy.

In "None" and "OnAccess" mode, it allocates a new loblocator for each clob and uses CopyLocator to move the fetched lob into the cached area. Unfortunately, that's a slow operation and so lobs in those cache modes are three times slower than the "OnLoad" ones.
(I'm sure I'm explaining something poorly that someone already knows lol!)

One possible change I'm researching is to always fetch into newly allocated loblocators. To the lab!

-Mark

[MJFShark]

Update: Lab results... looking good!