Any thoughts on how much work it would be for Epicor to do this? Seems like it shouldn’t be that big a deal? Or just add some votes please?
2 Likes
Registering a table for use in a BAQ is a minor amount of work but Ice.FileStore was intentionally excluded.
As you know, the FileStore table was designed to hold images and images are generally large to very large so adding an image field to a BAQ will have a very negative impact on query performance, will result in memory related failures on the AppServer or Clients, and network latency will also increase.
That said, your Ideas request is to allow the Image from FileStore to be used in reporting which is a reasonable use although even there, the performance and memory failures are still possible.
I believe the original design for the FileStore images included the use of images in reports via a Report Style extension. I will check with the team on that and how the capability works with BAQ reports.
3 Likes
For more data points about storing images in the database in general:
Brent Ozar’s opinion: Don’t do it.
Microsoft Research’s Opinion: Ok for files less than 1MB
https://www.microsoft.com/en-us/research/wp-content/uploads/2006/04/tr-2006-45.pdf
Filestore is available to add to a report data definition so excluding it from BAQs doesn’t make much sense - you are still querying the data either way - why is it ok one way and not the other? Our images are not large, they are just small logos.
Its not that simple in cloud. The database is the simplest and least fragile way to store small images like logos that almost never change.
Epicor started going down the road of solving this problem properly, with the companies/images tab in the report style, but unfortunately failed to develop anything usable. So what we are left with is filestore. Or hardcoded images on reports with visibility rules, or different report styles for every company, both of which are untenable once you get into the dozens of companies.
If that was the only use, then I (and Microsoft Research) are all for it. But we also use FileStore for Employee and Part images, which - in my experience - are often larger than 1MB.
As a cloud user, I agree with you. As a cloud provider, I know that large images have to be read into RAM during queries and that can affect the performance of the SQL Server and ALL of the users on that server. Also, as a cloud developer, one is used to sending a URI and having the client retrieve the image when needed and not use the server resources. I understand why one might do it in WinForms but it feels unnatural in a web context. 
Your case is the proper use for a blob field. It’s too bad that it grew out of that original purpose. Maybe Epicor could add a scalar function to return company logos and encapsulate away the need for FileStore.
But, I am allowed to use filestore as a table datasource in an rdd. I don’t understand how the performance ramification is any different vs. a baq. In addition you can see the convoluted workarounds people have come up with to query filestore on getlist in an updateable BAQ, so again, how is that any different? If its accessible, then let it be accessible in the simplest way possible (in a baq) and let shoot myself in the foot if I want to lol.
It’s just that in the cloud, we’re shooting everyone’s feet who share that same SQL server. It’s like being that guy in the neighborhood that runs a torrent server which makes Netflix buffer every 30 seconds.
Again, I agree. For your case, there should be a GetCompanyLogo function to supply easy access to those smaller images.
But how is it different if I use filestore in my RDD vs my BAQ?
It’s not. On a previous gig, we put the Part Image on the Traveler using an RDD. Some of them were quite large. This SQL Server was in our own Azure tenant, so I’m sure we paid more for our inefficient use of images.
See this is my point - Epicor has already accepted the performance risk, so really its just an issue of consistency (and making my life easier lol). The performance risk is irrelevant.
FileStore was an on-prem decision that Epicor might have done differently in the new SaaS world. I would be concerned that to mitigate the higher costs in storage, backups, and SQL sizing, they might introduce a new product charging for file usage. 
What’s the downside of using a URL in SSRS for logos instead of a blob? What’s the upside for the blob? With the URL, the same logo can be used by Kinetic and any other web-based apps you have. If there was a change, it would require only one update. 
Finding a place to put it. When you are cloud, you don’t just have servers lying around to put stuff on. You have to subscribe to yet another storage service, and hope you can figure out how to get Epicor to connect to it. And hope nobody forgets to pay the bill or else all your reports stop working one day.
We’re not already paying for a corporate web presence or 365 (Global Asset Document Library used by many companies for Corporate branding/Intranet)?
Nope. Setting any of that up requires involving internal and external IT resources. Putting an image inside Epicor requires nobody and nobody’s approval but myself. Back to my original point - simplicity.
I don’t envy your situation. If it’s easier to ask Rich to change Kinetic than to add a few dozen files to a SharePoint library (or tell you where they already are), the shadow IT there must be quite extensive!
of course asking and getting are two totally different things . . .
The FileStore table was a bit controversial (within Epicor Development) when it was added to the product. Allowing an image to be stored in the DB satisfied and simplified a number of functionality requests but the inclusion of the “blob” field in the DB is contrary to a purists view of a relational / transactional DB. We also had to adjust the standard pattern for creating the List and Rows data views in the Service definition to exclude the image field in those standard views.
Purist approach and Epicor tooling issues aside, I like the simplicity of having all the data in one location as it makes backup and restore much easier. In hindsight, we should have enforced a size limit on the image field and if we were to do the work now, we likely would do it in a different way. We are actively reviewing and adjusting some of the patterns / practices currently in Kinetic and while FileStore has not bubbled up, it does check the “Tech review needed” box.
BAQ vs RDD - Why not allow FileStore image in a BAQ since we allow it as an extension in the RDD?
The processing pattern of each is very different. BAQs return “sets” of data while RDD extensions work with single records. As an example, lets assume an average image size of 1 Meg and processing that returns 1,000 records and the image field added to both the BAQ and the RDD.
With that pattern, the BAQ processing will manage all 1,000 records in one request. That will require SQL to manage 1 Gig of space (memory and Temp DB disk) just for the images and then the AppServer process will also need to allocate 1 Gig for the images. Finally, that data will need to be encrypted and then transferred to whatever client requested the data where the client will need to decrypt and manage the space.
With the image added as an extension to the RDD, the RDD extensions are processed row by row so instead of asking SQL for 1,000 images in one go, we get 1 image with 1,000 separate SQL requests. That means that SQL and the AppServer only need to manage resources for one image at a time. While the row by row pattern is less efficient for small amounts of data, in the case of a “Blob” that processing pattern is preferred.
3 Likes
Wow thank you for the explanation. I am curious, what about a BAQ as a datasource within an RDD, which pattern is followed? What about the workaround where people use an updateable BAQ to get the image in the getlist method, does that go one row at a time too?
BAQs as a datasource within the RDDs follow the “get all data” with one request pattern. If multiple BAQs are defined within the RDD, the processing is as efficient as possible and there is only one interaction with SQL regardless of the number of BAQs in the RDD.
RDD extensions are currently not supported for BAQ based RDDs but if / when they are supported, that part would be row by row.
The resource pattern on SQL for the workaround of using a UBAQ to get the image will vary based on how the “get image” part is coded. Regardless of how the image is retrieved from SQL - all in one request or row by row - the impact on the AppServer, Network, and Client is the all records in one request pattern.
If images are being retrieved - via UBAQ or other methods - you must be cognizant of how the system resources are being used / abused. If using a pattern that returns multiple records with a single request, please limit the request to targeted small batches to avoid performance problems and potential memory related failures.
2 Likes