CODEWITHSUNDEEP
javaopenjpa
Jason
Jason

Reputation: 4130

Slow Performing OpenJPA Query

I have the following query method that has slow performance:

@Override
public Map<String, Long> getFeatureCounts() {

    StopWatch timer = new StopWatch();
    timer.start();
    Map<String, Long> resultMap = new LinkedHashMap<String, Long>();  // I want to ensure that the features are in alpha order

    EntityManager em = entityManagerFactory.createEntityManager();

    String jpaQuery = "SELECT F.featureClass, COUNT(DISTINCT F.id) FROM Feature F GROUP BY F.featureClass ORDER BY F.featureClass";
    Query query = em.createQuery(jpaQuery);

    List resultList = query.getResultList();

    for (Object o : resultList) {
        Object[] o2 = (Object[]) o;
        String key = (String) o2[0];
        Long value = (Long) o2[1];
        resultMap.put(key, value);
    }

    em.close();

    timer.stop();
    log.info("getFeatureCounts() executed in " + timer.getTime() + "ms.");

    return resultMap;
}

The table has about 1.3M records, but there is an index on featureClass, and F.id is the primary key. I'm waiting results back in something like the following format:

Airport->20316 Bridge->6509 etc.

LinkedHashMap is a requirement so I can maintain the order of the keys (it's either that or call Collections.sort() later... kind of a pay now or pay later thing).

Table definition is below:

CREATE TABLE FEATURE( FEATUREID INTEGER, FEATURENAME VARCHAR(100), FEATURECLASS VARCHAR(100), LAT NUMERIC(14,5), LNG NUMERIC(14,5), FEATURESOURCE VARCHAR(10), PRIMARY KEY(FEATUREID) ) ENGINE = InnoDB;

create index idx_featureclass on FEATURE(FEATURECLASS);

Any help is appreciated.

Jason

Upvotes: 1

Views: 453

Answers (1)

Kees de Kooter
Kees de Kooter

Reputation: 7195

The COUNT(DISTINCT F.id) seems unnecessary. A COUNT(1) would give you the same result. And probably better performance ;-).

Upvotes: 2

Related Questions