Re: Tomahawk+DataScroller+WorkingWithLargeTables

[Paul Iov]

...Well, let's check it step by step. In my JSP (2 Daniel): <ac:ajaxDataScroller       rendered="#{Recherche.data.rowCount gt 0}"       id="scroller"       for="tblData"       fastStep="10"       renderCurrentAsText="false"       maxPages="9"       ajaxSingle="true" /> ... <t:dataTable id="tblData" value="#{Recherche.data}" var="foto" rowId="#{foto.id}" rows="#{Recherche.rowsPerPage}" newspaperColumns="4" newspaperOrientation="horizontal"> <t:column>             <ui:include src="mediaview.xhtml"></ui:include>       </t:column> </t:dataTable>   I think, the lines var="foto"       rowId="#{foto.id}" are essential in JSP. I may be wrong, but the dataTable needs to identify the items somehow...   I've collected also the 'bean's code' from some levels of class hierarchy: //in bean itself public final DataModel getData(){ return _getPagedList().getDataModel(); }   //'standard' implementation of _getPagedList() in base class: //(Since I have many different PagedLists, the common methods //are extracted to). private IPagedList _pagedList = null; protected IPagedList _getPagedList() { if (null == _pagedList) {//pay attention to this check!             _pagedList = new PagedListFoto(GetMiddlewareConnector()); _pagedList.setRowsPerPage(rowsPerPage););       }       return _pagedList; } The private member _pagedList is also initializes 'lazy' -> just once per instance.   Then in PagedList. private DataModel dataModel; public PagedListFoto(SesEJBConnector ejbConnector){             super(ejbConnector);             dataModel = null; }   This one we have discuss early... public DataModel getDataModel() {         _log.debug("-------------- GetDataModel() called -------");         if (dataModel == null || _invalidated) {             dataModel = new LocalDataModel(rowsPerPage);             _invalidated = false; //set 'dirty' flag         }         return dataModel; }   And then very deeply in base class: constructor public PagedListBase(SesEJBConnector ejbConnector){             _log = LogFactory.getLog(this.getClass());             myConnector = ejbConnector;             internalStorage = new ArrayList<Integer>();//list of IDs             _invalidated = true; } public int getCount(){       return internalStorage.size(); }   // this two methods are used during fetching of page data protected List<Integer> getSubList(int startRow, int pageSize){       int toIndex = Math.min(internalStorage.size(), startRow + pageSize);       return internalStorage.subList(startRow, toIndex); } protected String getStrIdCommaList(List<Integer> subList){         StringBuffer sb = new StringBuffer();       for(Integer ID : subList)             sb.append(sb.length() == 0 ? ID.toString() : ","+ID.toString());                         return sb.toString(); }   Well... not especially clear, but still better then nothing :) The rest (how the items are fetched) was in rar. How and from where to populate this internal storage should be trivial.   I have no ideas, why the framework access getDataModel() again on going to another application page, but after many hours studying of source code of original t:dataScroller it's not really great surprise to me :) It was exactly the reason to use ac:ajaxDataScroller instead.   2 Vlad Daniel was quicker with the answer. I've traced this again in my code... two calls to getDataModel() should be the only source of duplicated DB access. (Regardless of bean's scope, two components access this property.)   2 Vladimir >DEBUG === LocalDataModel.fetchPage startRow: 0 pageSize: 5    ??????? >DEBUG **Fetch: required -> fetching..   I'm not sure if it's not a result of some "preserve data model"-tricks (of t:dataScroller? dataTable?)... Any way, it's not the same class instance where the old (startRow/pageSize) values are stored :) I would try to log instance's hash or check the scope of bean.   > 2. I think, this property is simply omitted in .tld or facelet taglib. In fact the scroller is a composed component. It consists of (at least) panelGrid, some commandLink-s and facet-s. So it's just not clear, which one should use this value :) Anyway I'll be wondering, if this warning affects the fetching.   And generally, about the strategy to handle large recordsets... I don't think, some really better solution is possible :) The scroller needs this total record count to know, how many pages to generate. (Don't forget: the DataModel must also can be able to 'report' this value.) In my implementation I have an internal storage... let's say, an ArrayList of IDs (integer or long), so I'm doing not separate "select count(*)..." but "select ID from..." (with all filtering/ordering stuff) already in constructor and fill this list just once. After this I have the total count automatically and each statement to fetch particular data page is "select ... where ID in(...)". Since the field is indexed, it's pretty quick... probably, the best possible choice. I have extra implemented the dirty flag to force 'hard' reload of this internal list, if some changes was made, that can affect the order or total count of items (edit/add/delete). The call hierarchy in fetching chain is trivial: getInternalList().getSublist(). The first one reloads the IDs if data are 'dirty' (this happens during first call from constructor), the second composes IDs of requested subset (page) into comma separated string. Since I'm working with EBJ, I haven't to reload whole dataset each time when just one 'record' is changed - if the entity was successfully persisted, I have already the actual 'copy'. (I guess Daniel, you are using event listener in your backing bean to edit data directly in PagedList? It's the only way to have actual copy displayed without to reload this page.)   I'm working with kind of snapshot (search results) with extra buffering. My internal 'in memory' page is up to five times larger as my displayed page. Since the user can adjust the count of displayed items, I'm adjusting also the size of internal buffer dynamically to keep roundtrip time reasonably even if 'on next page' the next internal frame of data must be fetched. I try also to predicate 'next' portion of fetched data, but it's different long story :) That's why I'm not posting all of my code... it's scattered over dozen of classes and packages :) This simple strategy (even without predication) works fine for me. By resultsets of 20000~30000 records and displayed page of  8-80 items (5~7 joined tables each) the response time is under 500ms (normally 40~60ms / click) (JBoss/EJB3/Oracle) If the dataset must be really 'live' (I mean, if after each click the page/scroller must reflect the data in DB EXACTLY), it should be also necessarily to implement some smart strategy to keep 'current' page over roundtrip etc. (The first one thinkable pitfall with t:dataScroller: we are at page n with m items per page. Then we navigate to n+1 and back, but the items are not more the same-> someone have added m items to the dataset and they are now displayed at page n due to sorting order.) However I'm not sure, if one really needs in this case some datascroller... rather some scroller with fixed length of resultset (?may be, incremented on demand etc.)     Regards, paul Vladimir Isakovich schrieb: Hi, Paul, Daniel and all list. I'm really glad finding this discussion, I guess in all serious projects there is a need for reading large tables. I was reading the Wiki page, and had a trouble of implementing it, though after reading this thread I've got it working, at least to some extent. I'm using Facelets 1.1.11 with MyFaces 1.1.3 -snapshot and tomahawk 1.1.3 (I was trying later versions, but facelets complained on server start, althoug I did not see real issues). The DB is MySql 5 and Hibernate is DB wrapper..   I do have some questions (3 total): 1. I've added your method in my bean:           public DataPage<Customer> fetchPage(int startRow, int pageSize) {             log.debug("=== LocalDataModel.fetchPage startRow: "+ startRow+" pageSize: "+pageSize);             return doFetchPage(startRow, pageSize);         } Still, I can see two DB roundtrips when doing 'next page' this is what I see in the log: OPENING PAGE First Time - no problem Jul 5, 2007 1:12:09 PM com.sun.facelets.tag.jsf.ComponentRule warnAttr WARNING: /web/sec/examples/largeTableScroller.xhtml @104,85 border="1" Property 'border' is not on type: org.apache.myfaces.component.html.ext.HtmlGraphicImage DEBUG === LocalDataModel.fetchPage startRow: 0 pageSize: 5 DEBUG **Fetch: required -> fetching... DEBUG === Gettin new page. startRow = 0 size: 5 DEBUG attempting to initialize the SessionFactory INFO  Hibernate 3.2.4.sp1 ..... multiple infos from Hibernate about session initialization......... Hibernate: select this_.Cust_ID as Cust1_0_0_, this_.Name as Name0_0_, this_.Phone_Number as Phone3_0_0_, this_.Street_A ddress as Street4_0_0_, this_.City as City0_0_, this_.State as State0_0_ from customers this_ limit ? DEBUG === LocalDataModel.fetchPage startRow: 0 pageSize: 5 DEBUG ***Fetch: not required (already fetched)! INFO  entering RESTORE_VIEW(1) in org.apache.myfaces.lifecycle.LifecycleImpl INFO  entering RESTORE_VIEW(1) in org.apache.myfaces.lifecycle.LifecycleImpl SCROLLING TO THE NEXT PAGE - unwanted read INFO  entering RESTORE_VIEW(1) in org.apache.myfaces.lifecycle.LifecycleImpl INFO  entering APPLY_REQUEST_VALUES(2) in org.apache.myfaces.lifecycle.LifecycleImpl DEBUG === LocalDataModel.fetchPage startRow: 0 pageSize: 5    ??????? DEBUG **Fetch: required -> fetching... DEBUG === Gettin new page. startRow = 0 size: 5 Hibernate: select this_.Cust_ID as Cust1_0_0_, this_.Name as Name0_0_, this_.Phone_Number as Phone3_0_0_, this_.Street_A ddress as Street4_0_0_, this_.City as City0_0_, this_.State as State0_0_ from customers this_ limit ? DEBUG Page Size: 5 DEBUG === LocalDataModel.fetchPage startRow: 0 pageSize: 5 DEBUG ***Fetch: not required (already fetched)! DEBUG === LocalDataModel.fetchPage startRow: 5 pageSize: 5    - this is what was expected DEBUG **Fetch: required -> fetching... DEBUG === Gettin new page. startRow = 5 size: 5 Hibernate: select this_.Cust_ID as Cust1_0_0_, this_.Name as Name0_0_, this_.Phone_Number as Phone3_0_0_, this_.Street_A ddress as Street4_0_0_, this_.City as City0_0_, this_.State as State0_0_ from customers this_ limit ?, ? DEBUG Page Size: 5 INFO  entering RESTORE_VIEW(1) in org.apache.myfaces.lifecycle.LifecycleImpl INFO  entering RESTORE_VIEW(1) in org.apache.myfaces.lifecycle.LifecycleImpl Well, apparently the framework (can I say it better?) is calling fetchPage() twice. First - with the old startRow value and second -with the new startRow value. 2. I also can see a warning:WARNING: /web/sec/examples/largeTableScroller.xhtml @104,85 border="1" Property 'border' is not on type: org.apache.myfaces.component.html.ext.HtmlGraphicImage but it seems that the property gets applied. (I can see the differense in apearance, when the border=1 is removed). 3. I also have a question about dataSize in your DataPage. Does it mean that the for a first time I have to run something like "select count(*)..." and then store this value to the session, so it may be retrieved for all consecutive reads? Do you have a better solution?   On 7/5/07, Paul Iov <[EMAIL PROTECTED]> wrote: Hi Daniel, unfortunately I have neither some simple project nor article explaining this. I have implemented it in my own meta framework which is part of a big commercial application. (It consists currently of 6 or 7 different Eclipse projects, so it would be really difficult to extract 'clearly' the code, you are asking for.) I'm attaching just three classes from my early 'playground' code. Sorry, it's the best I can offer right now :( You will not be able to compile this (because the rest of my stuff have nothing to do with fetching itself and would be more complicated as helpful) but you can track the approach. There are three methods, which shows how it works and how to get ride of duplicated (exactly: multiple) fetching of the same data: getPage() and doFetchPage() in DataPagedListDataModel<T> and getDataModel() in PagedListFoto (which extends the DataPagedListDataModel indirectly) The solution is to separate the fetching call into own method in base abstract class. (Why it should be abstract is explained in Wiki.)    private int _lastStartRow = -1;    private int _lastPageSize = -1;    private DataPage<T> _lastPage = null;    private DataPage<T> doFetchPage(int startRow, int pageSize){        if ((_lastPage == null) || (_lastStartRow != startRow) || (_lastPageSize != pageSize)){            _log.debug("**Fetch: required -> fetching...");            _lastPage = fetchPage(startRow, pageSize);            //store values to chech next time            _lastStartRow = startRow;            _lastPageSize = pageSize;        }else{            _log.debug("***Fetch: not required (already fetched)!");        }        return _lastPage;    } I store then the startRow and pageSize values and check it every time the new fetch is 'requested'. Just take a look at my debug statements entire the code, they should be self explained. The fetching take place exactly one time for particularly page of data. One more thing to be clearly explained here: the getDataModel() of 'enclosing' nested class (PagedListFoto in my case)  MUST BE CALLED TWICE, since you have two components in your JSP -> the DataTable and the Scroller and both are accessing this property. So you have to handle this situation yourself, i.e. directly in getter: public DataModel getDataModel() {      _log.debug("-------------- getDataModel() called -------");        if (dataModel == null) {            dataModel = new LocalDataModel(rowsPerPage);        }        return dataModel; } I hope it helps. Regards, paul P.S. As mentioned, I'm not using t:dataScroller since it's buggy in combination with AJAX (at least: it WAS buggu in 1.1.4 and 1.1.5), but I think, it makes no difference regarding fetching approach itself. I've send my code with few minor improvements to Adrian Mitev (author of original component, which was published at https://ajax4jsf.dev.java.net/servlets/ProjectDocumentList?folderID=6510&expandFolder=6510&folderID=5320 ). If you are interested in this component, just ask him about it. daniel ccss schrieb: > > Thanks!!! > > Paul do you have a simple project where I can see how all working > together, I'm really tire of search and search for solutions and > nothing , I´m new in this and really need help, thanks!! > > > P.D: have you publish some article explaining how all this works (your > DataScroller using AjaxDataScroller and the approach of the wiki)?... > you should i know that many people will appreciate it! >