View Source

Problem Statement

When a Datalist created is being viewed in an Userview's List, there are few things that will take place.

In this article, we will be using a custom query with JOIN statement (to get derived data set) to build the data set for the Datalist.The custom query will be placed in JDBC Datalist Database Binder.

This article's discussion and result is produced using MySQL 5.6 but should also be applicable to all RDBMS attached to Joget Workflow.

Let's set the stage first.

There are 2 tables, order and order_item.
order table has 1 million rows.
order_item has 2 million rows with foreign key order_id to table order and the necessary index added.
Every order has 2 order item.

This is the select query that we will use in the datalist binder.

SELECT o.*, i.* FROM app_fd_order o JOIN app_fd_order_item i ON o.id = i.order_id

Using the Datalist in a Userview

Depending on which Userview Menu you use, most of them would provide you the capability to show the row count appended to the menu name. The query we have here would take a long time to compute because the database would need to first execute the query (with JOIN) and return the data set entirely before it is able to count the records. The count query will be as the following:-

SELECT COUNT(*) FROM (SELECT o.*, i.* FROM app_fd_order o JOIN app_fd_order_item i ON o.id = i.order_id) temp

Retrieving derived data and then getting the row count will take anywhere from few seconds to few minutes to load depending on the actual row size and database performance.

This is the explain query result just in case if you are more inclined towards RDBMS kind of explanations.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL					2000000
2	DERIVED	i	ALL	Index 2				2000000	Using where
2	DERIVED	o	eq_ref	PRIMARY	PRIMARY	767	i.order_id	1

As you can see, there are 2 ALL type in retrieving the result. The definition of ALL type is as follow.

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables. (Source)

Viewing a List in a Userview

Clicking on the menu item will open up the Datalist. In a typical listing, we will have the pagination feature to traverse through records. For pagination to work, we will need to first (again) compute the number of records we have in total. The count query will kick in again this time around.

Even though in a single view, the listing will only list down limited number of records (e.g. 10 per page), which should normally take less than a second to load, it will be heavily compounded by the count query earlier that may take more than 1 minute to finally load.

If the database server has stellar buffer or caching size/capabilities, then subsequent viewing of each page will not be penalized by the expensive count query.

Conclusion

When you start to design your App, you will need to try to project the amount of data that will be pouring in over a considerable amount of time and evaluate if your App can still serve and hold up well by then. You can try to also make use of Performance Analyzer for continuous monitoring of your App's performance.

Only use a derived query when it is absolutely necessary. Try to have the data set computed elsewhere or beforehand when possible.