Images in Blobs, querey very slow from Lazarus/TZQuery over the LAN
lazarus / 16-Databases

----------------------------------------------------------------------
krzynio · 2014-11-05 18:24

Hello!
I have simple table as below:
Code: [Select]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]SELECT * from IDXUntil 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?

----------------------------------------------------------------------
Cyrax · 2014-11-05 20:35

Do you compress the image when you store it in the database? Or it is
just pure raw data?

----------------------------------------------------------------------
EgonHugeist · 2014-11-05 20:39

I don't think it works with 7.1

on Zeos-7.2 there is a option:
TZDataSet.Options [doCachedLobs]
This option isn't set by default so 7.2 only load the loabs on
demand(if it's possible todo so of course, because some Providers
can't go this route..)
If you add the option 7.2 falls back to old behavior..

Michael

----------------------------------------------------------------------
krzynio · 2014-11-06 07:42

Thank you guys for suggestions!
I consider compression or storing jpgs now and CachedBlobs if 7.2 will
be stable.
Today I decided to move images to separate table, store full image and
thumbnail and load full image on demand only.

BTW anybody knows how Flamerobin exactly works with blobs that it
works so fast?

----------------------------------------------------------------------
EgonHugeist · 2014-11-06 11:22

FlameRobin is bypassing the TDataSet-bottleneck!

Just view what Zeos-7.2-Beta ZDBC-Layer can do for you: See attached
html. This was a user constribution and compares FireDac, UniDac,
Synopse-native access and ZDBC for you! In addition to know what the
numbers represent, read:
http://forum.lazarus.freepascal.org/index.php/topic,26214.0.html