I have a spring batch application to load 5M records from a file into SQL Server database. I've different datasources differentiated by country code. When I use a single data source with @Primary annotation, the spring batch writer writes the 5M records in 5 minutes. But when I give multiple datasources with @Bean annotation and use a datasource which is not primary to write the file data into Database the perforamnce becomes very slow and it takes around 15minutes for the same 5M records. Can anyone explain if Spring Batch behaves differently with primary datasource and other datasources.
@Repository
@ComponentScan(basePackages = "com.abc.extract")
@EnableBatchProcessing
@EnableTransactionManagement
@Slf4j
public class DataSourceConfig {
public DataSourceConfig() {
}
@Autowired
private CCMConfiguration ccmConfig;
@Autowired
SpotlightConfig spotlight;
private DataSource datasourcesVal = null;
private DataSource datasourcesVal1 = null;
private Map<String,DataSource> datasourcesMap = null;
@Value("${app.country.code}")
String countryCode;
@Primary
@Bean("batchprimary")
public HikariDataSource hikariDataSource(@Value("${app.country.code}")
String countryCode1) throws IllegalBlockSizeException, NoSuchPaddingException, BadPaddingException,
NoSuchAlgorithmException, InvalidKeyException {
System.out.println("Contrycode:"+countryCode1);
SqlServerConfiguration dbConfiguration = ccmConfig.getSqlServerDatabaseConfig(countryCode1);
HikariDataSource ds = null;
try {
final String password = dbConfiguration.getDataBasePwd();
final String username = dbConfiguration.getDataBaseUserName();
ds = (HikariDataSource) DataSourceBuilder.create().type(HikariDataSource.class).build();
ds.setUsername(username);
ds.setPassword(password);
ds.setDriverClassName(dbConfiguration.getDataBaseDriver());
ds.setJdbcUrl(dbConfiguration.getDataBaseUrl());
ds.setConnectionTestQuery("SELECT 1");
ds.setConnectionTimeout(dbConfiguration.getConnectionTimeout());
ds.setIdleTimeout(dbConfiguration.getIdleTimeout());
ds.setMaximumPoolSize(dbConfiguration.getHikariPoolSize());
//ds.setMaximumPoolSize(2);
ds.setMaxLifetime(dbConfiguration.getMaxLifetime());
ds.setLeakDetectionThreshold(dbConfiguration.getLeakDetectionThreshold());
ds.setPoolName(dbConfiguration.getPoolName());
this.datasourcesVal = ds;
} catch (Exception ex) {
spotlight.sendNotification(ex, "Critical: Failed to establish Database connection");
log.error(ex.getMessage());
} finally {
if (!Objects.nonNull(ds) || ds.isClosed()) {
ds.close();
spotlight.sendNotification(new NullPointerException("Primary data source creation failed"),
"Critical: Failed to establish Database connection");
}
}
return ds;
}
@Bean("batchprimary1")
public HikariDataSource hikariDataSource1(@Value("${app.country.code}")
String countryCode1) throws IllegalBlockSizeException, NoSuchPaddingException, BadPaddingException,
NoSuchAlgorithmException, InvalidKeyException {
System.out.pri
ntln("Contrycode:"+countryCode1);
SqlServerConfiguration dbConfiguration = ccmConfig.getSqlServerDatabaseConfig(countryCode1);
HikariDataSource ds = null;
try {
final String password = dbConfiguration.getDataBasePwd();
final String username = dbConfiguration.getDataBaseUserName();
ds = (HikariDataSource) DataSourceBuilder.create().type(HikariDataSource.class).build();
ds.setUsername(username);
ds.setPassword(password);
ds.setDriverClassName(dbConfiguration.getDataBaseDriver());
ds.setJdbcUrl(dbConfiguration.getDataBaseUrl());
ds.setConnectionTestQuery("SELECT 1");
ds.setConnectionTimeout(dbConfiguration.getConnectionTimeout());
ds.setIdleTimeout(dbConfiguration.getIdleTimeout());
ds.setMaximumPoolSize(dbConfiguration.getHikariPoolSize());
//ds.setMaximumPoolSize(2);
ds.setMaxLifetime(dbConfiguration.getMaxLifetime());
ds.setLeakDetectionThreshold(dbConfiguration.getLeakDetectionThreshold());
ds.setPoolName(dbConfiguration.getPoolName());
//this.datasourcesVal = ds;
} catch (Exception ex) {
spotlight.sendNotification(ex, "Critical: Failed to establish Database connection");
log.error(ex.getMessage());
} finally {
if (!Objects.nonNull(ds) || ds.isClosed()) {
ds.close();
spotlight.sendNotification(new NullPointerException("Primary data source creation failed"),
"Critical: Failed to establish Database connection");
}
}
return ds;
}
@Autowired
@Qualifier("batchprimary")
public DataSource datasourcesVal;
@Autowired
@Qualifier("batchprimary1")
public DataSource datasourcesVal1;
@Bean
@JobScope
public Step ExtractNLoadItemOnHand(TaskExecutor taskExecutor,@Value("#{jobParameters}") Map<String, JobParameter> jobParameters)
throws InvalidKeyException, IllegalBlockSizeException,
NoSuchPaddingException, BadPaddingException,
NoSuchAlgorithmException, SQLException, ValidationException,
CalpiDataException {
int chunkSize = ApplicationConstants.CHUNK_SIZE;
log.info(" Extract and Load ItemOnHand Job - Started....");
countryCode = jobParameters.get("CountryCode").toString();
//dataSourceTemp = datasourcesMap.get(countryCode);
return stepBuilderFactory
.get("READ ITEM ONHAND STEP")
.listener(stepListners)
.<AccumOnhand, AccumOnhand> chunk(chunkSize)
.reader(extractItemOnHand(null))
.processor(new ItemProcessor<AccumOnhand, AccumOnhand>() {
@Override
public AccumOnhand process(AccumOnhand accumOnhand)
throws Exception {
return accumOnhand;
}
})
.writer(compositeItemWriter.compositeItemWriter(Arrays
.asList(loadItemOnHand()))).faultTolerant()
.retryLimit(ApplicationConstants.RETRY_SKIP_LIMIT)
.retry(Exception.class).skip(Exception.class)
.skipLimit(ApplicationConstants.RETRY_SKIP_LIMIT)
.taskExecutor(taskExecutor)
.throttleLimit(ApplicationConstants.THROTTLE_LIMIT).build();
}
@Bean
@StepScope
public JdbcBatchItemWriter<AccumOnhand> loadItemOnHand()
throws InvalidKeyException, IllegalBlockSizeException,
NoSuchPaddingException, BadPaddingException,
NoSuchAlgorithmException, ValidationException, CalpiDataException {
try {
//country = jobParameters.get("CountryCode");
JdbcBatchItemWriter<AccumOnhand> writer;
//datasource = dataSourceConfig.hikariDataSource(country.toString());
if(countryCode.equals("MX")) {
writer = stepDBItemWriter.writeDBData(
MessageFormat.format(SQLConstants.INS_ACCUM_ONHAND,
countryCode), datasourcesVal);
} else {
writer = stepDBItemWriter.writeDBData(
MessageFormat.format(SQLConstants.INS_ACCUM_ONHAND,
countryCode), datasourcesVal1);
}
log.info("Item on hand writer created successfully");
return writer;
} catch (Exception ex) {
log.error("Load loadItemOnHand Failed {} ", ex);
throw new ValidationException("Load loadItemOnHand Failed", ex);
}
}
