dataTable is very chatty
avarakin Jan 19, 2012 6:11 PMHi,
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:
- 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]