Speed up accessing Memo & WideMemo data?

[aehimself]

I have a query which is opening a table with some CLOB fields in it. I overridden the WideMemo field's gettext event so the grid will actually show the data in them, not only (BLOB).

Now here is the problem. When the gettext event is NOT assigned, displaying the grid takes ~100 ms. With gettext, it takes ~25 seconds.
I already have doCachedLobs disabled in the TZQuery object to make sure all data is available when the grid starts updating.

I guess this will have something to do with how the data is accessed in the field... as Memo and WideMemo fields create and destroy a stream object each time when Field.AsString (or Field.AsBytes) is called. And since the grid shows 30-40 of these fields...

I'll try to play around with some messy typecastings to see if they'll work but is there a way I can achieve some speedup... officially? :)

[EgonHugeist]

I already have doCachedLobs disabled in the TZQuery object to make sure all data is available when the grid starts updating.

Isn't it exactly vice versa? CachedLobs are local memory lobs on client side. UnCached lobs are fetched from Server to Client on demand(If the server supports a nice api for that, such as IB/FB, Oracle, Posgres using OID-Lobs).

[MJFShark]

Are we talking Oracle? As far as I can tell the Zeos Oracle driver doesn't support the CachedLobs option. I see no difference in performance with the setting and it appears that the clob is retrieved anytime the field is accessed (and I see the same network traffic regardless of re-reads.) I also get no hits back from the source code with a search for "CachedLob" in any of the Oracle dbc files (which I do for the other drivers.)

-Mark

[aehimself]

Isn't it exactly vice versa? CachedLobs are local memory lobs on client side. UnCached lobs are fetched from Server to Client on demand(If the server supports a nice api for that, such as IB/FB, Oracle, Posgres using OID-Lobs).

Well, I had it on Enabled until now, just switched it to disabled with little to no effect. And since yes, the RDBMS in question is Oracle I believe that Mark is right:

As far as I can tell the Zeos Oracle driver doesn't support the CachedLobs option.

I tried as a blob field, reading out as stream / bytes / string / raw, all had the insane performance penalty.
Is there a way to tell Zeos to load WideMemo fields as WideString? Downloading the dataset will take longer, but data could be read out immediately.

[EgonHugeist]

You guys are right, hope the quick fix of https://sourceforge.net/p/zeoslib/code-0/7033/ will resolve the issue?

[aehimself]

Holy f*ck!

It's not visible on Git yet so I made your changes in my local repository.
Loading time of 25 seconds has been reduced to 47 ms...!
Wtf :D

Btw, doCachedLobs has no effect, as TZAbstractStatement.Create only checks for a parameter in the SQL connection:

FCachedLob := StrToBoolEx(DefineStatementParameter(Self, DSProps_CachedLobs, 'false'));

ZDbcOracleStatement checks this FCachedLob field, not paying attention to the owning dataset's options.
I'll see if I can access it somehow from there to issue a fix...

Edit: it seems that unfortunately it's not going to work this easy. The statement doesn't have a connection to a dataset, it works with the connection object directly. We'll have to introduce a new parameter in the constructor, so the dataset can pass this information when creating said statement.

But holy f*ck this speed increase... THANK YOU!

[MJFShark]

That's interesting! I've also noticed that doCachedLobs has no affect. In my testing using the session based setting I see that my query takes much longer to retrieve the result set initially (as expected since now it retrieves the CLOBs at that time) and then accessing the clobs later is super fast and cached. I'm doing a fetch all since I also show the results in a virtual grid. So that definitely seems to be working correctly although I'd love to see separate "CacheLobs" and "DeferLobs" options (so that individual lobs are cached "on demand" if both settings are on.) Unless there's some way to do that now?

-Mark