I've got a screen where we have a list of construction projects, and each one has a place to upload a powerpoint presentation. Now that we've gone live and users have begun uploading these files, we've noticed that the list of projects takes a very long time to load (15s or so). I believe it is because it is aggregating the binary data of those presentations during the preparation.

Is it possible for me to check whether or not a record with binary data exists without actually aggregating that data, so I can speed up the loading of this page? I have the binary data in a separate entity from the main project information.

If there's any other information I can provide please let me know.

Hi Jared,


the binary file is in the same list that you are showing? How are you saving it?

Because is supposed you have two tables where one of them is just to save the binary file, and then, you choose not fetching it in preparation screen.

I do have a separate table, but I currently have it fetching that table to check if a binary file exists. I want to do this so I can indicate to the user that there is or is not a file attached to their project, and give them the option to upload one if not. Is there a way to do this?

I have two ideas for doing it.

1) You can mark on the main table every time that upload attach is done, and just look at this table instead of binary table.

2) You can create an action function with an advanced query that receive like parameter the relationship Id. This action just return a bool, and your query just need to be like this:

SELECT 1 FROM BINARY_TABLE WHERE ID = @ID

This will never return the binary file, however you will execute this query every each line on your table;  

Hi,

If a construction project only can have 1 file. the foreign key of that binary can be on the constructed project entity. If you model like this you just need to check if the foreign key is filled.

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

If a construction project only can have 1 file. the foreign key of that binary can be on the constructed project entity. If you model like this you just need to check if the foreign key is filled.

Regards,

Marcelo

The powerpoint entity shares a 1-1 relationship with the Project entity. How can I check to see if the foreign key exists without aggregating it?

I feel like I must be missing something simple. Thank you both for your help.

Might be a simplistic solution but how about you add a boolean checker hasPowerpoint that gets set once binary data gets added?


Jared Slayton wrote:

Marcelo Ferreira wrote:

Hi,

If a construction project only can have 1 file. the foreign key of that binary can be on the constructed project entity. If you model like this you just need to check if the foreign key is filled.

Regards,

Marcelo

The powerpoint entity shares a 1-1 relationship with the Project entity. How can I check to see if the foreign key exists without aggregating it?

I feel like I must be missing something simple. Thank you both for your help.

If you have 2 entities:


1. ProjectFile with Id and BinaryContent

2. Project entity with Id and ProjectFileId + whatever attributes 


ProjectFileId is the foreign key for your first entity into your 2'nd entity.


You can bring only entity 2 and check if ProjectFileId is null. If it's null then you don't need to bring the ProjectFile entity as there's no file associated and you just generate the content for when the data is empty.


Hi Jared

" Updating entities that have large texts or binary data attributes is very slow. Having text attributes with more than 2000 characters will be mapped to binary data types in the database and usually binary data types tend to take longer to be changed/updated than simple data types. Additionally, updating a record which has a binary data type will take longer than a record with simple data types because data pages will need to be moved for the binary data type. It is best to avoid having binary data types in entities that change frequently. If this cannot be avoided, isolate them in their own entity. "


Regards

I thought I was onto something for a moment. I removed the powerpoint from the aggregate and set it only to aggregate when the download was triggered, but the If statement that checks if there is a powerpoint is still a problem.

I used the Entities -> ProjectFile -> GetProjectFile(Id) and set the id to the project, and set the If boolean to be whether or not that result was null. Unfortunately when I tested, it just throws an error instead of returning a null value:

An internal error occurred and was logged.
Please try again later or contact the administration team.
Sorry for any inconvenience.

[OSDEV1].DBO.[OSUSR_KJ0_PROJECTFILE] with key 3737 was not found.

Still trying to find the right way to do this. Paulo's suggestion about querying SELECT 1 FROM BINARY_TABLE WHERE ID = @ID sounds like it's probably what I want to do but it's a little advanced for me. I'll have to tinker with it and see if I can get it to work.

Solution

I think I solved it. I rebuilt the aggregate using Group By columns for each individual value I was calling on in the screen, and included the PowerPoint's filename as one of the groupings. I then pointed at the filename in the If statement and said if the filename is not blank ( "" ) then show that there is a PPT file available for download. When they click the link it calls an action that aggregates only that project's PowerPoint and lets them download it. The screen now loads lightning fast even after uploading several large files!

Thanks for all the ideas everyone!

Solution