I have a simple spring standalone application where I creating my own datasource using apache basic datasource class. Data source is then passed to Named parameter jdbc template.
The first query is always taking more than 12 secs to execute - then the second query is fine. (I believe this is happening because it establishes connection for the first time). But this is hampering performance of the application as it is supposed to be running for every 5 mins but one job itself is taking more than the usual time. Can someone please tell me what can be done to improve the performance. Here is the code:
--- Datasource creation and template object creation --------
@Configuration
@PropertySource(value = { "file:///opt/dsp/share/Batchjobs/GRComponent/conf/application.properties" })
public class DataSourceConfig {
private static Log log = LogFactory.getLog(DataSourceConfig.class);
@Autowired
private Environment env;
@Bean
public DataSource dataSource() {
long start = System.currentTimeMillis();
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(env.getRequiredProperty("jdbc.driverClassName"));
dataSource.setUrl(env.getRequiredProperty("jdbc.url"));
dataSource.setUsername(env.getRequiredProperty("jdbc.username"));
dataSource.setPassword(env.getRequiredProperty("jdbc.password"));
/*DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getRequiredProperty("jdbc.driverClassName"));
dataSource.setUrl(env.getRequiredProperty("jdbc.url"));
dataSource.setUsername(env.getRequiredProperty("jdbc.username"));
dataSource.setPassword(env.getRequiredProperty("jdbc.password"));*/
long end = System.currentTimeMillis();
if(log.isDebugEnabled())
log.debug("Total time for creating datasource: " +(end - start));
return dataSource;
}
@Bean
public NamedParameterJdbcTemplate namedJdbcTemplate(DataSource dataSource) {
long start = System.currentTimeMillis();
NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
long end = System.currentTimeMillis();
log.info("Total time for NamedParameterJdbcTemplate: " +(end - start));
return jdbcTemplate;
}
-----First query call
SiteDetails sd = null;
try{
String fetchSiteDtls = "SELECT SITE_STATUS_ID, SITE_ENABLED, ITERATION_ID, UPDATED_DATE "+
"FROM DSP_SITE_DETAILS WHERE SITE_ID =:siteid";
MapSqlParameterSource parameterSource = new MapSqlParameterSource();
parameterSource.addValue("siteid", siteId);
if(log.isDebugEnabled())
log.debug("fetchSiteDetails:query:" +fetchSiteDtls);
long start = System.currentTimeMillis();
sd = namedParJdbcTempl.queryForObject(fetchSiteDtls, parameterSource, (rs, rowNum)->{
SiteDetails siteDetails = new SiteDetails();
siteDetails.setSiteStatus(rs.getInt("SITE_STATUS_ID"));
siteDetails.setSiteEnabled(rs.getString("SITE_ENABLED"));
siteDetails.setIterationId(rs.getInt("ITERATION_ID"));
siteDetails.setStatusUpdateTimeStamp(rs.getTimestamp("UPDATED_DATE"));
return siteDetails;});
sd.setSiteId(siteId);
long end = System.currentTimeMillis();
if(log.isDebugEnabled())
log.debug("Total time for executing query: " +(end - start));
return sd;
Question updated to state: Oracle is the database and query is already ran in sql client to run less than 1 sec. Even tried different sql. Tried different ways of connecting to datasource like spring datasource, apache basic datasource, HikerCP datasource, finally removed datasource itself and tried with normal jdbc connection with driver manager. I added execution time for every step and got that the connection object is taking 12 - 20 secs to connect.
Can someone from oracle community help with a suggestion as can there be any problem with the oracle setup or something else?
