JDBC Template Works Slow

Question

I am having performance problem while using JDBCTemplate to fetch a row from my database. When I run sql in plsql, I can get the result in 3 milliseconds but same query in the code works around 200 ms. I think that it works slow because before running query, a connection created and I lost too many time in it. I guess I need a connection pool or smt. else

Before writing code here, I want to talk about my spring boot project's flow. Client calls my endpoint and in this call, I am using multiple queries from multiple tables. All queries runs slow because for each query, another connection creates.

Database Config Class

@Configuration
public class DatabaseConfig {

    @Autowired
    private Environment env;

    @Bean(name = "fraudDb")
    public DataSource masterDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("driver-class-name"));
        dataSource.setUrl(env.getProperty("fraud.url"));
        dataSource.setUsername(env.getProperty("fraud.username"));
        dataSource.setPassword(env.getProperty("fraud.password"));

        return dataSource;
    }

    @Bean(name = "ndvliveDb")
    public DataSource secondDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("driver-class-name"));
        dataSource.setUrl(env.getProperty("ndvlive.url"));
        dataSource.setUsername(env.getProperty("ndvlive.username"));
        dataSource.setPassword(env.getProperty("ndvlive.password"));

        return dataSource;
    }

    @Bean(name = "fraudJdbcTemplate")
    @Autowired
    public JdbcTemplate masterJdbcTemplate(@Qualifier("fraudDb") DataSource fraudDb) {
        return new JdbcTemplate(fraudDb);
    }

    @Bean(name = "ndvliveJdbcTemplate")
    @Autowired
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("ndvliveDb") DataSource ndvliveDb) {
        return new JdbcTemplate(ndvliveDb);
    }
}

Queries In RestController

private RbtranServiceInputModel services(FraudActionsRestRequest fraudActionsRestRequest) {
Long start = System.nanoTime();
        String debitSegmentId = ndvliveCustomerInfoService.getSBUCode(Integer.parseInt(cifNoSender));
        Long end = System.nanoTime();
        System.out.println("debitSegmentId " + (end - start) / 1e6);

        //10 ms - 20 ms
        start = System.nanoTime();
        String debitName = ndvliveCustomerInfoService.getNameSurname(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("debitName " + (end - start) / 1e6);

        //3 ms
        start = System.nanoTime();
        ResponseGetBMSInfo bmsInfo = ndvliveCustomerInfoService.getOnlineCustomerInfo(new BigDecimal(cifNoSender));
        end = System.nanoTime();
        System.out.println("bmsInfo " + (end - start) / 1e6);

        //10 MS
        start = System.nanoTime();
        Date passwordChangeDate = ndvliveCustomerInfoService.getPasswordChangeDate(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("passwordChangeDate " + (end - start) / 1e6);

        //10ms
        start = System.nanoTime();
        Date smartSmsGsmNoRegistrationDate = ndvliveCustomerInfoService.getSmartSmsGsmNoRegistrationDate(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("smartSmsGsmNoRegistrationDate " + (end - start) / 1e6);

        //6 ms
        start = System.nanoTime();
        Date membershipDate = ndvliveCustomerInfoService.getMembershipDate(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("membershipDate " + (end - start) / 1e6);

        start = System.nanoTime();
        BigDecimal smartSmsNo = ndvliveCustomerInfoService.getSmsGsmNo(new BigDecimal(cifNoSender));//TODO sms;
        end = System.nanoTime();
        System.out.println("smartSmsNo " + (end - start) / 1e6);

        start = System.nanoTime();
        String habitInfo = ndvliveCustomerInfoService.getHabitInfo(new BigDecimal(cifNoSender), channel);
        end = System.nanoTime();
        System.out.println("habitInfo " + (end - start) / 1e6);

        start = System.nanoTime();
        Date lastBlockSimDate = ndvliveCustomerInfoService.getLastBlockSimDate(new BigDecimal(cifNoSender), userId);
        end = System.nanoTime();
        System.out.println("lastBlockSimDate " + (end - start) / 1e6);

        start = System.nanoTime();
        boolean isFamiliar = ndvliveCustomerAccountInfoService.getFamiliarAccount(new BigDecimal(fraudActionsRestRequest.getDebitCustomerId()), fraudActionsRestRequest.getProcessCode(), fraudActionsRestRequest.getCreditAcctNumber(), fraudActionsRestRequest.getSmartSmsNo());
        end = System.nanoTime();
        System.out.println("isFamiliar " + (end - start) / 1e6);
        rbtranServiceInputModel.setKnownAccount(isFamiliar);

        start = System.nanoTime();
        boolean isInWhitelist = ndvliveDeviceInfoService.isInWhiteList(Long.parseLong(fraudActionsRestRequest.getDebitCustomerId()), fraudActionsRestRequest.getUserData02(), fraudActionsRestRequest.getDeviceId());
        end = System.nanoTime();
        System.out.println("isInWhitelist " + (end - start) / 1e6);
        rbtranServiceInputModel.setInWhitelist(isInWhitelist);
    }

This function takes up to 2 seconds.

DAO

public interface CustomerInformationTempDao {
    String getSbuCodeByClientNo(Integer clientNo);
}

DAOImpl

@Repository
public class CustomerInformationTempDaoImpl implements CustomerInformationTempDao {

    @Autowired
    @Qualifier("ndvliveJdbcTemplate")
    private JdbcTemplate ndvliveJdbcTemplate;

    public String getSbuCodeByClientNo(Integer clientNo) {
        String query = "SELECT * FROM mytable WHERE client_no=" + clientNo;
        try {
            return ndvliveJdbcTemplate.queryForObject(query, (resultSet, i) -> resultSet.getString("SBU_CODE"));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
}

SOLUTION

Changing DriverManagerDataSource

@Configuration
public class DatabaseConfig {

    @Autowired
    private Environment env;

    @Autowired
    private DataSourceProperties dataSourceProperties;

    @Bean(name = "fraudDb")
    public DataSource masterDataSource() {
        DataSourceBuilder factory = DataSourceBuilder
                .create(this.dataSourceProperties.getClassLoader())
                .driverClassName(env.getProperty("driver-class-name"))
                .url(env.getProperty("fraud.url"))
                .username(env.getProperty("fraud.username"))
                .password(env.getProperty("fraud.password"));
        return factory.build();
    }

    @Bean(name = "ndvliveDb")
    public DataSource secondDataSource() {
        DataSourceBuilder factory = DataSourceBuilder
                .create(this.dataSourceProperties.getClassLoader())
                .driverClassName(env.getProperty("driver-class-name"))
                .url(env.getProperty("ndvlive.url"))
                .username(env.getProperty("ndvlive.username"))
                .password(env.getProperty("ndvlive.password"));
        return factory.build();
    }

    @Bean(name = "fraudJdbcTemplate")
    @Autowired
    public JdbcTemplate masterJdbcTemplate(@Qualifier("fraudDb") DataSource fraudDb) {
        return new JdbcTemplate(fraudDb);
    }

    @Bean(name = "ndvliveJdbcTemplate")
    @Autowired
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("ndvliveDb") DataSource ndvliveDb) {
        return new JdbcTemplate(ndvliveDb);
    }
}

DriverManagerDataSource -So No Connection Pooling .Each Time you are creating and discarding DB Connection PreparedStatement - instead of appending 'clientNo' to Query you can use PreparedStatement — Paul Erric, Jan 19 '19 at 17:45
If I use Prepared Statement except queryForObject, I will get faster results. Am I understood correctly? — Berkin, Jan 19 '19 at 17:46

score 6 · Accepted Answer · answered Jan 19 '19 at 17:47

There are few things you can do to improve the performance:

Instead of using DriverManagerDataSource define a connection pool with a fixed connection count. To simplify use SingleConnectionDataSource which will not perform any on-borrow connection validation. It's not a production grade setup but there will be less moving pieces in your test.
Instead of SELECT * specify only the columns that you need. Resolving * could be different between JDBC and command line client, best to remove this unknown.
Use prepared statements with client_no = ? instead of client_no=" + clientNo concatenation. This will result in better query plan caching when checking different client numbers.

Do note that JVM requires 10,000+ invocations of a method to start JIT compiling and optimizing. If you have a simple main() that doesn't warm up the JVM the code will be slower. The command line client is compiled to native code already.

I changed all my queries as you said. Also I guess I have to change DriverManagerDataSoruce to DataSourceBuilder. https://stackoverflow.com/questions/25573034/spring-boot-how-do-i-set-jdbc-pool-properties-like-maximum-number-of-connection — Berkin, Jan 19 '19 at 18:17
+1 This answer fixed my slow Junit tests just by changing `DriverManagerDataSource` to `SingleConnectionDataSource`. — Kapil, Aug 31 '19 at 08:19

JDBC Template Works Slow

Database Config Class

Queries In RestController

DAO

DAOImpl

SOLUTION

1 Answers1