Announcement Announcement Module
Collapse
No announcement yet.
NamedParameterJdbcTemplate really slow for list parameter Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • NamedParameterJdbcTemplate really slow for list parameter

    Hello,

    I used NamedParameterJdbcTemplate for the following sql:
    select create_date from myTable where create_date>=:startDate and create_date<:endDate and period_flg='Y' and statistic_flg='Y' and source='typeA' and source_id in ( :ids ) "

    When I pass single element arrayList for ids. It returns fast. (less than a second).

    But when I pass two elements arrayList for ids. It return very slow (around 5 mins). I run the sql directly in sqlplus or sqldeveloper with 2 ids. Both returned in less than 1 second. And with 1 element it returns 95 records. with 2 elements, it returns 120 records. I wrote the simple jdbc client to test the sql using PreparedStatment:
    select create_date from myTable where create_date>=? and create_date<? and period_flg='Y' and statistic_flg='Y' and source='typeA' and source_id in ( ?, ? )

    It returns in less than second.

    BTW, db is oracle. using spring version 3.0.6.

    Here is my piece of test code:
    NamedParameterJdbcTemplate template=new NamedParameterJdbcTemplate(ds);
    Map<String, Object> params=new HashMap<String, Object>();

    ArrayList<String> idsList=new ArrayList<String>();
    idsList.add("3-947FEBX");
    idsList.add("1-89KLGUX");
    params.put("ids", idsList);

    Calendar cal=Calendar.getInstance();
    cal.add(Calendar.MONTH, -12);
    params.put("startDate", cal);
    Calendar cal1=Calendar.getInstance();
    params.put("endDate", cal1);

    template.query("select create_date from myTable where create_date>=:startDate and create_date<:endDate and period_flg='Y' and statistic_flg='Y' and source='typeA' and source_id in ( :ids ) ",
    params,
    new RowCallbackHandler(){
    @Override
    public void processRow(ResultSet arg0)
    throws SQLException {
    // TODO Auto-generated method stub
    System.out.println(arg0.getDate("create_date"));
    }

    }
    );



    I enable the debug for JdbcTemplate, I see it stop at second line for long time:
    0 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
    2 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select create_date from myTable where create_date>=? and create_date<? and period_flg='Y' and statistic_flg='Y' and source='typeA' and source_id in ( ?, ? )]
    (long time lag here)
    2011-04-01
    .....

    Debugging through the code, it seems it stop at rs=ps.executeQuery in jdbc.core.jdbcTemplate.doPreparedStatement(Prepare dStatement)


    Would someone please tell me if I pass the list into NamedParameterJdbcTemplate right? I don't understand why the query returns fast in both simple jdbc client and sqlplus, which it takes so much more time when using the NamedParameterJdbcTemplate. I wonder if anyone else encounter similar problem before.

    thanks.

  • #2
    Hi

    Calendar cal1=Calendar.getInstance();
    Above instance its return long format date so its take time to query processing .

    Instead of this try SimpleDateFormat..like 'DD/MM/YYYY'.

    Thanks

    Comment


    • #3
      Hello, mubamuasic,

      Thanks a lot. Actually I noticed that after I post this. When I used java.sql.Date instead of Calendar, it solved the problem. But I did not fully understand the issue. Thanks for raising to my attention. I should realize the Calendar get converted to timestamp instead of date. I will check at DB side to understand the difference caused by passing single id or 2 ids.

      Comment

      Working...
      X