Slow Page Loading

Use this forum to suggest new features, language support, documentation, etc. for OpenEMM's roadmap

Moderator: moderator

sparkiss
Posts: 1
Joined: Wed Feb 22, 2012 8:12 pm

Slow Page Loading

Post 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.
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Slow Page Loading

Post 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.
OpenEMM Maintainer
unhuman
Posts: 46
Joined: Mon Jan 23, 2012 10:29 pm

Re: Slow Page Loading

Post by unhuman »

From the last time I looked, as well, the database could use some indexes.
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Slow Page Loading

Post by maschoff »

See install guide, section 6.8, and feel free to make some recommendations in this forum!
OpenEMM Maintainer
Post Reply