Best Practice. Complex preparation vs. web-blocks to display list records in a table?

Best Practice. Complex preparation vs. web-blocks to display list records in a table?

  

Hello Everyone.

For my first post, I have a question on best practices regarding the use of web-blocks vs. a slightly more complex preparation.

My data source in this instance comes from three entities, and the display requirement is such that I cannot use a single aggregate or SQL query.

The retrieved data is to be displayed in a table-records widget, however two of the columns must also be concatenated lists of text.

Outsystems does not allow the use of a list-records widget inside a table-records column unless it is contained inside a web-block. As I understand it, a web-block requires its own preparation and in my case I would be constructing two quite similar web-blocks but for different attributes on different entities.

I also don’t foresee that these web-blocks will be reused* and therefore it seems like unnecessary clutter to me.  (*unless of course I can create a generic web-block that accepts generic input, but that is a question for another day.)

My alternative (possibly old-school) solution to this is to extract and manipulate the data into a structure in the preparation, and then append that structure to a list of structures which then becomes the source-record-list for my structure. In this case the concatenated attributes have already been assembled into a single text attribute which can then easily be displayed in the table-records widget.

It works, but I would like to know if there is any reason why the web-block approach is better/preferable to mine? If it is, what would be the reason for it.

Thanks in advance
Albert

Me again, I cannot seem to edit my post, so here are some additional questions and thoughts around it.

1. My understanding is that seeing as a list-records has to be inside a web-block for it to be displayed inside a table-records column, the preparation for the web-block would in any case execute for every row in the table-records widget. Is this correct?

2. Would the cursor-like manner in which I build the data in my preparation be any more or less efficient than the web-block preparation being run repeatedly?

3. Possible downside to my solution is having to create a structure specially just for this screen. Is that acceptable, or should single-purpose structures be avoided?

4. My solution makes use of three local variables. A single working variable of the structure, a list variable of the structure, and another single variable of the structure to facilitate simple initialization of the working structure. Is there any suggestion to avoid this may local variables of the same type?

5. An advantage to my solution is that I can carry a boolean flag in the structure which indicates whether any of the secondary aggregates returned data or not. In this manner I have additional control over the formatting of the other related columns in my final table records. E.g. if no records are returned from the GetLoadings aggregate I can display blank cells in the related numeric cells instead of zeroes as would normally be the case. In this example I can indicate via the displayed fields if there were no loadings whatsoever; if there were loadings and their value was zero; there were loadings and what their value was.

Hi Albert,

1. Yes, this is correct. For every line of the table-records the webblock is called and executed.

2. I would say it's better to do it up front, because this makes navigating more quicker.

3. A structure specifically for one screen, is in my opinion not bad.

4. I would suggest that you put all the logic in a single server action (e.g. GetData), that outputs the list-structure that you need. Then have a working list-structure (i.e. local variable in the screen). Use this list-structure a source for the table-records. Assign the output of the GetData action to the working list-structure.

This way you hide the complex logic and re-use the action for other purposes. Especially when you need for some reason to refresh the data. Then you can simply call the action again, assign the output to the working list-structure and ajax refresh the table-records.

Cheers,

Menno 

Solution

Hello Albert, 

I think this is a very interesting topic (that appears here in the forum from time to time ;) haha).

Before deciding which approach to use, you need to understand your requirements, including number of concurrent users to the screen, number of records being fetched in a page, etc.

If the number of concurrent users is small and the number of records being show is small, possibly the impact in performance if you use the web blocks to fetch the data will not be a problem. But the solution and maintenance of your code will be greatly improved.

If the performance is or may be in the future a problem, than I would use a different approach.

Instead of fetching data from different sources and processing it server side to construct a single lilst, I would opt to use a single SQL and use the advantages of the SQL dialect being used to put together the other data that must be show as a string list on both columns.

It will be much more efficient thant fetching three sources and the query complexity will no be bigger than the code complexity to process the data in your approach (while will require a litle more SQL knowledge). The downside is that if you are required to change database or install the application in a server using a differente database, you will have to review this code to the new database engine.

So, as I said, is a question of deciding what will have less negative impact.

In the better case, a change in UI design (if possible) would probably solve this issue in a better way for everybody.

Cheers.

Solution

Menno Hoogsteen wrote:

4. I would suggest that you put all the logic in a single server action (e.g. GetData), that outputs the list-structure that you need. Then have a working list-structure (i.e. local variable in the screen). Use this list-structure a source for the table-records. Assign the output of the GetData action to the working list-structure.

This way you hide the complex logic and re-use the action for other purposes. Especially when you need for some reason to refresh the data. Then you can simply call the action again, assign the output to the working list-structure and ajax refresh the table-records.


Thank you for providing answers and opinions to my questions Menno.
I didn't think of creating a server action, but that would make sense instead of having all this logic in the preparation. And, as you pointed out, it allows me to refresh the data much simpler! I hadn't thought of that.
Thank you for the heads up!

Albert

Eduardo Jauch wrote:

Hello Albert, 

I think this is a very interesting topic (that appears here in the forum from time to time ;) haha).

Before deciding which approach to use, you need to understand your requirements, including number of concurrent users to the screen, number of records being fetched in a page, etc.

If the number of concurrent users is small and the number of records being show is small, possibly the impact in performance if you use the web blocks to fetch the data will not be a problem. But the solution and maintenance of your code will be greatly improved.

If the performance is or may be in the future a problem, than I would use a different approach.

Instead of fetching data from different sources and processing it server side to construct a single lilst, I would opt to use a single SQL and use the advantages of the SQL dialect being used to put together the other data that must be show as a string list on both columns.

It will be much more efficient thant fetching three sources and the query complexity will no be bigger than the code complexity to process the data in your approach (while will require a litle more SQL knowledge). The downside is that if you are required to change database or install the application in a server using a differente database, you will have to review this code to the new database engine.

So, as I said, is a question of deciding what will have less negative impact.

In the better case, a change in UI design (if possible) would probably solve this issue in a better way for everybody.

Cheers.

Hi Eduardo,

I did wonder if it would come out that my question already existed in the forums. I think my searches (through Google) didn't quite find what i was looking for in the forum. I'll trawl the forums more directly in future. :-)

Thank you for the pointers on how to determine which is a better solution based on number of records and concurrent users. I'll keep that in mind.

As for the SQL, I have a few years of SQL experience and with the data layout required from the different tables if have not been able to do it in a single statement. Unless of course, the SQL allows the use of things like CTEs, temp tables or stored procedures. I'll go learn...

Thank you for pointing out that statements containing SQL might not be transportable. Good to remember.

Thanks

Albert



Hello,

Dont get me wrong. I pointed out that this question appeared before because it is an interesting question and I see a potential in it to an article, for example, because it seems to be a some common need :)
And search the forum is not easy, as the question may be there a few times, but not with the same phrasing, tags, etc.

Regarding the SQL, I was talking about things like this: https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

As you said "The retrieved data is to be displayed in a table-records widget, however two of the columns must also be concatenated lists of text.", that kind of solution came to mind. 

Of course, your specific data model may do this possibility impossible or too hard. I don't know your data model to say. But there is always this possibility.

In any case, this kind of requirements are usually problematic and using web blocks or complex processing in preparation will always have a negative impact in the application performance.

Cheers.


Imho there is a guideline for it (check the performance success pages) however every use case should be measured.

sometimes webblocks if cached should perform better instead of a big ugly sql.

it really depends on the situation.

but always beware where you going to spent the most time: client, server, database.


Hello Albert,

For each set of records to display in the list, do one single query and fetch the information to populate the widgets (containing web blocks inside the list), which can be iterated again when using pagination.

Avoid to have many calls to your data base. However, as other members explained, this depends on your use cases and critical system requirements.

Regards,

Eduardo Jauch wrote:

Hello,

Dont get me wrong. I pointed out that this question appeared before because it is an interesting question and I see a potential in it to an article, for example, because it seems to be a some common need :)
And search the forum is not easy, as the question may be there a few times, but not with the same phrasing, tags, etc.

Regarding the SQL, I was talking about things like this: https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

As you said "The retrieved data is to be displayed in a table-records widget, however two of the columns must also be concatenated lists of text.", that kind of solution came to mind. 

Of course, your specific data model may do this possibility impossible or too hard. I don't know your data model to say. But there is always this possibility.

In any case, this kind of requirements are usually problematic and using web blocks or complex processing in preparation will always have a negative impact in the application performance.

Cheers.


To add to the depth of complexity on this topic (in the hope it is turned into an article); The mentioned rules of thumb and solutions are valid for local entities, but how would these change in the mentioned scenario when having to handle internal and external entities?

As an example, the initial list is cached on the platform and the web block gets data from the external source.

The rules of thumb and considerations are still valid but are there any other best practices that should be followed or born in mind in this situation?

Other than the obvious "Don’t perform joins over linked servers" :)

Ouen



Hello Ouen,

In this situation specifically, I would say that a UI refactory to avoid this scenario is strongly advisable.
If this is not possible at all (convince the Client of the performance problem of such UI), you can try some things.

First, I think (but I'm not sure) it is possible to cache external data. This would improve performance.But even if possible, it can be not desirable, depending on information type, user requirements, etc.

While is not advisable to have joins over linked servers, you can always create a view or a temporary table that fetch only the data you really wants to join, so that the problem of the entire table being transferred between servers is avoided.

Other than that, probably in this case, showing less records and than fetching all records and processing data can lead to improve performance even if with local data this would get the contrary effect...

But again, database access timings, server load, etc, all have to be taken into consideration before deciding (or if trying to solve a bottleneck), as each case will be different.

Cheers.