What makes PVD slow?

[meriator]

There are of course several different reasons that slow down PVD.

One is a lot of pictures that are way too big.

In my humble opinion, pictures with a maximum size of 500 x 500 px are completely sufficient. With an average compression ratio of 75%, the quality is still very good. So that a file size is between approx. 30 - 90 kb depending on the picture.
With these settings, PVD runs very quickly even with 10,000+ films with an average of 3-4 images per film and person.

In my opinion, images with sizes of 2000 x 2000 px and more that quickly reach file sizes of 2 - 5 MB have not been lost in the database. Not that you shouldn't have such large pictures, especially if you want to print out covers from time to time. Then an image can often not be big enough to achieve proper printing results. I can understand that and I also have scans of my DVDs, but not in the database. Especially when you consider that such large pictures do not fit 100% on the display without having to scroll.

This fact also creates a problem that cannot be easily solved with PVD. How do I find pictures that are too big?
PVD itself offers the possibility of executing SQL queries, but it is of no use because PVD does not display the result, so that something could be done with it.
e.g. the query

SELECT r. *, M. "Title" FROM IMAGES r
JOIN MOVIES m ON r. "Mid" = m. "Mid"
WHERE OCTET_LENGTH (r. "Imgdata")> 100000;

This query should display all entries in the IMAGES table and the associated title of the film from the MOVIES table.
Ha doesn't work like that.
Now this is where "FlameRobin" comes into play as the tool of the first choice. A freeware program that you don't even have to install.
If you have added the PVD database to this program as a resource, you can do a lot more.
BUT CAUTION you can also destroy the DB.
So it is always a good thing to work with a copy of the database first.

Back to the SQL query. The print OCTET_LENGTH is a built-in Firebird function and returns the size of the image in bytes.
If you first want to know how many images exceed the size of 100,000 bytes are in the database, you have to change the query accordingly.
SELECT COUNT (*) as sum_pics FROM IMAGES r
WHERE OCTET_LENGTH (r. "Imgdata")> 100000;

Ah, now you can see one next to the displayed result. The execution obviously takes longer. For this and other reasons I changed my table images. I've added a few, actually 4 fields to the database.
The corresponding SQL statement is also here

ALTER TABLE IMAGES ADD
"caption" Varchar (150) CHARACTER SET UNICODE_FSS COLLATE UNICODE_FSS;
Alter table IMAGES ADD "imgheight" integer;
Alter table IMAGES ADD "imgwidth" integer;
Alter table IMAGES ADD "imgsize" integer;

and immediately afterwards I executed this SQL statement.
Update IMAGES r set r. "Imgsize" = OCTET_LENGTH (r. "Imgdata");

And now I get with this statement

SELECT COUNT (*) as sum_pics FROM IMAGES r
WHERE r. "Imgsize"> 100000;

the result is displayed like a flash
however, I would have to execute the UPDATE statement every time after adding images. Oops, boring, I don't want it, it has to be automated.
Whoops, briefly postponed this instruction, which is called a TRIGGER, which should do this in the future.

SET TERM ^;
CREATE TRIGGER MOVIES_IMAGE_SIZE FOR IMAGES ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN NEW. "Imgsize" = OCTET_LENGTH (NEW. "Imgdata"); END ^
SET TERM;

so and now the value for imgsize is entered automatically with each new insertion or change of a picture.
I now change the first query to

SELECT r. *, M. "Title" FROM IMAGES r
JOIN MOVIES m ON r. "Mid" = m. "Mid"
WHERE r. "Imgsize"> 100000;

Unfortunately, now I still have to reduce all of them by hand and the height and width of the pictures do not register by themselves.
But we already have the option of inserting a short line of text for the caption.

how we can automate the whole thing more conveniently soon.
ps. we also can do that with

ALTER TABLE PEOPLEIMAGES ADD
"caption" Varchar(150) CHARACTER SET UNICODE_FSS COLLATE UNICODE_FSS;
Alter table PEOPLEIMAGES ADD "imgheight" integer;
Alter table PEOPLEIMAGES ADD "imgwidth" integer;
Alter table PEOPLEIMAGES ADD "imgsize" integer;

Alter table THUMBNAILS ADD "imgheight" integer;
Alter table THUMBNAILS ADD "imgwidth" integer;
Alter table THUMBNAILS ADD "imgsize" integer;


Alter table PEOPLETHUMBNAILS ADD "imgheight" integer;
Alter table PEOPLETHUMBNAILS ADD "imgwidth" integer;
Alter table PEOPLETHUMBNAILS ADD "imgsize" integer;


Update THUMBNAILS r set r."imgsize" = OCTET_LENGTH(r."imgdata");
Update PEOPLEIMAGES r  set r."imgsize" = OCTET_LENGTH(r."imgdata");
Update PEOPLETHUMBNAILS r  set r."imgsize" = OCTET_LENGTH(r."imgdata");


SET TERM ^ ;
CREATE TRIGGER THUMBNAILS_IMAGE_SIZE FOR THUMBNAILS ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN  NEW."imgsize" = OCTET_LENGTH(NEW."imgdata"); END ^
SET TERM ; ^

SET TERM ^ ;
CREATE TRIGGER PEOPLEIMAGE_SIZE FOR PEOPLEIMAGES ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN  NEW."imgsize" = OCTET_LENGTH(NEW."imgdata"); END ^
SET TERM ; ^

SET TERM ^ ;
CREATE TRIGGER PEOPLETHUMBNAILS_SIZE FOR PEOPLETHUMBNAILS ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN  NEW."imgsize" = OCTET_LENGTH(NEW."imgdata"); END ^
SET TERM ; ^
----------------------------------------------------------------------------------------
und in deutsch
----------------------------------------------------------------------------------------
Es gibt natürlich mehere verschiedene Gründe die PVD verlangsamen.

Einer ist, viele viel zu große bilder.

Meiner  bescheidenen Meinug nach genügen Bilder mit einer maximalen Grüße von 500 x 500 px völlig. Bei einer durchschnitlichen Kompresionsrat von 75% ist auch die Qualität immernoch sehr gut. So das man bei einer Dateigrüße je nach bild zwischen ca. 30 - 90 kb liegt.
Mit diesen Einstellungen läuft PVD auch bei 10000++ Filmen mit durchschnitlich 3-4 Bildern pro Film und Person noch sehr zügig.

Bilder mit Größen von 2000 x 2000 px und mehr die schnell auch Dateigrößen von 2 - 5 MB ereichen haben meiner Meinung nach nicht in der Datenbank verloren. Nicht dass man solch große Bilder nicht haben sollte, vorallem wenn man ab und an auch covers ausducken möchte. Dann kann oft ein Bild garnicht groß genug sein um ordentlich Druckergebnisse zu erreichen. Das kann ich verstehen und auch ich habe von meinen DVDs solche scanns, aber eben nicht in der Datenbank. Vorallem wenn man bedenkt dass solch großen Bilder noch nicht zu 100% auf das Display passen ohne srollen zu müssen.

Mit diesem Umstand entsteht auch ein Problem das mit PVD so nicht einfach lösbar ist. Wie finde ich zu große Bilder?
PVD selbst bietet zwar die möglicht SQL-Abfragen aus zu führen nur nützt das nichts da PVD das Resultat nicht anzeigt , so dass man damit etwas anfangen könnte.
z.B die Abfrage

SELECT r.*, m."title" FROM IMAGES r
JOIN MOVIES m ON r."mid" = m."mid"
WHERE OCTET_LENGTH(r."imgdata") > 100000;

Mit dieser Abfrage sollten alle Eintrage der Tabelle IMAGES sowie der dazugehörige Titel des Film aus der Tabelle  MOVIES angezeigt werden.
Ha geht so nicht.
Nun hier kommt als Das Hilfsmittel der erten Wahl "FlameRobin" ins Spiel. Ein freeware Program das man noch nicht einmal unbedingt installieren muß.
Hat man die PVD-Datenbank in diesem Programm als resource hinzugefügt kann man sehr viel mehr machen.
ABER VORSICHT, man kann auch die DB zerstören.
So ist es immer eine gute Sache zu nächst mit einer Kopie der Datenbank zu arbeiten.

Zurück zur SQL-Abfrage. Der aus Druck OCTET_LENGTH ist eine eingebaute Firebird function und gibt die Größe des Bildes in Bytes zurück.
Möchte man erst ein mal wissen wieviel bilder die Größe von 100000 Bytes überschreiten in der Datenbank sind, muß man die Abfrage dahingehend ändern.
SELECT COUNT(*) as sum_pics FROM IMAGES r
WHERE OCTET_LENGTH(r."imgdata") > 100000;

Aha jetzt sieht man schon mal eines neben dem angezeigten ergebnis. Die Ausführung dauert sichtlich länger. Aus diesem und weiteren Gründen habe ich meine Tabelle Images geändert. Ich habe ein paar, genau genommen 4 Felder der Datenbank hinzugefügt.
Hier auch gleich die entsprechende SQL-anweisung

ALTER TABLE IMAGES ADD
"caption" Varchar(150) CHARACTER SET UNICODE_FSS COLLATE UNICODE_FSS;
Alter table IMAGES ADD "imgheight" integer;
Alter table IMAGES ADD "imgwidth" integer;
Alter table IMAGES ADD "imgsize" integer;

und direct danach habe ich diese SQL-Anweisung ausgeführt.
Update IMAGES r set r."imgsize" = OCTET_LENGTH(r."imgdata");

Und jetzt bekomme ich mit diesem Statement

SELECT COUNT(*) as sum_pics FROM IMAGES r
WHERE r."imgsize" > 100000;

wie ein blitzartig das ergebnis angezeigt
allerdings müsste ich jetzt jedes mal nach dem ich Bilder hinzugefügt habe die UPDATE-Anweisung erneut ausführen. UUps langweilig, will ich nicht, das muß automatisiert werden.
schwupps, noch kurz diese anweisung nach geschoben die man einen TRIGGER nennt, der das zukünftig erledigen soll.

SET TERM ^ ;
CREATE TRIGGER MOVIES_IMAGE_SIZE FOR IMAGES ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN  NEW."imgsize" = OCTET_LENGTH(NEW."imgdata"); END ^
SET TERM ;

so und nun wird bei jedem  neuen einfügen oder ändern eines bildes der wert für imgsize automatisch eingetragen.
ändere ich nun noch die erste Abfrage um in

SELECT r.*, m."title" FROM IMAGES r
JOIN MOVIES m ON r."mid" = m."mid"
WHERE r."imgsize" > 100000;

jetzt muß ich aber leider noch immer alle von Hand verkleinern und auch die Höhe und Breite der bilder trägt sich nicht von alleine ein.
Aber wir haben schon mal die Möglichkeit ein kurzes textzeile die Bildunterschrift einzufügen.

wie wir das ganze bequemer automatisieren können demnächst.
ps. das Ganze können wir auch mit den Tabelle: THUMBNAILS , PEOPLEIMAGES, PEOPLETHUMBNAILS machen sie oben

[afrocuban01]

I think PVD is essentially slow because it's a 32-bit app and it cannot work with more than 2GB of memory, so at the very end it will crash when the task needs more than that: for example if you want to change the way images are stored - in or out of PVD, no matter how small images are.

Also, it'll get painfully slow if the amount of characters to a field is huge. Try to import 50k characters into any "memo" field for any movie (and there are cases on IMDb that has huge amount of chars, synopsis page for example), and you'll know what I mean.

So, at some point nothing can help with the slowness, not even converting to some other DB... 

[meriator]

hey afrocuban
you are right and you are wrong
PVD = 32 bit right
but
i use firebird as 64 bit version
and connecting PVD via Connect to Server (MainMenu-Option) (ConnectionString)
  ConnectionString = "localhost:C:\\YourFolder\YourDB.pvd"
and this makes it much faster
beside this many other Apps like a local webserver or FlameRobin can connect to the DB at the same time
Using the PVD embebed Firebird dlls blocks the DB-access for all other Apps

but u need Firebird 2.5 installed and runing
(hint: don't use any other/newer version it won't work with PVD)
give it a try

your welcome meriator 

[afrocuban]

hey afrocuban
you are right and you are wrong
PVD = 32 bit right
but
i use firebird as 64 bit version
and connecting PVD via Connect to Server (MainMenu-Option) (ConnectionString)
  ConnectionString = "localhost:C:\\YourFolder\YourDB.pvd"
and this makes it much faster
beside this many other Apps like a local webserver or FlameRobin can connect to the DB at the same time
Using the PVD embebed Firebird dlls blocks the DB-access for all other Apps

but u need Firebird 2.5 installed and runing
(hint: don't use any other/newer version it won't work with PVD)
give it a try

your welcome meriator 

Unfortunately this will not help with storing images out of or in PVD itself, since when connecting via fbserver, that option is not available - it is greyed out.

[meriator]

hey afrocuban
I think you miss understood me
"connecting to a fbserver" makes the cominication between PVD an the DB faster. AT this point it has nothing to do with the images. fbserver (64 bit) runs faster then the embebed fb (32 bit) dill's.

the image problem is solved (in my case) in an other way

first i gave them names by setting the "imgpath"
with
SQL

UPDATE IMAGES i
SET "imgpath" = (
    SELECT m."title"
    FROM MOVIES m
    WHERE m."mid" = i."mid"
) || '_' || i."mid" || '_' || i."imgid" || i."imgformat"
WHERE EXISTS (
    SELECT 1
    FROM MOVIES m
    WHERE m."mid" = i."mid"
      AND i."imgpath" IS DISTINCT FROM (m."title" || '_' || i."mid" || '_' || i."imgid" || i."imgformat")
)
ROWS 10;

(you can set the ROWS 10 to ROWS 1000 or more)

I made a (64 bit) dll-file named 'image_udf.dll'
which is an UDF module and has to be placed in the
Firebird-Server->UDF(folder)
the two function of the module GET_IMAGE_WIDTH & GET_IMAGE_HEIGHT has to be declared via a Statement so that the FB-server gets aware of them

The task of the module is to read image-height and image-width from the blob-field "imgdata"

this is realized via a Trigger withhin the FB-server
on Inserts or Updates
so i have changed the old Trigger which only affected imgsize via OCTET_LENGTH (r. "Imgdata")
to also get "imgwidth"= GET_IMAGE_WIDTH(r."imgdata") & "imgheight" = GET_IMAGE_HEIGHT(r."imgdata")

a simple SQL-Statement like

Update IMAGES i set i."caption" = i."caption"
where i."imgheight" is NULL;

will update all data rows

and now i can easy find all images which are to big
except Back-Covers which are 700px in height
and banners  which are 970px in width
all my images should not exceed 500 px in height or  width


SELECT  m."mid", m."title", m."year",m."size", m."series",m."epid",m."wish",m."visible",
       i."imgid", i."imgformat",i."imgheight", i."imgwidth", i."imgsize", i."imgpath"
FROM IMAGES i
left join MOVIES m on (m."mid" = i."mid")
where  (i."imgheight" > 500 or i."imgwidth" > 500) and NOT (i."imgheight" = 700 or i."imgwidth" = 970)
ROWS 1000;


but still the image must be resized manualy

curently this is done automaticly (for new images) by php, as all my script run via a local proxy to my local server an back
the local server resizes each image to size configered in it's config file.
the local server and it's local proxy helps me a lot in managing the PVD-DB.

by the way a little schematic view on whats going on my PC using PVD-scripts

[meriator]

And now I have successfuly compilered a 64bit dll as UDF module which does the resize action automaticly

the function it self requieres
IMAGE_RESIZE("imgdata", 500, "imgformat", 78)
where
1. "imgdata" = our Blob field
2. 500 = max. desired size here: 500px (on the bigger side height or width)
3. "imgformat" = ('.jpg' or '.png') this makes diffrence
            (to find out the images format makes via the function makes it much slower so, as we got it allready, we send it as parameter)
4. 78 = compresion ratio (.jpg  only)

this dll has a view exceptions which satisfy my needs
... so do nothing if ...
1. ... the height = 700 px (this is the height of back covers to be readable)
2. ... the width = 970 px & height = 231 px (this is the width of banners)
   with on exepetion here (970:231 = an aspect ratio to approximately 4.2:1, so if width > 970 or <= 930 with equal aspect, then change it to width = 970 px)
3. ... the width = 500 px & height < 500 px
4. ... the height = 500 px & width < 500 px

currently i not made a Trigger for this resizing, in cause of testing with GET_IMAGE_WIDTH & GET_IMAGE_HEIGHT functions
may i have to put all together in 1 single UDF module

I will see

if every thing works as expected I will publish the moduls here

sty tuned meriator

[afrocuban]

Wow. This is way out of my league, and I admit I jsut have to wait for the final dll and how it applies on the outcome, preferably through some example.

Thanks for your commitment, though!