Saturday, May 26, 2012

How To Disable SELECT COUNT Execution for ADF Table Rendering

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 AutoHeightRowsRowCountThreshold will be ignored and framework will force SELECT COUNT execution. AutoHeightRows property allows to render table height dynamically, based on current number of rows:

18 comments:

Martin said...

Hi Andrejus,

very very good posting! We had performace problems in a production environment because the framework fires the COUNT-statments to the databse (to some big db-views with many rows in the underlying tables, this is very cost intensive).

We override the getEstimatedRowCount-Method and return the result from getFetchedRowCount. This work because the view object has a max fetzsize of 500. But the property RowCountThreshold="-1" is much better and easier.

We have some performance problems with the adf-tree too, do you think RowCountThreshold="-1" works for tree too? I made a litle test and it seems ok, but did you have experience in a production environment with a adf-tree and RowCountThreshold="-1"?

Thank you and best regards

Martin

Andrejus Baranovskis said...

Hi Martin,

Yes, we are using RowCountThreshold="-1" for the tree as well, it eliminates Select Count. Also consider to use Retain View Link Accessor option.

In case you will spot some issues related to RowCountThreshold="-1" and tree - please post.

Andrejus

Stephen J. said...

Hi Andreus,
great tip, as usual. :)

How do you get the "Top SQL..." window?

Andrejus Baranovskis said...

Hi Stephen,

You need to run separate instance of Oracle SQLDeveloper, there is no such report from JDeveloper. Connect as System user and you will access to the report.

Regards,
Andrejus

Anonymous said...

TOP SQL Statements:
select * from v$sqlarea v order by v.first_load_time desc

Could help @Stephan

Anonymous said...

Hi Andrejus!
I guess RowCountThreshold=-1 brings also a side effect by column sorting. Sorting is working only by rows that have been selected by first fetch, the rest unfortunately is not included in sorting.

Thanks
Alexander

alex_ssr said...

Hi Andrejus!
One more comment about side effects that RowCountThreshold=-1 is causing by me.
I'm using JDev 11g 11.1.2.1.0. I have applied RowCountThreshold=-1 to my EmpIterator. Then I created a context menu on corresponding table to implement record duplication using creatInsert. To get source record for duplication I tried the next code :
BindingContainer bindings = getBindings();
DCIteratorBinding dciter = (DCIteratorBinding)bindings.get("EmpIterator");
Row currentRow = dciter.getCurrentRow();

so currentRow was containing in all cases the first row from collection->always the first row!!!

Then I tried:

RichTable table = this.empTableHandler;
Iterator iter = table.getSelectedRowKeys().iterator();
CollectionModel model = (CollectionModel)table.getValue();

JUCtrlHierBinding treeBinding = (JUCtrlHierBinding)model.getWrappedData();
if (iter != null && iter.hasNext()) {
List rowKey = (List)iter.next();
JUCtrlHierNodeBinding rowData = treeBinding.findNodeByKeyPath(rowKey);
System.out.println("FirstName =" + rowData.getAttribute("FirstName"))
}
FirstName was not coming out at all.
I have debugged and saw that iterator was not null, but his collection was empty, so iter.hasNext() was equal to false.

When I changed back RowCountThreshold to 0. Everything started to work properly.

I guess we have to pay attention to those side effects or bugs.

Best Regards
Alexander

Andrejus Baranovskis said...

Hi,

I was testing as you have described - I can't confim your findings. It works well with my sample app - entire rowset is filtered, as you scroll. Please double check.

Andrejus

Andrejus Baranovskis said...

Hi Alexander,

I can't agree regarding selected row as well. It works with RowCountThreshold = -1. I have tested it with provided sample app (JDev 11g R2 11.1.2.1.0). There is no bug.

Make sure you have selection defined on the table.

Andrejus

alex_ssr said...

Hi Andrejus,

thanks for quick answer.
Selection is there, at least if we are talking about the same properties:
af:table value="#{bindings.EmpVI.collectionModel}" var="row"
rows="#{bindings.EmpVI.rangeSize}"
emptyText="#{bindings.EmpVI.viewable ? 'No data to display.' : 'Access Denied.'}"
fetchSize="#{bindings.EmpVI.rangeSize}" rowBandingInterval="0"
filterModel="#{bindings.EmpVIQuery.queryDescriptor}"
queryListener="#{bindings.EmpVIQuery.processQuery}" filterVisible="true"
varStatus="vs"
selectedRowKeys="#{bindings.EmpVI.collectionModel.selectedRow}"
selectionListener="#{bindings.EmpVI.collectionModel.makeCurrent}"
rowSelection="single" id="t5" styleClass="AFStretchWidth" columnStretching="last"
binding="#{HelperBean.empTableHandler}" contentDelivery="immediate"
partialTriggers="cmi1"


I will work on example. Please let me know location where I could deploy it when it is ready.

Best Reagrds
Alexander

Andrejus Baranovskis said...

Hi Alex,

When you will have sample app, just send it to me by email to check.

Thanks,
Andrejus

Lucas said...

Hi Andrejus, I've tried set RowCountThreshold like you, but my app is keeping use count on select.

I'm getting this exception when I try to sroty any column :

<05/11/2012 09h55min49s BRST> <2012-11-05 09:55:49.511--UnitOfWork(10194008)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Column "Funcionarios.nome" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Error Code: 8127
Call: SELECT COUNT(id) FROM "Funcionarios" ORDER BY nome DESC
Query: ReportQuery(referenceClass=Funcionarios sql="SELECT COUNT(id) FROM "Funcionarios" ORDER BY nome DESC")>
<05/11/2012 09h55min49s BRST> <2012-11-05 09:55:49.542--UnitOfWork(27153634)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Column "Funcionarios.nome" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Error Code: 8127
Call: SELECT COUNT(id) FROM "Funcionarios" ORDER BY nome DESC
Query: ReportQuery(referenceClass=Funcionarios sql="SELECT COUNT(id) FROM "Funcionarios" ORDER BY nome DESC")>

Andrejus Baranovskis said...

Hi,

You are using MS SQL server. I was testing this with Oracle DB.

Andrejus

Lucas said...

Thanks for the response, probably is the MSSQL server. I tried run the project on MySQL and it's worked. But unfortunately I have to use MSSQL server.

Have you got any clue?

Andrejus Baranovskis said...

Nope..., may be you should register it as a bug for MSSQL.

Andrejus

Anonymous said...

Andrejus,

I see below issue with RowCountThreshold="-1", when used with poll refresh

User scrolls to some 5 (or) 6 pages down and if we refresh the table(with a poll,) then user loses his current row selection and will load with first 25 records (my range given is 25) as if a fresh query is run.

If u dont have RowCountThreshold="-1" it works fine.

Andrejus Baranovskis said...

Probably this is specific to poll refresh, because it performs submit.

Andrejus

Shrikant1988 said...

Hi Andrejus,
Can you please below point mention in above post.
"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"

I have enabled both autorowHeight:6 and RowCountThreshold = -1 in page def.I cannot see autorowHeight taking precedence over RowCountThreshold. In short now count query is not getting executed.