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 to build the data set for the Datalist. The custom query will be placed in JDBC Datalist Database Binder.
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.
- Every order has 2 order item.
This is the select query that we will use in the datalist binder.
QuerySELECT o.*, i.* FROM order o JOIN 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 query will be something like...
SELECT COUNT(*) FROM (SELECT o.*, i.* FROM order o JOIN 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.
Viewing a List in a Userview
Clicking on the menu item will open up the Datalist. In a typical listing, we will have pagination feature. 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.