Page 1 of 1

Lazarus, Firebird and Blobs over the network, slow

Posted: 05.11.2014, 20:09
by krzynio8
Hello!
I have simple table as below:

Code: Select all

CREATE TABLE IDX
(
  ID CARDINAL NOT NULL,
  ID_PARENT INTEGER NOT NULL,
  CODE VARCHAR(10) NOT NULL,
  DES1 VARCHAR(100) NOT NULL,
  DES2 VARCHAR(100),
  IMAGE BLOB SUB_TYPE 0,
  CONSTRAINT PK_IDX_0 PRIMARY KEY (ID)
)
The field IMAGE stores bmp image 800x800x16.
I have only 200 rows in this table.
I select the records using simple query:

Code: Select all

SELECT * from IDX
Until I worked on localhost everythig was working fine and I didn't notice any problem with speed.
When I moved database to another machine in LAN the above query instead of 1s is executing over 10s.
It works so slow only from Lazarus/Zeos 7.1.3 (the same slow speed on windows and linux).
The same query executed from Falmerobin finishes in 1s from localhost and 1s from remote server as well.
When I remove the IMAGE field from query Lazarus works very fast again.

Does it mean that Zeos TZQuery reads all blob data from all rows and Flamerobin reads only pointers or info about existing or not existing data?
How to force TZQuery for loading full blob data for current row only?

Re: Lazarus, Firebird and Blobs over the network, slow

Posted: 11.11.2014, 12:53
by EgonHugeist
Switch to 7.2 from SVN.

Current \testing-7.2 branch supports loading/flushing on demand for FireBird and PostgreSQL OID-Lobs. This saves memory and increases the reading-performance!
Note:

I'm not ready with the lob's, it might be possible i can get this running for all plains which do support a scrollable resultset.

Re: Lazarus, Firebird and Blobs over the network, slow

Posted: 12.11.2014, 08:21
by Sergiomaster
Hello,

is not , as i understood, the usage of TZQuery properties cashedblob fot that ?
from doc\html\parameters.html
6.2. Statement parameters
cursor=<cursor name> - The given cusor name is sent to sql server.
cashedblob=[yes,no] - This means that blob data fetch immediate if [yes] or used interbase blob if [no].
if not then what does it means ? (for my tutorial)

Re: Lazarus, Firebird and Blobs over the network, slow

Posted: 12.11.2014, 15:22
by EgonHugeist
Serge,

the "cashedlob=True" does no longer exist on 7.2 since it was a wrong type e.g "cachedlob=x" should match the case.

there is a TZDataSet.option available on 7.2: doCachedLob but you also can add the Param "cachedlob=True"
indicates same behavior but is not default on 7.2.
Note:
It also works for PostgreSQL OID-lobs and if i'm ready with some more refactorings than this option should work for ALL scrollable ResultSet-providers(NO SQLite) or such as FB where we've a unique ID to load the lobs on demand.