dataTable is very chatty

[avarakin]

Hi,

I am starting with Richfaces 4.1 and after some effort of setting up my project based on sortable-filterable-lazyloaded dataTable, I was able to get it running in Tomcat 7.

Now the problem is that I see that dataTable is extremely chatty while talking to database.

I can see that we are doing the following operations while changing from page 1 to page 2:

- Counting records :  7 times

- Getting data for page 1 :    7 times

- Getting data for page 2 :   4 times

There is no way I can put such an application into production - it will kill our database server.

Please let me know if I am doing something wrong.

More details on my environment if this hellps:

-Richfaces 4.1

-JSF 2.0 - MyFaces

-JPA - EclipseLink

-Tomcat 7

-Java 6

-Database AS400

Below is the full log of database operations provied by EclipseLink.

Thank you!

Alex

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [20, 0]

[EL Fine]: Connection(26895259)--SELECT COUNT(maa4nc) FROM DEVDTA.TXNPHY01

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [20, 0]

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [20, 0]

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [20, 0]

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [20, 0]

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [20, 0]

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [20, 0]

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [40, 20]

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [40, 20]

[EL Fine]: Connection(26895259)--SELECT COUNT(maa4nc) FROM DEVDTA.TXNPHY01

[EL Fine]: Connection(26895259)--SELECT COUNT(maa4nc) FROM DEVDTA.TXNPHY01

[EL Fine]: Connection(26895259)--SELECT COUNT(maa4nc) FROM DEVDTA.TXNPHY01

[EL Fine]: Connection(26895259)--SELECT COUNT(maa4nc) FROM DEVDTA.TXNPHY01

[EL Fine]: Connection(26895259)--SELECT COUNT(maa4nc) FROM DEVDTA.TXNPHY01

[EL Fine]: Connection(26895259)--SELECT COUNT(maa4nc) FROM DEVDTA.TXNPHY01

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [40, 20]

[EL Fine]: Connection(26895259)--SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT maa4nc AS a1, maa7nc AS a2, maa5nc AS a3, maa6nc AS a4 FROM DEVDTA.TXNPHY01) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

bind => [40, 20]

[healeyb]

JSF just does this, especially in datatables. I can't honestly say I've ever properly understood why it happens, I suppose

it's to do with the lifecycle phases, but I'm sure there are improvements to be made, and performance benefits to be gained

if it didn't do it! There's an explanation here: http://stackoverflow.com/questions/2090033/why-jsf-calls-getters-multiple-times.

The key thing is to design around this "feature". If you have a ...dataTable value="#{bean.someList}" then:

1. Absolutely don't access the database from bean#getSomeList().

2. Consider initially loading someList from a preRenderView event (f:event ... listener=). Remember that the preRenderView

    event will fire for both full page loads and partial (ajax) refreshes, so it often makes sense to check which is happening with

    FacesContext.getCurrentInstance().getPartialViewContext().isAjaxRequest(). I would typically load the data on a full page

     load only, and then:

3. Reload someList only when you've changed some data, i.e. if you have a delete row function, delete the row from the

     database, refresh someList and then rerender the table. There are plenty of optimisations possible here, like removing

     the deleted entry from someList without doing a full refresh from the database, but  it's probably best to "make it work first,

    before you make it work fast"!

On a more advanced level Richfaces also has the ability to update the dissection of specific table rows & columns using the

update="@rows" notation.

Regards,

Brendan.

p.s. It's often tempting to want to carry out step 2 using a constructor or @PostConstruct initialiser, but I've found that the

       preRenderView technique is better, but you can always experiment.

[avarakin]

Brendan,

Thank you for reply!

A workaround I thought of is this: before sending request to database do some caching, maybe at JPA level or  at application level but never got a chance to implement it.

On the other hand Primefaces does not have this issue - it fires lazy load only once.

So based on this and other factors we decided to go with Primefaces for the project.

Sad because I like JBoss a lot.

Alex