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:

28 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

Andrej Baranovskij 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

Anonymous said...

Hi Andreus,
great tip, as usual. :)

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

Andrej Baranovskij 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

Andrej Baranovskij 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

Andrej Baranovskij 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

Andrej Baranovskij said...

Hi Alex,

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

Thanks,
Andrejus

lucasdeabreu 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")>

Andrej Baranovskij said...

Hi,

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

Andrejus

lucasdeabreu 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?

Andrej Baranovskij 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.

Andrej Baranovskij 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.

Anonymous said...

At global level, I have set row limit say 500.
For a particular search criteria there are 200 records, it displays max 500 records which is as expected.
But on tabel's top left, we have empty cell by clicking which we can select all rows. But when I click on it, I get 'RowLimitExceededWarning' error.

Don Kleppinger said...

I would like to set this at runtime using a checkbox. Is there a way to set it programmatically? I don't find any setRowCountThreshold method on the DCIteratorBinding.

Andrej Baranovskij said...

There must be a way, should check ADF source code.

Regards,
Andrejus

Don Kleppinger said...

I found it in JUIteratorDef initialized throught the init method. I may blog about this if it works good.

JUIteratorBinding it = (JUIteratorBinding)getIteratorBinding("ViewIterator");
JUIteratorDef def = (JUIteratorDef)it.getDef();
HashMap init = new HashMap();
if (!Boolean.TRUE.equals(getPageFlowScope().get("skipRowCount"))){
init.put("RowCountThreshold",0L);
def.init(init);

} else{
init.put("RowCountThreshold",-1L);
def.init(init);
}

Andrej Baranovskij said...

Thanks for update. This seems like it should work.

Andrejus

Matt McPeak said...

Is there any way to customize the SQL executed by getEstimatedRowCount()?

I am trying to implement a RichTable where some of the filter columns use a SelectManyChoice box. I've overridden the query listener in my RichTable, and it works, as long as RowCountThreshold for my iterator is -1.

If I use RowCountThreshold = 0, which I would *like* to do, ADF immediately submits getEstimatedRowCount which generates SQL using the _original_ filter criteria, not the filter criteria modified in my query listener.

I would be very grateful for your guidance.

Anonymous said...

Hi..

I have tried all the above solutions. I have also tried overriding QueryHitCount method. But my query is still extremly slow as compared to when I run same query directly.
All the time is taken by executequeryforcollection method. My query have few million records and have 10 filters. My view is query based.

Any suggestions.

Thank you

Andrej Baranovskij said...

You should check how many records are fetched from DB. If you fetch all million records - that will be very slow :)

Andrejus

Anonymous said...

Hi Andrejus

The settings you mentioned works perfectly for tables. However, if the table displays the results from an af:Query component, SELECT COUNT(1) is still fired regardless of making those settings. Do you think this is an ADF defect?

Peter said...

Hi Andrejus,

I have a slowness during the startup of adf page (using jdev 12).
I trie to modify the log level of ADFLoggerDiagnosticImpl to Finest. I've noticed that the view object executed twice, first time to get the row count and the second time to get the data. The execution of the view object done from the java bean class, and the VO retrieved from adf page definition using findIteratorBinding, the view object not used in the screen as adf table, only I need the vo to verify something.
I have putted the rowCountThreshould = -1 in the page definition, but I've noticed that the VO still executed twice in the log.



<[59870] XXXVO ViewRowSetImpl.doSetWhereClause(-1, date, 01/06/2021 13:46:02)>
<[59873] XXXVO ViewRowSetImpl.execute caused params to be "un"changed>
<[59874] Carrying over CappedRowCount:-1for ViewRowSet:XXXVO>
<[59875] Column count: 4>
<[59876] XXXVO ViewRowSetImpl.doSetWhereClause(-1, date, 01/06/2021 13:46:02)>
<[59879] executeQueryForCollection ViewObject:XXXVO, RowSet:XXXVO>
<[59880] XXXVO>#q old SQLStmtBufLen: 1197, actual=1167, storing=1197>

Thank you