Page 1 of 1

Slow Page Loading

Posted: Wed Feb 22, 2012 8:23 pm
by sparkiss
Hi, I have 10,000,000 of recipients and it took forever when you browse the list.
The problem is poorly designed sub-query like:
SELECT COUNT(*) FROM
( SELECT .... <-- ORIGINAL QUERY
) AGN

So what I did is the next:

ReceipentDaoImpl.java

Code: Select all



	public PaginatedList getRecipientList( String sqlStatementForCount , String sqlStatementForRows, String sort, String direction , int page, int rownums, int previousFullListSize ) throws IllegalAccessException, InstantiationException {

        // TODO use RecipientQueryBuilder inside DAO
    	JdbcTemplate aTemplate = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
    	
			int idx = sqlStatementForCount.toUpperCase().indexOf("FROM");
    	String subStr = sqlStatementForCount.substring(idx);
    	String queryStr = "SELECT count(*) " + subStr;
    	int totalRows = aTemplate.queryForInt(queryStr);
    	
    	
    	//int totalRows = aTemplate.queryForInt("SELECT count(*) FROM ( " + sqlStatementForCount + " ) agn" );
     	if( previousFullListSize == 0 || previousFullListSize != totalRows ) {
     		page = 1;
     	}

I wish this helps people who has the same problem.

Re: Slow Page Loading

Posted: Fri Feb 24, 2012 8:25 pm
by maschoff
Thank you for your code contribution! To be sure what you have changed exactly, it would be helpful if you would post your code as patch in unified diff format.

FYI: We have changed the code in OpenEMM 2012 to limit the number of recipients to be shown in the recipient list - which limits execution time as well.

Re: Slow Page Loading

Posted: Thu Apr 05, 2012 7:36 pm
by unhuman
From the last time I looked, as well, the database could use some indexes.

Re: Slow Page Loading

Posted: Thu Apr 05, 2012 7:50 pm
by maschoff
See install guide, section 6.8, and feel free to make some recommendations in this forum!