Long-running query against customer table

Use this forum to report bugs and to check for bugfixes and new releases of OpenEMM

Moderator: moderator

mopoke
Posts: 1
Joined: Wed Aug 03, 2011 4:35 am

Long-running query against customer table

Post by mopoke » Wed Aug 03, 2011 4:43 am

From time to time we'll notice a long-running query on our database server which looks like this:

Code: Select all

SELECT count(*) FROM ( select cust.customer_id, cust.gender, cust.firstname, cust.lastname, cust.email FROM customer_1_tbl cust ) agn
Typically this will take some minutes to complete (there's around 600,000 customers in our database).

However, if the query is written as

Code: Select all

SELECT count(*) FROM customer_1_tbl cust
then it completes in a fraction of a second.

As far as I can tell, the count(*) query comes from getRecipientList in RecipientDaoImpl.java - it wraps around an SQL statement generated by getSQLStatement in RecipientQueryBuilder.java.

I realise that getSQLStatement could return a query with a join table or, indeed, a query with a where clause, but for this simple case is it possible to rewrite the query so that it doesn't do a full table scan on the customer table just to get a count?

maschoff
Site Admin
Posts: 2188
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Long-running query against customer table

Post by maschoff » Wed Aug 03, 2011 8:24 am

This statement is used in class RecipientQueryBuilder, method getSQLStatement, line 160. Method getSQLStatement is used in class RecipientAction to fetch the requested data set for the recipient list. But it is also used to get the count of this selection to display the total number of records on top of the recipient list. For the latter job the query could be replaced by your faster suggestion.

Since the recipient list will be redesigned for OpenEMM 2011 R2 anyway, we will make sure your suggestion is included.
OpenEMM Maintainer

Post Reply