190101 - no subject - How to efficiently display data from mul

mjmallet over 8 years ago

How to efficiently display data from multiple tables in a report?

I have to create a report that will be retrieving all rows from 4-5 different tables. The client wants to have the ability to extract that report into excel.

Is it best to create a view on the database that would consolidate all tables and then create a data type/data store to get that information to display in my grid?

I want to limit the number of queries to the database. If I fetch the data from each tables in SAIL and store it to a local variable, and then query against those local variable.

Which options would be less effort? And what would be the best practice in this scenario?

Discussion posts and replies are publicly visible

Parents

0 sikhivahans over 8 years ago

@mjmallet Hi, construction of a database view is the ideal way to accomplish the use case. Not only the construction, the view should also execute very quickly, that is, it should be optimised to a maximum extent(for instance, making use of indices is one of the ways). Few practitioners here could assist you in case if you need any assistance in optimising the view.

An other way to perform a join is, you can query all the 5 tables individually and perform a join on the resultant datasets by making use of 'CDT Manipulation' plugin. But this way is complex.

When it comes to Appian, it's better to choose the way we query, depending on the size of dataset. If the size of the dataset is relatively small, then you can query the entire data and store it in a local variable. Else it would be ideal to query the data in batches, and that too it would be good to perform a query on need basis(For instance, in case of paging grid, data can be queried when the user paginates.)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 sikhivahans over 8 years ago

@mjmallet Hi, construction of a database view is the ideal way to accomplish the use case. Not only the construction, the view should also execute very quickly, that is, it should be optimised to a maximum extent(for instance, making use of indices is one of the ways). Few practitioners here could assist you in case if you need any assistance in optimising the view.

An other way to perform a join is, you can query all the 5 tables individually and perform a join on the resultant datasets by making use of 'CDT Manipulation' plugin. But this way is complex.

When it comes to Appian, it's better to choose the way we query, depending on the size of dataset. If the size of the dataset is relatively small, then you can query the entire data and store it in a local variable. Else it would be ideal to query the data in batches, and that too it would be good to perform a query on need basis(For instance, in case of paging grid, data can be queried when the user paginates.)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data