I have this issue:
我有这个问题:
I have an old big application developed in java (Spring framework). The problem is a sql query that It takes a long time when I do it from the Java application , unlike when I do it from Sql Developer. From Java It takes around 7 minutes. From Sql Developer it takes 30 seconds. The query is the same from both cases.
我有一个用java(Spring框架)开发的旧的大应用程序。问题是一个SQL查询,当我从Java应用程序执行它时需要很长时间,这与我从Sql Developer完成时不同。来自Java大约需要7分钟。从Sql Developer开始需要30秒。两种情况下的查询都是相同的。
The application config is:
应用程序配置是:
- JDK 1.6
- OJDBC 6
- Spring framework 3.6
- Oracle 11g Enterprise Edition Release 11.2.0.3.0
Spring框架3.6
Oracle 11g企业版11.2.0.3.0版
Datasource config:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driverClassName}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="initialPoolSize" value="${jdbc.initialPoolSize}"/>
<property name="maxPoolSize" value="${jdbc.maxPoolSize}"/>
<property name="minPoolSize" value="${jdbc.minPoolSize}"/>
</bean>
Finally, I'm using IBATIS 2.3 for the sql mapping.
最后,我使用IBATIS 2.3进行sql映射。
I made a test debug, and when the DAO class calls the Ibatis maps (the query) is when the slow performance begins.
我做了一个测试调试,当DAO类调用Ibatis映射(查询)时,慢速性能开始。
Some help?
Sorry for my english.
对不起我的英语不好。
Regards
Update 1:
The query:
SELECT PAG.ID_PAG AS ID_PAG, CAD.CAD_NOMBRE AS CAD_NOMBRE, LOC.LOC_NOMBRE AS LOC_NOMBRE, EMI.EMI_CODIGO AS EMI_CODIGO, EMI.EMI_NOMBRE AS EMI_NOMBRE, TO_CHAR(PAG.PAG_FECHA, 'DD/MM/YYYY') AS TRX_FECHA, PAG.PAG_POS AS TRX_POS, RTRIM(PAG.PAG_COMPROBANTE) AS TRX_COMPROBANTE, 1 AS CANTIDAD, PAG.PAG_MONTO AS TRX_MONTO, PAG.PAG_COMISION_TOTAL AS MONTO_COMISION, PAG.PAG_NETO_TOTAL AS MONTO_NETO FROM PW_PAGO PAG, PW_LOCAL LOC, PW_EMISOR EMI, PW_CADENA CAD, PW_TIPOTRX_EMI TTE WHERE CAD.ID_CADENA = PAG.ID_CADENA AND EMI.ID_EMISOR = PAG.ID_EMISOR AND LOC.ID_LOCAL = PAG.ID_LOCAL AND PAG.ID_TIPOTRX_EMI = TTE.ID_TIPOTRX_EMI AND PAG.PAG_FLAG_COMISION = 'S' AND PAG.PAG_FECHA >= TO_DATE('20160501','YYYYMMDD') AND PAG.PAG_FECHA <= TO_DATE('20160503','YYYYMMDD') ORDER BY CAD.CAD_NOMBRE, LOC.LOC_NOMBRE, EMI.EMI_NOMBRE, TO_CHAR(PAG.PAG_FECHA, 'DD/MM/YYYY'), PAG.PAG_POS, PAG.PAG_COMPROBANTE, PAG.PAG_MONTO
SELECT PAG.ID_PAG作为ID_PAG,CAD.CAD_NOMBRE作为CAD_NOMBRE,LOC.LOC_NOMBRE作为LOC_NOMBRE,EMI.EMI_CODIGO作为EMI_CODIGO,EMI.EMI_NOMBRE作为EMI_NOMBRE,TO_CHAR(PAG.PAG_FECHA,'DD / MM / YYYY')作为TRX_FECHA,PAG。 PAG_POS作为TRX_POS,RTRIM(PAG.PAG_COMPROBANTE)作为TRX_COMPROBANTE,1作为CANTIDAD,PAG.PAG_MONTO作为TRX_MONTO,PAG.PAG_COMISION_TOTAL作为MONTO_COMISION,PAG.PAG_NETO_TOTAL作为MONTO_NETO来自PW_PAGO PAG,PW_LOCAL LOC,PW_EMISOR EMI,PW_CADENA CAD,PW_TIPOTRX_EMI TTE WHERE CAD.ID_CADENA = PAG.ID_CADENA AND EMI.ID_EMISOR = PAG.ID_EMISOR AND LOC.ID_LOCAL = PAG.ID_LOCAL AND PAG.ID_TIPOTRX_EMI = TTE.ID_TIPOTRX_EMI AND PAG.PAG_FLAG_COMISION ='S'AND PAG.PAG_FECHA> = TO_DATE('20160501' ,'YYYYMMDD')和PAG.PAG_FECHA <= TO_DATE('20160503','YYYYMMDD')按CAD.CAD_NOMBRE,LOC.LOC_NOMBRE,EMI.EMI_NOMBRE,TO_CHAR(PAG.PAG_FECHA,'DD / MM / YYYY')订购, PAG.PAG_POS,PAG.PAG_COMPROBANTE,PAG.PAG_MONTO
Update 2: Here is the Ibatis config:
更新2:这是Ibatis配置:
<?xml version="1.0" encoding="UTF-8"?>
<resultMap id="getPagosComisionesSinGrupoDetalle"
class="cl.bbr.portalweb.dto.InformePagosComisionesSinGrupoDetalleDTO" >
<result column="ID_PAG" property="id_pag" jdbcType="INTEGER" />
<result column="CAD_NOMBRE" property="cad_nombre" jdbcType="VARCHAR" />
<result column="LOC_NOMBRE" property="loc_nombre" jdbcType="VARCHAR" />
<result column="EMI_CODIGO" property="emi_codigo" jdbcType="VARCHAR" />
<result column="EMI_NOMBRE" property="emi_nombre" jdbcType="VARCHAR" />
<result column="TRX_FECHA" property="trx_fecha" jdbcType="DATE" />
<result column="TRX_POS" property="trx_pos" jdbcType="VARCHAR" />
<result column="TRX_COMPROBANTE" property="trx_comprobante" jdbcType="VARCHAR" />
<result column="CANTIDAD" property="cantidad" jdbcType="INTEGER" />
<result column="TRX_MONTO" property="trx_monto" jdbcType="DECIMAL" />
<result column="MONTO_COMISION" property="monto_comision" jdbcType="DECIMAL" />
<result column="MONTO_NETO" property="monto_neto" jdbcType="DECIMAL" />
</resultMap>
<!-- Obtiene datos comisiones sin grupo y con detalle-->
<select id="getPagosComisionesSinGrupoDetalle"
resultMap="getPagosComisionesSinGrupoDetalle"
parameterClass="cl.bbr.portalweb.dto.InformePagosComisionesSearchCriteria">
SELECT PAG.ID_PAG AS ID_PAG, CAD.CAD_NOMBRE AS CAD_NOMBRE, LOC.LOC_NOMBRE AS LOC_NOMBRE, EMI.EMI_CODIGO AS EMI_CODIGO,EMI.EMI_NOMBRE AS EMI_NOMBRE, TO_CHAR(PAG.PAG_FECHA, 'DD/MM/YYYY') AS TRX_FECHA, PAG.PAG_POS AS TRX_POS, RTRIM(PAG.PAG_COMPROBANTE) AS TRX_COMPROBANTE, 1 AS CANTIDAD,PAG.PAG_MONTO AS TRX_MONTO, PAG.PAG_COMISION_TOTAL AS MONTO_COMISION,PAG.PAG_NETO_TOTAL AS MONTO_NETO FROM PW_PAGO PAG, PW_LOCAL LOC,PW_EMISOR EMI, PW_CADENA CAD, PW_TIPOTRX_EMI TTE WHERE CAD.ID_CADENA =PAG.ID_CADENA AND EMI.ID_EMISOR = PAG.ID_EMISOR AND LOC.ID_LOCAL = PAG.ID_LOCAL AND PAG.ID_TIPOTRX_EMI = TTE.ID_TIPOTRX_EMI AND PAG.PAG_FLAG_COMISION = 'S' AND <![CDATA[PAG.PAG_FECHA >= #f_ini#]]> AND <![CDATA[PAG.PAG_FECHA <= #f_fin#]]> ORDER BY CAD.CAD_NOMBRE, LOC.LOC_NOMBRE, EMI.EMI_NOMBRE, TO_CHAR(PAG.PAG_FECHA, 'DD/MM/YYYY'), PAG.PAG_POS, PAG.PAG_COMPROBANTE, PAG.PAG_MONTO
</select>
Ibatis map config:
Ibatis地图配置:
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> <sqlMapConfig>
<settings useStatementNamespaces="true"/>
<sqlMap resource="cl/bbr/portalweb/dao/ibatis/maps/oracle/FINDER_INFORME_PAGOS_COMISIONES_SqlMap.xml"/> </sqlMapConfig>
1 个解决方案
#1
1
Set the fetchSize
parameter on your select to a large value, e.g. 1000:
将select上的fetchSize参数设置为较大的值,例如1000:
<select id="getPagosComisionesSinGrupoDetalle"
resultMap="getPagosComisionesSinGrupoDetalle"
parameterClass="cl.bbr.portalweb.dto.InformePagosComisionesSearchCriteria"
fetchSize="1000">
SELECT PAG.ID_PAG AS ID_PAG, CAD.CAD_NOMBRE AS CAD_NOMBRE, LOC.LOC_NOMBRE AS LOC_NOMBRE, EMI.EMI_CODIGO AS EMI_CODIGO,EMI.EMI_NOMBRE AS EMI_NOMBRE, TO_CHAR(PAG.PAG_FECHA, 'DD/MM/YYYY') AS TRX_FECHA, PAG.PAG_POS AS TRX_POS, RTRIM(PAG.PAG_COMPROBANTE) AS TRX_COMPROBANTE, 1 AS CANTIDAD,PAG.PAG_MONTO AS TRX_MONTO, PAG.PAG_COMISION_TOTAL AS MONTO_COMISION,PAG.PAG_NETO_TOTAL AS MONTO_NETO FROM PW_PAGO PAG, PW_LOCAL LOC,PW_EMISOR EMI, PW_CADENA CAD, PW_TIPOTRX_EMI TTE WHERE CAD.ID_CADENA =PAG.ID_CADENA AND EMI.ID_EMISOR = PAG.ID_EMISOR AND LOC.ID_LOCAL = PAG.ID_LOCAL AND PAG.ID_TIPOTRX_EMI = TTE.ID_TIPOTRX_EMI AND PAG.PAG_FLAG_COMISION = 'S' AND <![CDATA[PAG.PAG_FECHA >= #f_ini#]]> AND <![CDATA[PAG.PAG_FECHA <= #f_fin#]]> ORDER BY CAD.CAD_NOMBRE, LOC.LOC_NOMBRE, EMI.EMI_NOMBRE, TO_CHAR(PAG.PAG_FECHA, 'DD/MM/YYYY'), PAG.PAG_POS, PAG.PAG_COMPROBANTE, PAG.PAG_MONTO
</select>
The default value for the Oracle JDBC driver is very low, so there's a lot of back and forth traffic with the server.
Oracle JDBC驱动程序的默认值非常低,因此服务器有很多来回流量。
#1
1
Set the fetchSize
parameter on your select to a large value, e.g. 1000:
将select上的fetchSize参数设置为较大的值,例如1000:
<select id="getPagosComisionesSinGrupoDetalle"
resultMap="getPagosComisionesSinGrupoDetalle"
parameterClass="cl.bbr.portalweb.dto.InformePagosComisionesSearchCriteria"
fetchSize="1000">
SELECT PAG.ID_PAG AS ID_PAG, CAD.CAD_NOMBRE AS CAD_NOMBRE, LOC.LOC_NOMBRE AS LOC_NOMBRE, EMI.EMI_CODIGO AS EMI_CODIGO,EMI.EMI_NOMBRE AS EMI_NOMBRE, TO_CHAR(PAG.PAG_FECHA, 'DD/MM/YYYY') AS TRX_FECHA, PAG.PAG_POS AS TRX_POS, RTRIM(PAG.PAG_COMPROBANTE) AS TRX_COMPROBANTE, 1 AS CANTIDAD,PAG.PAG_MONTO AS TRX_MONTO, PAG.PAG_COMISION_TOTAL AS MONTO_COMISION,PAG.PAG_NETO_TOTAL AS MONTO_NETO FROM PW_PAGO PAG, PW_LOCAL LOC,PW_EMISOR EMI, PW_CADENA CAD, PW_TIPOTRX_EMI TTE WHERE CAD.ID_CADENA =PAG.ID_CADENA AND EMI.ID_EMISOR = PAG.ID_EMISOR AND LOC.ID_LOCAL = PAG.ID_LOCAL AND PAG.ID_TIPOTRX_EMI = TTE.ID_TIPOTRX_EMI AND PAG.PAG_FLAG_COMISION = 'S' AND <![CDATA[PAG.PAG_FECHA >= #f_ini#]]> AND <![CDATA[PAG.PAG_FECHA <= #f_fin#]]> ORDER BY CAD.CAD_NOMBRE, LOC.LOC_NOMBRE, EMI.EMI_NOMBRE, TO_CHAR(PAG.PAG_FECHA, 'DD/MM/YYYY'), PAG.PAG_POS, PAG.PAG_COMPROBANTE, PAG.PAG_MONTO
</select>
The default value for the Oracle JDBC driver is very low, so there's a lot of back and forth traffic with the server.
Oracle JDBC驱动程序的默认值非常低,因此服务器有很多来回流量。