We have an item database with about 50.000 records, limited to 200 records (we need 200, this cannot be lowered for better performance) a lot of records contain an image with the varbinary database storage method. I am unfortunately not able to test if the images are the cause of the performance lag, but i am quite sure, because within seconds there are 100's of mb's used when viewing the item table (other larger tables with just text cause no problems). The problem only occurs on the windows client not on universal, and the problem is exaggerated on wifi or vpn because of the mb's that are transferred to the clients. I have tried to hide the image column (the one for viewing) but the performance dip stayed. Almost as if it is slowed by the existence of the image blob varbinary column. Is the system perhaps trying to show the full images instead of smaller thumbnails? is there a way to limit the mb usage while viewing the grid?
Database stored images are greatly reducing performance
+2- Sidekick
Best answer by Mark Jongeling
Hi Guido,
Uncompressed images can indeed cause some significant performance downgrade, especially when there are many. Pages per 200 records is a good way of making it less impactful, it's still X amount of MB per row.
It may be very useful to store the uncompressed images separate from the items, for example in a item_media table; so they are not lost, but placed separately.
With the use of the HTTP connector, you could connect with an API online or a webservice that can compress these images for you, then store those compressed images with your items. This should keep the performance reasonable, and will still show a still detailed enough image of the item. Would that be an alternative to look into?
Hopefully other Community members can also elaborate their own solutions to similar challenges 😄
+23Hi Guido,
Uncompressed images can indeed cause some significant performance downgrade, especially when there are many. Pages per 200 records is a good way of making it less impactful, it's still X amount of MB per row.
It may be very useful to store the uncompressed images separate from the items, for example in a item_media table; so they are not lost, but placed separately.
With the use of the HTTP connector, you could connect with an API online or a webservice that can compress these images for you, then store those compressed images with your items. This should keep the performance reasonable, and will still show a still detailed enough image of the item. Would that be an alternative to look into?
Hopefully other Community members can also elaborate their own solutions to similar challenges 😄
+16I would say vote for this idea:
Hiding fields doesn't mean they are not loaded anymore.. that's why it doesn't impact your performance because the image field in your case is still being fetched.
The other point is that if you upload a large image it will fetch the large image. While you should basically upload it optimized for your viewing experience. This is not supported by the SF, like for example in Wordpress you can upload an image and it will already optimize it for you or where you have the option to choose the dimensions for saving. I had the same problem and the solution was to actually normalize the images to standard sizes and lesser quality to really reduce the file size and thus improve the performance because of less MB's needed to be transferred.
+23Hi
Did Freddy and I help you out with this? Feel free to select the answer that helped the most as "Best answer” 😄
- Apprentice
We solved the problem with the images. Many images uploaded by the users are way too big and slow down the user interface. That is why we opted for a solution in SQL with an custom made assembly. There are other ways to resize the images, for example uploading to an API where the resized images are returned. We didn’t want to use such solution because you don’t know wat such party does with this data.
We created a class library in Visual Studio which resizes the images. The input is the original file data and width, the output is the reduced images data. We loaded the ouput dll into sql and made a function. With the code below, we reduce the images with a width of 200
it's not quite ready yet but it gives an idea how we solved it
declare @org_img nvarchar(max) = (select base64Image from item cross apply (select image_blob_data '*' for xml path('')) T (base64Image) where item_id = @item_id)
declare @reduced_image varbinary(max) = CAST(dbo.ClevrrResizeImage(@org_img, 200) AS xml).value('xs:base64Binary(.)', 'varbinary(max)')
update i set image_blob_data = @ reduced_image from item i where i.item_id = @item_id
disadvantage is that this doesn't work in the cloud, you can't add an assemblies here.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.