Custom DS and Paging

[jarekevi]

v9.0p_2013-12-11/PowerEdition Deployment (built 2013-12-11)

Browser: Firefox 25.0.1
System: Win 7, Win 8

I'm having performance problems using custom DS's.

Basically the situation looks following:
I have a dynamic growing DB table. And i want to load data in to a ListGrid every couple of seconds.

ListGrid :

 		
setDataFetchMode(FetchMode.PAGED);
setDataPageSize(1000);
setShowAllRecords(false);
setShowFilterEditor(true);
setAllowFilterExpressions(true);		
setFastCellUpdates(true);
setShowRollOver(false);
setShowGridSummary(true);			
setShowRecordComponents(true);		         
setShowRecordComponentsByCell(true);

fetch:

 
DataSource dataSource = getMessageGrid().getDataSource();	
		dataSource.setAllowAdvancedCriteria(true);				 
		DSRequest request = new DSRequest();
		request.setSortBy(getMessageGrid().getSort());
		dataSource.fetchData(crit, new DSCallback() {
			@Override
			public void execute(DSResponse response, Object rawData, DSRequest request) {		
				try {
					getMessageGrid().setShowFilterEditor(true);
					getPresenter().getView().getMassReviewUpperBar().getAdvancedFilter().setDisabled(false);
					DataSource dataSource = getMessageGrid().getDataSource();
					final ResultSet resultset = new ResultSet();
					resultset.setDataSource(dataSource);
					resultset.setCriteria(getMessageGrid().getCriteria());
					resultset.setFetchMode(FetchMode.PAGED);	
					resultset.setInitialLength(response.getTotalRows());
					Record[] responseData = response.getData();
					Record[] initialData = new Record[response.getEndRow()+1];
					for (int i = 0; i <= response.getEndRow()-1; i++) {
					    if (i < response.getStartRow()) 
					    	initialData[i] = null;
					    else 
					    	initialData[i] = responseData[i-response.getStartRow()];
					}
					resultset.setInitialData(initialData);
					resultset.setInitialSort(getMessageGrid().getSort());
					getMessageGrid().setData(resultset);
					resultset.destroy();
					getMessageGrid().filterByEditor();
				} catch (ClassCastException e) {
					getPresenter().getView().getExportBar().showFinishedWithErrorsLabel();	
					getPresenter().getView().getFilterBar().showFinishedWithErrorsLabel();
				}				
		       }
		 
		}, request);

At the beginning everything works fine i have a small slow down when fetch is called, but with increasing data, the slow down unables the ussage of the appliactation, becouse it becomes so long that the next fetch is called and the circle begins. Ataround 20k of recs the slow down starts to be unhandable. Every next fetch provieds ~1k of records.

The record dosent seam to be to havy

<fields>
		<field name="id" type="integer" title="id" primaryKey="true" hidden="true"/> 
		<field name="bcc" type="text" title="bcc" />
		<field name="cc" type="text" tite="cc" />
		<field name="extern_type" type="integer" title="type"/>
		<field name="hadoop_file_id" type="integer" title="hadoopFieldID" />
		<field name="import_date"  type="date"       title="import_date" /> 
		<field name="privilege_status" type="integer" title="pStatus"/> 
		<field name="recipient" type="text" title="to" />
		<field name="review_status" type="integer" title="reviewed"/>
		<field name="sender" type="text" title="from" />
		<field name="sent_date"  type="datetime" title="datefield"/> 
		<field name="subject" type="text" tite="subject" />
		<field name="sys_creation_date"  type="date" title="sys_creation_date"/> 
		<field name="technology" type="integer" title="type"/>
		<field name="query_info_id" type="integer" primaryKey="true" title="query_info_id" hidden="true"/>
		<field name="annotated" type="boolean" title="hasAnnotations" />
		<field name="attachments_hadoop_files_ids" type="any" title="attachments_hadoop_files_ids" />
		<field name="source" type="integer" title="source" />
		<field name="risk_level" type="integer" title="risk_level" />
		<field name="review_update_date" type="datetime" title="review_update_date" />
		<field name="employee_id" type="text" title="employee_id" />
		<field name="attachments_hadoop_files_ids" type="text" title="attachments_hadoop_files_ids" />
				
	</fields>

What i need is that the listgrid can sort/filter during data loading on the already provided data or on the DB table (which grows can grow to even 200k and larger sets records) the data should be paged to sets of 100 - 1k data sets, but the number of total records in grid summary should show the actuall size of the table.

I'm showing the number of records in the summary fields of one of the fields

 
subjectField.addSummaryFunction(new SummaryFunction() {
			
			@Override
			public Object getSummaryValue(Record[] records, ListGridField field) {
				return ""+records.length+" messages";
			}
		});

Question:
How should I fetch the data?

[Isomorphic]

Fetching 200k records every few seconds is going to overload your database before any SmartGWT-related performance concerns even come into play. So first, figure out whether you can deliver just deltas to the client instead of an entire refreshed list.

Once that is working, you probably don't want the client-side data to grow to an unbounded extent. You could choose a fixed size and only show the last 1000 rows for example, with controls allowing the users to go further back in history if they need to.

[jarekevi]

The DB is no problem I have dedicated clustered servers for the DS. I would like to use the grid summary to show the number of messages found, if not i will just add an additional lable.
But the question is that if i stop loading data after a set number of records and allow the user to fetch them afterwards, is the Listgrid going to withstand the amound of data - (select all(checkboxes), sort, filter ect.)

[Isomorphic]

The DB is no problem I have dedicated clustered servers for the DS.

Just a comment that clusters fall over under heavy load too. 4 machines is only 4x faster in the best possible case.

But the question is that if i stop loading data after a set number of records and allow the user to fetch them afterwards, is the Listgrid going to withstand the amound of data - (select all(checkboxes), sort, filter ect.)

It's not clear what scenario you're talking about.

If you're asking whether a ListGrid can work with a partial cache of a multi-million row database and provide all those operations, then yes.

If you're asking whether a ListGrid can work with an in-memory fully loaded dataset of that size, the answer is a qualified yes. The code is well optimized, but older browsers like IE6-8 are 100x or more slower than modern browsers, and generally cannot cope with million+ in-memory datasets.

Also, several browsers will show a dialog to users claiming a "script is running slowly" even when the operation would complete in a reasonable amount of time. For example, IE9+ will do so in some circumstances when the code would have completed in less than 0.2 seconds, and Firefox also has similar cases (but not quite so absurd).

[jarekevi]

Solution and conclusions

Developing on FireFox 25( depployed used on XulRunner wit Firefox enigne) and from expirience the grid can't support such an amount of data. Selecting All messages or expanding an component is realy slow. Sometimes unusable slow.
The DB fetches occur only in specific situations and repeat every 10-20 sec for 1-5 min so it shouldn't be that bad.

Some observations:
setting fetchmode PAGING and number of items of pages won't work until you also call the setAutoFetchData(true) method (ke pasa?). Also after calling next fetch paging turns off !?!
Solved my problem with:

DataSource dataSource = getManager().getMessageGrid().getDataSource();	
		dataSource.setAllowAdvancedCriteria(true);
		DSRequest request = new DSRequest();
		Integer[] visibleRows = getManager().getMessageGrid().getVisibleRows();	
		Integer endRow = 300;
		if(getManager().getMessageGrid().getResultSet() != null)
		 endRow = (visibleRows[1] + getManager().getMessageGrid().getResultSet().getResultSize());
		Integer startRow = endRow-150;
		startRow = Math.max(0, startRow);
		request.setStartRow(startRow);
		request.setEndRow(endRow);		
		request.setSortBy(getManager().getMessageGrid().getSort());
		dataSource.fetchData(criterion, new DSCallback() {
			@Override
			public void execute(DSResponse response, Object rawData, DSRequest request) {				
								
				DataSource dataSource = getManager().getMessageGrid().getDataSource();
				final ResultSet resultset = new ResultSet();
				resultset.setDataSource(dataSource);
				resultset.setCriteria(getManager().getMessageGrid().getCriteria());
				resultset.setInitialLength(response.getTotalRows());				
				resultset.setInitialData(response.getData());
				resultset.setInitialSort(getManager().getMessageGrid().getSort());
				getManager().getMessageGrid().setData(resultset);
				getManager().getMessageGrid().filterByEditor();
		    }
		 
		}, request);

but sadly i cant use the grid summary this way, I'm overriding the summary of an field and it would be nice to be able to decide when to show the data in a summary.

Looking at the upper code is ther a way to additionaly optimize the fetch?

[Isomorphic]

Some observations:
setting fetchmode PAGING and number of items of pages won't work until you also call the setAutoFetchData(true) method (ke pasa?). Also after calling next fetch paging turns off !?!

We're having trouble understanding what this means, but please review the docs for ListGrid.fetchData(): it automatically constructs a ResultSet, using settings on the ListGrid (such as dataPageSize and dataProperties). If you manually construct a ResultSet, these settings are not applied. If you manually construct a ResultSet and then subsequently call fetchData(), your manually constructed ResultSet is replaced with an automatically created one (per docs) and the settings *would* then apply.

but sadly i cant use the grid summary this way, I'm overriding the summary of an field and it would be nice to be able to decide when to show the data in a summary.

What type of summary are you talking about? There are both per-group and whole-grid summaries. For whole-grid summaries, if you want to show a summary when only partial data has been loaded, you can use summaryRowDataSource (see the docs for this).

[jarekevi]

but wouldn't resultset.setFetchMode(FetchMode.PAGED); leave the paging on ?

[Isomorphic]

?

We described in detail above the circumstances in which this setting applies. Please reread, and be sure to look at the mentioned docs as well.

[jarekevi]

I'm facing a problem with my new approach and filter editor and sort. I'm getting the right date and amount but after the filtering and sorting i'm loosing the criteria set in during the fetch.

final AdvancedCriteria criterion = new AdvancedCriteria(OperatorId.AND);
		criterion.addCriteria(new Criteria(MessageInterface._querryID, ""+getManager().getParams().getQueryID()));		
		DataSource dataSource = getManager().getMessageGrid().getDataSource();	
		dataSource.setAllowAdvancedCriteria(true);
		DSRequest request = new DSRequest();
		Integer[] visibleRows = getManager().getMessageGrid().getVisibleRows();	
		Integer endRow = 300;
		if(getManager().getMessageGrid().getResultSet() != null)
		 endRow = (visibleRows[1] + getManager().getMessageGrid().getResultSet().getResultSize());
		Integer startRow = endRow-150;
		startRow = Math.max(0, startRow);
		request.setStartRow(startRow);
		request.setEndRow(endRow);	
		request.setCriteria(criterion);
		request.setSortBy(getManager().getMessageGrid().getSort());
		getManager().getPresenter().setActualCrit(criterion);
		dataSource.fetchData(criterion, new DSCallback() {
			@Override
			public void execute(DSResponse response, Object rawData, DSRequest request) {				
				getManager().getMessageGrid().setShowFilterEditor(true);
				getManager().getPresenter().getView().getMassReviewUpperBar().getAdvancedFilter().setDisabled(false);
				getManager().updateMessagesNumber();
				DataSource dataSource = getManager().getMessageGrid().getDataSource();
				final ResultSet resultset = new ResultSet();
				resultset.setDataSource(dataSource);
				resultset.setCriteria(getManager().getMessageGrid().getCriteria());
				resultset.setInitialLength(response.getTotalRows());				
				resultset.setInitialData(response.getData());
				resultset.setInitialSort(getManager().getMessageGrid().getSort());
				getManager().getMessageGrid().setData(resultset);
		    }
		 
		}, request);

only thing set on Listgrid

setAllowFilterExpressions(true);

[jarekevi]

I get that i overide my Listgrid settings with the new result, but i need to. I was just wondering if it is possible to configure the resultset to get the paging back.

[jarekevi]

Im fetching the data and geting

'PostgreSQL': SELECT query_infos.active_to_date, query_infos.case_id, query_infos.creator_id, query_infos.creator_name, query_infos.execution_time, query_infos.folder_id, query_infos.id, query_infos.name, query_infos.number_of_result, query_infos.query_params, query_infos.query_params_id, query_infos.query_params_version, query_infos.query_status, query_infos.query_type, query_infos.status, query_infos.sys_creation_date FROM dev.query_infos WHERE (query_infos.id=33480)

but on filter or sort i get

=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Host:127.0.0.1:8888
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: User-Agent:Mozilla/5.0 (Windows NT 6.3; WOW64; rv:24.0) Gecko/20100101 Firefox/24.0
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Accept-Language:pl,en-us;q=0.7,en;q=0.3
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Accept-Encoding:gzip, deflate
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Referer:http://127.0.0.1:8888/Com_evizone_rbp_UI.html?gwt.codesvr=127.0.0.1:9997
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Cookie:JSESSIONID=pkl6o95wcmly; non-via-session=Yes
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Connection:keep-alive
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Content-Type:application/x-www-form-urlencoded; charset=UTF-8
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Content-Length:972
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Pragma:no-cache
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - Header Name:Value pair: Cache-Control:no-cache
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - session exists: pkl6o95wcmly
=== 2013-12-20 17:54:57,900 [l0-7] DEBUG IDACall - remote user: null
=== 2013-12-20 17:54:57,901 [l0-7] INFO  RequestContext - URL: '/com_evizone_rbp_ui/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows NT 6.3; WOW64; rv:24.0) Gecko/20100101 Firefox/24.0': Moz (Gecko) with Accept-Encoding header
=== 2013-12-20 17:54:57,901 [l0-7] WARN  RPCManager - client/server version mismatch.  Client is version: v9.0p_2013-12-11, server is version: v9.0p_2013-11-26 - mixing different client/server versions is generally not supported.  If you've installed a more recent client version, try clearing the browser cache and reloading the page.
=== 2013-12-20 17:54:57,916 [l0-7] DEBUG XML - Parsed XML from (in memory stream): 14ms
=== 2013-12-20 17:54:57,921 [l0-7] DEBUG RPCManager - Processing 1 requests.
=== 2013-12-20 17:54:57,921 [l0-7] DEBUG DSRequest - Caching instance 11 of DS messages from DSRequest.getDataSource()
=== 2013-12-20 17:54:57,921 [l0-7] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"messages",
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_MessageGrid_2",
    appID:"builtinApplication",
    operation:"messages_fetch",
    oldValues:null
}
=== 2013-12-20 17:54:57,921 [l0-7] INFO  IDACall - Performing 1 operation(s)
=== 2013-12-20 17:54:57,922 [l0-7] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2013-12-20 17:54:57,922 [l0-7] DEBUG DeclarativeSecurity - DataSource messages is not in the pre-checked list, processing...
=== 2013-12-20 17:54:57,922 [l0-7] DEBUG AppBase - [builtinApplication.messages_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2013-12-20 17:54:57,922 [l0-7] DEBUG AppBase - [builtinApplication.messages_fetch] No public zero-argument method named '_messages_fetch' found, performing generic datasource operation
=== 2013-12-20 17:54:57,922 [l0-7] INFO  SQLDataSource - [builtinApplication.messages_fetch] Performing fetch operation with
	criteria: {}	values: {}
=== 2013-12-20 17:54:57,923 [l0-7] INFO  SQLWhereClause - [builtinApplication.messages_fetch] empty condition
=== 2013-12-20 17:54:57,923 [l0-7] INFO  SQLDataSource - [builtinApplication.messages_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2013-12-20 17:54:57,924 [l0-7] DEBUG SQLDataSource - [builtinApplication.messages_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2013-12-20 17:54:57,924 [l0-7] DEBUG SQLDataSource - [builtinApplication.messages_fetch] Eval'd row count query: SELECT COUNT(*) FROM dev.query_message_persist_results WHERE ('1'='1')
=== 2013-12-20 17:54:57,924 [l0-7] DEBUG SQLConnectionManager - [builtinApplication.messages_fetch] Borrowed connection '1561615899'
=== 2013-12-20 17:54:57,924 [l0-7] DEBUG SQLDriver - [builtinApplication.messages_fetch] About to execute SQL query in 'PostgreSQL' using connection '1561615899'
=== 2013-12-20 17:54:57,924 [l0-7] INFO  SQLDriver - [builtinApplication.messages_fetch] Executing SQL query on 'PostgreSQL': SELECT COUNT(*) FROM dev.query_message_persist_results WHERE ('1'='1')
Success pinging server and keeping session.
=== 2013-12-20 17:55:00,494 [l0-7] INFO  SQLDataSource - [builtinApplication.messages_fetch] Row count query found 4679043 rows, which exceeds the DataSource's progressiveLoadingThreshold of 200000.  Switching to progressive loading mode.
=== 2013-12-20 17:55:00,494 [l0-7] DEBUG SQLDataSource - [builtinApplication.messages_fetch] Using SQL Limit query
=== 2013-12-20 17:55:00,494 [l0-7] DEBUG SQLDataSource - [builtinApplication.messages_fetch] SQL windowed select rows 0->75, result size 76. Query: SELECT query_message_persist_results.annotated, query_message_persist_results.attachments_hadoop_files_ids, query_message_persist_results.bcc, query_message_persist_results.cc, query_message_persist_results.employee_id, query_message_persist_results.extern_type, query_message_persist_results.hadoop_file_id, query_message_persist_results.id, query_message_persist_results.import_date, query_message_persist_results.privilege_status, query_message_persist_results.query_info_id, query_message_persist_results.recipient, query_message_persist_results.review_status, query_message_persist_results.review_update_date, query_message_persist_results.risk_level, query_message_persist_results.sender, query_message_persist_results.sent_date, query_message_persist_results.source, query_message_persist_results.subject, query_message_persist_results.sys_creation_date, query_message_persist_results.technology, query_message_persist_results.with_attachment FROM dev.query_message_persist_results WHERE ('1'='1') OFFSET 0 LIMIT 76
=== 2013-12-20 17:55:00,670 [l0-7] INFO  DSResponse - [builtinApplication.messages_fetch] DSResponse: List with 76 items
=== 2013-12-20 17:55:00,670 [l0-7] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2013-12-20 17:55:00,670 [l0-7] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2013-12-20 17:55:00,692 [l0-7] DEBUG SQLDriver - Freeing SQLDriver dbConnection 1561615899
=== 2013-12-20 17:55:00,693 [l0-7] DEBUG SQLConnectionManager - About to close PoolableConnection with hashcode "1561615899"

[Isomorphic]

This call:

resultset.setFetchMode(FetchMode.PAGED);

.. is a no-op. "paged" is the default. If you provide initialData and initialLength settings that indicate a partially loaded dataset (see docs for resultSet.initialData), subsequent accesses of data in the unloaded portion will trigger fetches.

About this call that doesn't use paging: because we've only got partial code and partial information, we can't help with where this "first call" is coming form. However, if you look in the RPC tab of the Developer Console, it tells you what component initiates each call, so it's easy to troubleshoot from there.

[jarekevi]

It seems that mixing different kind of criterion (criteria,criterion,AC) causes the problem. odd thing.

[Isomorphic]

Let us know if you have some code we can run that seems to show a bug.

[jarekevi]

private void getActualNumberOfMessages(Criteria criteria){
		if(!getView().getExportBar().isQueryFinished())
			return;		
		
		DataSource ds =  DataSource.getDataSource("messagesCount"); 		
		DSRequest request= new DSRequest();
		request.setStartRow(0);
		request.setEndRow(1);
		DSCallback callback= new DSCallback() {
		         @Override
		         public void execute(DSResponse aResponse,  Object aRawData, DSRequest aRequest)
		         {
		           getView().getExportBar().setActualNumOfMessages(aResponse.getTotalRows());
		           getView().getExportBar().setNumberOfMessages();		            
		         }
		      };
		ds.fetchData(criteria, callback, request);
	}

works fine but using

private void getActualNumberOfMessages(Criteria criteria){
		if(!getView().getExportBar().isQueryFinished())
			return;
		System.out.println(criteria.getValues());
		AdvancedCriteria ac = new AdvancedCriteria(OperatorId.AND);		
		ac.addCriteria(criteria);
		ac.addCriteria(getActualCrit());
		DataSource ds =  DataSource.getDataSource("messagesCount"); 		
		DSRequest request= new DSRequest();
		request.setStartRow(0);
		request.setEndRow(1);
		DSCallback callback= new DSCallback() {
		         @Override
		         public void execute(DSResponse aResponse,  Object aRawData, DSRequest aRequest)
		         {
		           getView().getExportBar().setActualNumOfMessages(aResponse.getTotalRows());
		           getView().getExportBar().setNumberOfMessages();		            
		         }
		      };
		ds.fetchData(ac, callback, request);
	}

where getActualCriteria() returns the basic criteria used during datafetch (doesn't actually matter what's there), after using the asAdvancedCriteria() any way, all my grids start to fetch with criteria where("1" == "1").
That is an quick example, but using a wrong combination of criteria,criterion,AC causes the grid to lose the LiveGrid ability or what ever and starts to fetch all data with where("1"="1")