如何从我的Java应用程序中提高SQL性能

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

Spring框架3.6

Oracle 11g Enterprise Edition Release 11.2.0.3.0

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

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