dynamic 语句带参数与不带参数的性能差距问题

spring+ibatis的应用,oracle数据库,三张表的数据两分别为:
OFMS_DUN_BILL 300W, OFMS_DUN_SERV 150W, OFMS_DUN_ORG 3k

spring 关键配置如下:

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName"><value>${jdbc.driverClassName}</value></property>
    <property name="url"><value>${jdbc.url}</value></property>
    <property name="username"><value>${jdbc.username}</value></property>
    <property name="password"><value>${jdbc.password}</value></property>
</bean>

<bean id="sqlMapClient"
    class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
  <property name="configLocation">
    <value>/WEB-INF/SqlMapConfig.xml</value>
  </property>
</bean>

<bean id="ibatisQryDao" class="test.dao.QryDao">
  <property name="dataSource" ref="dataSource" />
  <property name="sqlMapClient" ref"sqlMapClient" />
</bean>

SqlMapConfig.xml 配置如下:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
	<sqlMap resource="ibatis/test.xml" />
</sqlMapConfig>

ibatis查询配置如下:

ibatis/test.xml
<select id="testCount0" resultClass="java.util.HashMap">
  select sum(b.AMOUNT) SUM_AMOUNT, count(*) BILL_COUNT 
  from OFMS_DUN_BILL b, OFMS_DUN_ORG o, OFMS_DUN_SERV s
  where s.SERV_ID=b.SERV_ID and o.ID=b.FK_ASSIGN_DUN_ORG_ID
  <dynamic>
    and o.BCFL>='0001' and o.BCFL<='0001z'
  </dynamic>
</select>
	
<select id="testCount1" resultClass="java.util.HashMap">
  select sum(b.AMOUNT) SUM_AMOUNT, count(*) BILL_COUNT 
  from OFMS_DUN_BILL b, OFMS_DUN_ORG o, OFMS_DUN_SERV s
  where s.SERV_ID=b.SERV_ID and o.ID=b.FK_ASSIGN_DUN_ORG_ID
  <dynamic>
    and o.BCFL>=#orgBcfl# and o.BCFL<=#orgBcflEnd#
  </dynamic>
</select>

查询的dao代码如下:

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

public class QryDao extends SqlMapClientDaoSupport{
	
	public List qryTestCount0(){ 
		Map args = new HashMap();
		List list = super.getSqlMapClientTemplate().queryForList("testCount0", args);
		return list;
		
	}
	public List qryTestCount1(){ 
		Map args = new HashMap();
		args.put("orgBcfl", "0001");
		args.put("orgBcflEnd", "0001z");
		List list = super.getSqlMapClientTemplate().queryForList("testCount1", args);
		return list;
	}
}

问题:
执行 QryDao.qryTestCount0() 需要6s,跟在pl/sql查差不多
执行 QryDao.qryTestCount1() 需要20s

两个查询的区别就是一个dynamic是写死的一个是带参数的，怎么性能差距这么大，甚是不解

iBATIS的东东请换个地方。

楼上啥意思呀

你将ibatis最后生成的SQL贴出来看看

   Map args = new HashMap(); 
创建对象,
        args.put("orgBcfl", "0001");  
        args.put("orgBcflEnd", "0001z"); 
为对象添加值也是要时间的啊

dynamic加不加有什么用吗?

那时间也差得太多了......

还有其它原因么？

不关动态sql语句的事。