Announcement Announcement Module
No announcement yet.
Spring is taking ridiculous amount of time for accessing data from the OracleDatabase Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring is taking ridiculous amount of time for accessing data from the OracleDatabase

    The Spring Data Access is taking around 10 minutes to get the Data, while getting the same data using same query using Toad is taking like 20 seconds.

    Am I doing something wrong? (Or) Do I need to add any extra configuration to make the Spring data access faster?

    <?xml version="1.0" encoding="UTF-8"?>


    <bean id="dataSource1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:hostNameortS"/>
    <property name="username" value="uname"/>
    <property name="password" value="pass"/>

    <!-- Transaction manager for a single DataSource
    <bean id="transactionManager1" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
    <property name="dataSource"><ref local="dataSource1"/></property>

    <bean id="mapper" class="com.AccountRowMapper"/>
    <bean id="userDAO" class="com.UserJdbcDAO">
    <property name="dataSource"><ref local="dataSource1"/></property>
    <property name="rowMapper"><ref local="mapper"/></property>

    <bean id="userManagerTarget" class="com.UserManagerImpl">
    <property name="userDAO"><ref local="userDAO"/></property>

    <bean id="userManager" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
    <property name="transactionManager"><ref local="transactionManager1"/></property>
    <property name="target"><ref local="userManagerTarget"/></property>
    <property name="transactionAttributes">
    <prop key="*">PROPAGATION_REQUIRED</prop>


    public class UserJdbcDAO<T> extends SimpleJdbcDaoSupport {

    public static String query = "select Query";

    public ParameterizedRowMapper<T> rowMapper;
    public ParameterizedRowMapper<T> getRowMapper() {
    return rowMapper;
    public void setRowMapper(ParameterizedRowMapper<T> rowMapper) {
    this.rowMapper = rowMapper;
    public void getUser() {
    long startTime = 0;
    System.out.println("Start Time:"+(startTime = System.currentTimeMillis()));
    List<T> data = getSimpleJdbcTemplate().query(query, rowMapper);

    package com;

    import com.UserJdbcDAO;

    public class UserManagerImpl{
    private UserJdbcDAO dao;

    public void setUserDAO(UserJdbcDAO dao) {
    this.dao = dao;

    public void getUser() {
    package com;

    import org.springframework.jdbc.core.simple.Parameterized RowMapper;

    import java.sql.ResultSet;
    import java.sql.SQLException;

    public class AccountRowMapper implements ParameterizedRowMapper {
    public static final String ACCOUNT_ID = "id";
    public static final String ACCOUNT_NAME = "name";
    public static final String ACCOUNT_ACTIVE = "active";
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
    return mapAccountRow(resultSet,i,DEFAULT_COLUMN_PREFIX);
    public static Account mapAccountRow(ResultSet resultSet, int i, String prefix) throws SQLException {
    Account account = new Account();
    account.setId(resultSet.getLong(prefix+ACCOUNT_ID) );
    account.setName(resultSet.getString(prefix+ACCOUNT _NAME));
    account.setActive(resultSet.getInt(prefix+ACCOUNT_ ACTIVE));
    return account;

    package com;

    import org.springframework.context.ApplicationContext;
    import plicationContext;

    public class Main {

    * @param args
    public static void main(String[] args) {
    ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext .xml", Main.class);
    UserManagerImpl userManagerImpl = (UserManagerImpl)context.getBean("userManagerTarge t");


  • #2
    How many rows in the table?


    • #3
      Use [ code][/code ] tags when posting code !!!!

      Next you are comparing apples and oranges. In toad it is only the query execution time, in spring it is also the time to convert all rows into objects and my guess is that you select a heavy amount of rows which need to get converted.

      Also you aren't using transactions, you have configured them but don't use them, you are using the target directly instead of using the proxied instance. You are using system.out if there is something that is slow it is system.out.
      Last edited by Marten Deinum; Oct 10th, 2009, 04:29 AM.


      • #4
        Spring Data Access.

        I am sorry about the code having some funny emoticons here and there.

        Regarding transactions, I was using transactions but I thought transactions might be causing data retrieval to be slow. So, I changed the code to not use transactions.

        Regarding comparing apples and oranges, There are 57,000 rows in the table. So, you think row mapping is causing lot of time? Can I do anything to make it faster because what good is it without converting them into objects for future use?


        • #5
          Converting a resultset of 57000 takes a lot of time and is probably also not so wise to do (unless you want memory and performance issues). Use paging and/or figure out a way to limit the amount of data.