2 Replies Latest reply on Feb 14, 2012 11:49 PM by Alexander Varakin

    dataTable is very chatty

    Alexander Varakin Newbie

      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]

       

       

       

        • 1. Re: dataTable is very chatty
          Brendan Healey Master

          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.

          • 2. Re: dataTable is very chatty
            Alexander Varakin Newbie

            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