从结果集中获取数据太慢

Question

使用结果集从PostgreSQL数据库获取数据太慢。

这是我的密码。

for (int i = 0; i < qry_list.size(); i++) {
    try {
        resultSet = statement.executeQuery(qry_list.get(i));
        resultSet.setFetchSize(0);
        while (resultSet.next()) {
            totalfilecrated = totalfilecrated
                    + resultSet.getInt(columnname);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

在这里，我尝试在for循环中获取数据。它好吗？

这是我的疑问。

用于获取单个组织的ID（org_unit_id）。

"select org_unit_id from emd.emd_org_unit where org_unit_id 
in(select org_unit_id from emd.emd_org_unit_detail where entity_type_id=1 and is_active=true) and 
is_active=true order by org_unit_name_en";

然后我想得到每个org_unit_id 的文件数

select count(*) as totalfilecreatedelectronic from fl_file ff
left join vw_employee_details_with_department epd on epd.post_detail_id=ff.file_opened_by_post_fk
where ff.file_nature = 'E' and ((ff.migration_date>='2011-01-01' and ff.migration_date<='2015-01-01') or 
(ff.opening_date >='2011-01-01' and ff.opening_date <='2015-01-01')) and 
epd.departmentid=org_unit_id";

Answer 1

看到您的第二个查询已经包含对org_unit_id列的引用，您可能会考虑直接加入emd_org_unit表：

select org.org_unit_id, count(*) as totalfilecreatedelectronic 
  from fl_file ff
  left join vw_employee_details_with_department epd on epd.post_detail_id=ff.file_opened_by_post_fk
  -- join to active entries in emd_org_unit
  inner join from emd.emd_org_unit org ON epd.departmentid=org.org_unit_id  
         AND org.is_active=true
   where ff.file_nature = 'E' 
        and (
  (ff.migration_date>='2011-01-01' and ff.migration_date<='2015-01-01') or 
  (ff.opening_date >='2011-01-01' and ff.opening_date <='2015-01-01')) 
 -- and now group by org_unit_id to get the counts
 group by org_unit_id

如果你为此创建一个SQLFiddle，我想事情会变得更清楚。