Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
14
Erster Beitrag:
vor 3 Jahren, 5 Monaten
Letzter Beitrag:
vor 2 Jahren, 4 Monaten
Beteiligte Autoren:
iso, Fabrice Harari, Luiz Menezes, Arie, Stewart Crisler, CCC2, Joel, Tor-Bjarne

images stored in hypersql database

Startbeitrag von iso am 16.01.2015 15:50

Hello,

i ve created programs in the past where i have saved images directly into the datafile. But they seem to slow the database down a ridiculous amount, even when ive only had about 5 thumbnail pictures am i doing something wrong? because of this ive been saving the paths and storing the images elsewhere, however i ve started a new project (WB18) that requires quite a few images linked to records, i know i can still create paths and do it as i have been, but i would prefer on this particular project to store them in the database. has anyone had success storing lots of images in a database without a massive dent in its performance?

thanks iso

Antworten:

Hi iso

Are you sure the database is causing troubles and not your page, loading several FULL blown images?

I have an image with 2 thumbnails without any problem, stored in hfcs. Database holds 100.000+ images.
My app shows these thumbnails in a looper. After the user picks one, the full image is shown.

von Arie - am 16.01.2015 16:03
Hi

I have a 8GB+ DB where about 7GB are blobs (images and videos)
I have ZERO problem, it's fast and nothing changes when I add more blobs

You may have a conception problem...

Now, OF COURSE, if you are using queries returning a ton of records and you INCLUDE the blob in your query, then ALL the images will be read BEFORE you display the first one...

So that will NOT be a good way of coding your app

von Fabrice Harari - am 16.01.2015 16:07
Hi,

Thanks Fabrice maybe thats part of the problem. Are you saying the images (and videos which i didnt even know i could store!) should be in a separate table?

Arie, even when i try to view the datafile with WDMap (with only about 5 images in) it takes ages or crashes!

Are you resizing/compressing the image before saving?

Thanks

ISO

von iso - am 19.01.2015 11:08
Iso,

I just defined 2 or 3 thumbnails in the analysis and that's it.
Whenever I add an image, the hfsql engine creates the thumbnails as well.
Nothing special and no coding what so ever.
The item is denifed as binary memo, maybe that differs from your situation?

As a test I opened a 45GB table with WDMAP. It opens instantly, showing the first 50 records or so. It doesn't load the whole table as once, but read additional data when scrolling. At least for the images. But that's just how wdmap works I guess.

von Arie - am 19.01.2015 13:00
I am not saying that you have to have your images and video in a separate file, although you CAN do that

I am saying that you shouldn't do a "select *..." on a file where there are binary memos, as in this case, all of them will be read when they are probably not needed

Best regards

von Fabrice Harari - am 19.01.2015 13:18
Fabrice,

Do you mean that "blobs should be in a separate table" as Iso stated? Can you explain more the "Select" and how can we make the wrong statement?.

von Luiz Menezes - am 19.01.2015 17:10
Hi Luis

no, I means that they CAN be in a separate table, in order to avoid any kind of error when using a query...

If you are doing a query on a file where the record is 200 bytes long and your query returns 1000 records, then you get a 200 KB answer (fast and easy even on the web)

if your query included the blob, then you get 200KN PLUS the total size of all the blobs for the 1000 records, which can means several GBs... And that will be slow, no matter what...

Instead, you always should do your queries WITHOUT the blobs, and when you need to display/use one, you just do a hreadseek and hextractmemo...

If your blobs are in a separate table, you don't even have to read the main record again... And you can never by MISTAKE include the blobs in a query

Best regards

von Fabrice Harari - am 19.01.2015 20:47
I ve been doing some testing putting them in a separate table and it seems to be all ok, so thanks for your help on this. Also Fabrice is there a way of compressing the image for example if users try to add images of 4gb, or is it just a case of resizing the image?

Thanks Arie never noticed the HyperFileSQL thumbnails before,

von iso - am 20.01.2015 11:00
Hi,

WinDev has zip compression capabillities, check out ZipCreate etc. in doc.

Cheers
Tor-Bjarne

von Tor-Bjarne - am 20.01.2015 11:45
Thanks Fabrice. Now I understand and got it.

von Luiz Menezes - am 20.01.2015 17:28
Hi -

I'm getting ready to start trying to build an image store / retrieve system. I want to use it across multiple projects by sharing the dialog pages over many projects, so it'll be easier to save everything in a dbase I think. Is there a sample out there I can use to get started? Also - I get confused about the functions for storing and retrieving the images from the database and using them. Which functions serve this purpose?

von Joel - am 13.02.2016 15:19
the following suggestion based on my experiences will speed up.
1. create stand alone table for blob .
for example :
create table tbl_img {
id int NOT NULL autoincrement,
img blob null,
primary key(Id)
}


2. do not create relationship key from other tables to image table. this will slow down other tables when doing insert/modify/delete

3. do not use blob if size of image/files more than 40K.

4. rather than 1 image table , create multiple image tables will speed up access.

5. if you need to store big size image ( 1mb and above) , Preferred way is to have a directory structure to save files and keep the path to the file in the database (this method is done automatic in mssql).


the last is be careful when write query join to image table.

Query1 vs Query2 which is faster. anyone can guess ?


////////////////////////////////////////////////////////////////////////////////////////
Query1

select
emp.emp_id,
emp.emp_name,
img.blob
from
emp
inner join img
on img.img_id = emp.img_id
where
emp.dept_id = 'SALES'


/////////////////////////////////////////////////////////////////////////////////////////////
Query 2

select
P.emp_id,
P.emp_name,
img.blob
from
(
select
emp.emp_id,
emp.emp_name,
emp.img_id
from
emp
where
emp.dept_id = 'SALES'
) P
inner join img
on img.img_id = P.img_id

von CCC2 - am 13.02.2016 16:45
In WDMap there is a way to turn off displaying thumbnails of images (I never remember exactly how to do it but try some right clicking). With thumbnails displaying against regular images, performance is abysmal. Once you get the images turned off, things work as expected.

Stewart Crisler

von Stewart Crisler - am 17.02.2016 04:57
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.