If you analyze SQL execution, you will see there is difference between ADF form and ADF table rendering. When rendering ADF form - there is one SQL statement executed, to retrieve data. Things are a bit different for ADF table - framework executes two SQL statements while rendering ADF table. First it executes SELECT COUNT to retrieve number of rows to be returned and only after that it executes actual SQL statement to bring data. ADF needs to know estimated row count, in order to render table scrollbar properly. Perhaps this part can be optimized by ADF team, they could count retrieved rows based on fact (based on SQL to retrieve rows), without executing initial SELECT COUNT. However, if you want to optimize ADF table rendering and disable initial SELECT COUNT execution - there is a way, I will describe it now (thanks to my Red Samurai colleague - Florin Marcus, who figure it out).
Download sample application - QueryOptimizationTableApp.zip.
When ADF table is rendered with default settings, it gets information from initial SELECT COUNT query about estimated number of rows - you can see that based on table scrollbar, it knows how many rows are there:
We can track executed SQL statements, both SELECT COUNT and actual SQL to retrieve data invoked:
To disable SELECT COUNT execution, go to Page Definition and select table iterator:
Open Property Inspector and locate RowCountThreshold property. Read description for this property, by default its value equal to 0. This means it executes SELECT COUNT:
You can change it to be -1, it will not execute SELECT COUNT anymore, this will allow to render ADF table a bit faster:
There will be no SELECT COUNT executed for this table anymore. But you will see the difference as well - ADF table scrollbar is not adjusted to the total number of records in the table. If you user will scroll to the end of range size - ADF will fetch next range size and scrollbar size will change. Compare scrollbar appearance with the one when SELECT COUNT was executed:
SELECT COUNT execution might be quite slow, depending on data structure - it can be good optimization technique to disable it. But as always, there is no golden rule and all depends on specific use case and requirements. Its important to know tuning techniques and apply them smartly.
We can double check - indeed, SELECT COUNT was not executed anymore with RowCountThreshold = -1:
Keep in mind, RowCountThreshold tuning property is not applied if your ADF table is enabled with AutoHeightRows - RowCountThreshold will be ignored and framework will force SELECT COUNT execution. AutoHeightRows property allows to render table height dynamically, based on current number of rows:
