NamedParameterJdbcTemplate really slow for list parameter

[jadedr]

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.

[mubamusic]

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

[jadedr]

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.