0

I have set my fetch size as below still its taking long time to fetch records for Oracle DB.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
         jdbcTemplate.setFetchSize(2000);
         npJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

return this.npJdbcTemplate.query(
            GenericQueryCache.getQuery("GET_GSD_MAPPING"), selectParameters,
            new RowMapper<GdsMapping>() {
                @Override
                public GdsMapping mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return GdsMapping.builder()
                            .Code(rs.getString("CODE_EXCH"))
                            .CodeToIdentifyMarket(rs.getString("CODE_FOR_MARKET"))
                            .Name(rs.getString("NAME"))
                            .FuturesCode(rs.getString("CODE_FC"))
                            .Type(rs.getString("TYPE"))
                            .build();

                }
            });

sql query : select * from GDS_MAPPING

explain plan : Plan hash value: 4000448272

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 44626 | 1350K| 102 (0)| 00:00:02 |

| 1 | TABLE ACCESS FULL| GDS_MAPPING | 44626 | 1350K| 102 (0)| 00:00:02 |

pooja jha
  • 5
  • 4
  • 1
    How did you arrive at this conclusion? – Henry Feb 25 '19 at 09:10
  • there are 44k records in db.. when i start my application..it takes time to load this table data.. other table's fetchsize are working fine. but for this fetch i get slowness issue..i printed count ++ log in return code above..i can see for this its fetching one by one.. for others fetchsize is quite fast. Dont know why – pooja jha Feb 25 '19 at 09:34
  • 1
    You'll have to post the SQL of your query and preferable the [execution plan](https://stackoverflow.com/a/34975420/4808122) of it. Some queries can't use (version dependent) fetch size, e.g. when containing CLOB columns, other queries are *slow even with large fetch size*. – Marmite Bomber Feb 25 '19 at 11:35
  • please find the sql and explain plan in question – pooja jha Feb 26 '19 at 09:51

0 Answers0