Recommendation to store Picture/Image

Like (0) Share

Report

Posted on 10 Oct 2019 09:15:38 by Romain_Pham

Hello PowerApps community,

I have created a Audit Tracking application to help auditor to record issue during an audit with pictures.

I have migrate the database that are currently in Excel to an Azure SQL (Stadandard S0 10 DTU) and the picture that are host in One Drive (353 pictures 250 MB)

But i am facing now performance issues to display the pictures once push in Azure SQL Server.

First to query the Database to see the record take several minutes to display in SSMS.

But in PowerApps it's even worst it load for a really long time and even end up in Server Response Error.

It takes around 20sec to display only 20 pictures that are less than 1 MB, but it will take more than a 1 min if there is 5 pics over 1MB to 2MB.

While i didnt have issues when the pictures was on One Drive and I am now wondering if Azure SQL would be the best to store the pictures upload through powerapps and if maybe stay in OneDrive or Sharepoint or CDS would be better performance wise for at least displaying the picture ?

Any advice on that ?

PS : please note i am located in China, our O365 licence have been bought in France so i believe the server is host in Ireland and the Azure Database is in South East Asia . I don't know how much this has an impact but i don't have any ability to change that.

Categories:

Connecting to Data

All responses (4)

Answers (1)

Romain_Pham 38 on 11 Oct 2019 at 02:50:11

Like (0)

Report

Re: Recommendation to store Picture/Image

@v-xida-msft thanks for your reply.

The pictures are currently store in OneDrive and have excel file refers to it as you describe. And this works well.
If I want to move to one SQL server is becasue the delegation issue and PowerApps as of today can only read the 2.000 first items of an Excel table.
I am already well aware of the difference between the different storage my question was more about what is the optimal storage solution when dealing with Picture.
Anyway it seems that the server and licence location doesn't help my case and CDS or Excel table with OneDrive would be the way to go in my scenario if i want to improve performance when display pictures.
Other than the picture the rest of the data work super fine and quick after moving to SQL Azure just the picture displaying performance was not as good as expected.
Thanks for the other post about improving apps perfomance
Romain_Pham 38 on 11 Oct 2019 at 02:12:37

Like (0)

Report

Re: Recommendation to store Picture/Image

Hi @_douglasromao thanks for your reply.

I believe it's the blob I am saving as I just patch "Add_Button.Media " and then the pic got rename in one drive with weird name like "443d3003529d40868d49801d1a0fa0ff"
In SQL server i upload it as varbinary(max) as the image datatyoe will be deprecated soon in SQL Azure.
I did the test in storing it in vchar(max) but no difference in terms of performance.
Which difference would it make to save it in base64 ?

As well i was wondering how could I control the size of the media add by the user when using a computer ? on iOS and Andorid there the option to optmize file for upload but it seems not having it on the Web interface that could improve performance for the upload but as well the displaying.

I did some other test yesterday and it works somehow ok when I actually don't display all the pics at once but take adantage of the delegation. Even for 2-3 picture performance is good enough and it will take only few seconds what ever the size of it. as this is what happen in reality the user only need to see the 2-3 picture t most of his findings I guess that will do for the moment. If i got too much complaints by my users regarding that matters i will think about moving the picture in CDS.

So piece of advice for people working with Pictures and Image and have to import it use delegation to display it fast in your app 🙂
Verified answer

v-xida-msft on 11 Oct 2019 at 02:11:44

Like (0)

Report

Re: Recommendation to store Picture/Image

Hi @Romain_Pham ,

Based on the issue that you mentioned, I think this issue is related to the location difference between your Office 365 server and your Azure SQL Server.

The server location would affect the data retrieve speed from your PowerApps app.

Currently, as an alternative solution, I think the Excel file (formatted as a Table) may be good choice for you. You could consider add a ImageData[Image] column in your Excel table (hosted in your OneDrive), then add the Excel table as data source in your app.

More details about storing image data into Excel table, please check the following article:

https://www.eliostruyf.com/uploading-images-to-your-onedrive-with-powerapps/

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/tutorial-working-with-images-in-excel

In addition, I also think the CDS is a better choice for you. You could add a Entity in your CDS, then add a Image field, use the Image field to store image data from your app.

More details about the difference between the Excel table, SharePoint list, CDS and SQL Server, please check the following blog:

http://powerappsguide.com/blog/post/excel-vs-sharepoint-vs-sql-server-vs-cds

In order to improve your app's performance, please check and see if the following article would help in your scenario:

https://baizini-it.com/blog/index.php/2017/09/13/powerapps-improving-app-performance/

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/performance-tips

Best regards,
Community Power Pla... on 10 Oct 2019 at 09:20:57

Like (0)

Report

Re: Recommendation to store Picture/Image

Hi @Romain_Pham ,

The impact related to the location of the server could make it worst, because you have the latency between them - CDS is a really good option as well, mainly because is in the same environment.

One question that I have is, are you saving the blob of this image or the base64?