开发者

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.

LinkedHash开发者_运维问答Map 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


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜