CODEWITHSUNDEEP
javapostgresqljdbcresultset
Akhil Jayakumar
Akhil Jayakumar

Reputation: 2312

getting data from result set is too slow

Fetching data from PostgreSQL database with Result Set is too slow.

Here is my code.

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();
    }
}

Here I try to fetch data inside a for loop.is it good?

Here is my query.

For getting ID of individual Organisations(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";

Then i want to get the count of files with each 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";

Upvotes: 0

Views: 577

Answers (1)

Jan
Jan

Reputation: 13858

Seeing how your second query already contains a reference to a column that's an org_unit_id, you might think joining emd_org_unit table in directly:

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

If you'd create a SQLFiddle for this, things would get much clearer I guess.

Upvotes: 3

Related Questions