ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yuvraj Shinde" <Yuvraj.Shi...@sos.sungard.com>
Subject iBATIS QUERRY TAKE LONG TIME AS COMAPARED TO NORMAL JDBC
Date Thu, 31 Jan 2008 06:01:15 GMT
Hi all,

 

 

I am providing you the detail about the QUERRY, TABLE STRUCTURE
structure.

I am also attaching the DTO.

I am passing dates here in hash map.

 

QUERRY 

 

<select id="TPOW_SEARCH_BY_DELIVERY_AND_LOGIN_DATE"
parameterClass="java.util.Map"

    resultMap="TPOW_RESULTMAP"
resultClass="com.sungard.energy.shared.zainet.business.domain. TpowDTO">

    SELECT * 

      FROM TPOW 

      WHERE

        TPOW_ENDDATE &gt;= #FIRST_SELECTED#

          AND

        TPOW_STARTDATE &lt;= #LAST_SELECTED#

            AND   

        AUDIT_ALF &lt;= #LOGINDATE:TIMESTAMP#

          AND

        AUDIT_AOHM &gt; #LOGINDATE:TIMESTAMP#

                  

  </select>

 

After running this querry it takes almost 40 seconds.while normal JDBC
take 15 second.what could be reason ?

 

 

TOTAL NO OF TABLE ROWS - 300000.

 

 

TABLE STRUCTURE

 

CREATE TABLE TPOW ( 

  AUDIT_ACTIVE        NUMBER (5), 

  AUDIT_AGEN          NUMBER (5), 

  AUDIT_AOP0          CHAR (4), 

  AUDIT_ADATE0        DATE, 

  AUDIT_ATIME0        NUMBER (5), 

  AUDIT_AOP1          CHAR (4), 

  AUDIT_ADATE1        DATE, 

  AUDIT_ATIME1        NUMBER (5), 

  AUDIT_ALF           DATE, 

  AUDIT_AOHM          DATE, 

  AUDIT_ROHM          DATE, 

  AUDIT_REFRESH       NUMBER (10), 

  AUDIT_ZKEY          NUMBER (10)   NOT NULL, 

  AUDIT_XKEY          NUMBER (10)   NOT NULL, 

  AUDIT_UTIL1         NUMBER (10)   NOT NULL, 

  AUDIT_UTIL2         NUMBER (10)   NOT NULL, 

  TRADE_TNUM          CHAR (6), 

  TRADE_STATUS        NUMBER (10), 

  TRADE_VOID          CHAR (1), 

  TRADE_TRDTYP        CHAR (2), 

  TRADE_PARENT        CHAR (6), 

  TRADE_PRTGEN        NUMBER (5), 

  TRADE_PRTTYP        CHAR (2), 

  TRADE_CHILD1        CHAR (6), 

  TRADE_CHILD2        CHAR (6), 

  TRADE_TDATE         DATE, 

  TRADE_GROUP         CHAR (5), 

  TRADE_SGROUP        CHAR (5), 

  TRADE_GROUP3        CHAR (5), 

  TRADE_GROUP4        CHAR (5), 

  TRADE_CPTY          CHAR (10), 

  TRADE_MANUM         CHAR (15), 

  TRADE_CCOMM         CHAR (1), 

  TRADE_CCSCHED       CHAR (10), 

  TRADE_CCFORM        NUMBER (5), 

  TRADE_CCAMT         FLOAT (49), 

  TRADE_CCUNIT        FLOAT (49), 

  TRADE_CCCY          CHAR (6), 

  TRADE_BRO           CHAR (10), 

  TRADE_BCOMM         CHAR (1), 

  TRADE_BCSCHED       CHAR (10), 

  TRADE_BCFORM        NUMBER (5), 

  TRADE_BCAMT         FLOAT (49), 

  TRADE_BCUNIT        FLOAT (49), 

  TRADE_BCCY          CHAR (6), 

  TRADE_SALES         CHAR (10), 

  TRADE_SCOMM         CHAR (1), 

  TRADE_SCSCHED       CHAR (10), 

  TRADE_SCFORM        NUMBER (5), 

  TRADE_SCAMT         FLOAT (49), 

  TRADE_SCUNIT        FLOAT (49), 

  TRADE_SCCY          CHAR (6), 

  TRADE_BOOK          CHAR (4), 

  TRADE_TRADER        CHAR (4), 

  TRADE_TRDREX        CHAR (4), 

  TRADE_FCONF         CHAR (4), 

  TRADE_BCONF         CHAR (4), 

  TRADE_XREF          CHAR (10), 

  TRADE_XREFN         FLOAT (49), 

  TRADE_PLCCY         CHAR (6), 

  TRADE_AMT           FLOAT (49), 

  TRADE_SCN           NUMBER (5), 

  TRADE_MEMO          CHAR (128), 

  TRADE_MEMO2         CHAR (128), 

  TRADE_TORG          CHAR (6), 

  TRADE_TORGTP        CHAR (2), 

  TRADE_INTERTNUM     CHAR (6), 

  TRADE_WHATIF        CHAR (1), 

  TRADE_PROD          NUMBER (10), 

  TRADE_TEMPLATE      NUMBER (10), 

  TPOW_TPL            CHAR (32), 

  TPOW_CONTACT        CHAR (4), 

  TPOW_CONTACT2       CHAR (4), 

  TPOW_MKT            CHAR (6), 

  TPOW_LOC            CHAR (6), 

  TPOW_SCOMP1         CHAR (6), 

  TPOW_TRADETYPE      CHAR (1), 

  TPOW_STARTDATE      DATE, 

  TPOW_ENDDATE        DATE, 

  TPOW_BCOUNT         NUMBER (5), 

  TPOW_TRANFLAG       CHAR (1), 

  TPOW_DATEF          DATE, 

  TPOW_DATES          DATE, 

  TPOW_DATEE          DATE, 

  TPOW_DAYS           NUMBER (5), 

  TPOW_TIMES          NUMBER (5), 

  TPOW_TIMEE          NUMBER (5), 

  TPOW_AMOUNT         FLOAT (49), 

  TPOW_RAMPAMT        FLOAT (49), 

  TPOW_PRICE          FLOAT (49), 

  TPOW_RAMP           CHAR (1), 

  TPOW_WAAMOUNT       FLOAT (49), 

  TPOW_WAPRICE        FLOAT (49), 

  TPOW_PRICEFIXED     FLOAT (49), 

  TPOW_PRICEBASIS     FLOAT (49), 

  TPOW_PRICE2BASIS    FLOAT (49), 

  TPOW_PRICEPREM      FLOAT (49), 

  TPOW_IDXFLAG        CHAR (1), 

  TPOW_IDXMKT         CHAR (6), 

  TPOW_SCOMP2         CHAR (6), 

  TPOW_IDXLOC         CHAR (6), 

  TPOW_RATE           FLOAT (49), 

  TPOW_FLOOR          FLOAT (49), 

  TPOW_CAP            FLOAT (49), 

  TPOW_CCY            CHAR (6), 

  TPOW_UNIT           CHAR (1), 

  TPOW_UNIT2          CHAR (1), 

  TPOW_UNITPREM       CHAR (1), 

  TPOW_PERIOD         CHAR (1), 

  TPOW_PERIODPRC      CHAR (1), 

  TPOW_PERIODPREM     CHAR (1), 

  TPOW_FIXSCHE        CHAR (1), 

  TPOW_FIXSTART       NUMBER (5), 

  TPOW_FIXDAYS        NUMBER (5), 

  TPOW_FIXPSET        CHAR (10), 

  TPOW_FIX2SCHE       CHAR (1), 

  TPOW_FIX2START      NUMBER (5), 

  TPOW_FIX2DAYS       NUMBER (5), 

  TPOW_FIX2PSET       CHAR (10), 

  TPOW_FIXMONTH1      NUMBER (5), 

  TPOW_FIXMONTH2      NUMBER (5), 

  TPOW_PEAKCOUNT      NUMBER (5), 

  TPOW_PEAKPERIOD     CHAR (1), 

  TPOW_FIRM           CHAR (10), 

  TPOW_FLEXLOC        CHAR (1), 

  TPOW_LOSSFLAG       CHAR (1), 

  TPOW_LOSSMKT        CHAR (6), 

  TPOW_LOSSLOC        CHAR (6), 

  TPOW_LOSSPRICE      FLOAT (49), 

  TPOW_LOSSRATE       FLOAT (49), 

  TPOW_LOSSSUP        CHAR (1), 

  TPOW_LOSSIDX        CHAR (1), 

  TPOW_OPT1FLAG       CHAR (1), 

  TPOW_OPT1TYPE       CHAR (1), 

  TPOW_OPT1MODEL      CHAR (1), 

  TPOW_OPT1TRIG       CHAR (1), 

  TPOW_OPT1TRIGTYPE   CHAR (1), 

  TPOW_OPT1TPRICE     FLOAT (49), 

  TPOW_OPT1EXTYPE     CHAR (1), 

  TPOW_FLEXFLAG       CHAR (1), 

  TPOW_OPT1MIN        FLOAT (49), 

  TPOW_OPT1MAX        FLOAT (49), 

  TPOW_OPT1EXNUM      NUMBER (5), 

  TPOW_OPT1DIGTP      CHAR (1), 

  TPOW_OPT1NOTTYPE    CHAR (1), 

  TPOW_OPT1NOTVAL     FLOAT (49), 

  TPOW_OPT1NOTTIME    NUMBER (5), 

  TPOW_OPT1EXPDATE    DATE, 

  TPOW_OPTVOL         CHAR (1), 

  TPOW_PREM1FLAG      CHAR (1), 

  TPOW_PREM1TYPE      CHAR (1), 

  TPOW_PREM1VAL       FLOAT (49), 

  TPOW_PREM1CCY       CHAR (6), 

  TPOW_PREM1PAY       FLOAT (49), 

  TPOW_PREM1DATE      DATE, 

  TPOW_INV1           CHAR (1), 

  TPOW_INVLAG         CHAR (1), 

  TPOW_INVLAGNUM      NUMBER (10), 

  TPOW_INVROUND       CHAR (1), 

  TPOW_INVPREC        NUMBER (5), 

  TPOW_BS             CHAR (1), 

  TPOW_TRANSCO        CHAR (10), 

  TPOW_TRANSCA        CHAR (10), 

  TPOW_TRANSDUR       CHAR (10), 

  TPOW_LOSSPEAKS      NUMBER (5), 

  TPOW_LOSSPEAKE      NUMBER (5), 

  TPOW_TRANSLOSS      FLOAT (49), 

  TPOW_TRANSLOSS2     FLOAT (49), 

  TPOW_TRANSNET       CHAR (1), 

  TPOW_TRANSOASIS1    CHAR (20), 

  TPOW_TRANSOASIS2    CHAR (20), 

  TPOW_TRANSOASIS3    CHAR (20), 

  TPOW_TZ             CHAR (7), 

  TPOW_SETTLECALTYPE  CHAR (1), 

  TPOW_CONTRACTTYPE   CHAR (1), 

  TPOW_ADHOCPERIOD    CHAR (1), 

  TPOW_ADHOCDAYS      CHAR (1), 

  TPOW_BILLON         NUMBER (5), 

  TPOW_PAYON          NUMBER (5), 

  TPOW_ACCTMO         CHAR (5), 

  TPOW_THRESH         FLOAT (49), 

  TPOW_HEDGE          CHAR (1), 

  TPOW_VAT            FLOAT (49), 

  TPOW_HDAYLOC        CHAR (6), 

  TPOW_SCHED          CHAR (1), 

  TPOW_HDAYMKT        CHAR (6), 

  TPOW_FUNIT          CHAR (20), 

  TPOW_FUNITNUM       NUMBER (5), 

  TPOW_OSNUM          NUMBER (10), 

  TPOW_OSDATE         DATE, 

  TPOW_UTIL1          CHAR (1), 

  TPOW_UTIL2          CHAR (1), 

  TPOW_UTIL3          CHAR (1), 

  TPOW_UTIL4          CHAR (10), 

  TPOW_UTIL5          CHAR (10), 

  TPOW_TNUMGROUP      CHAR (6), 

  TPOW_SOURCE         NUMBER (10), 

  TPOW_ECVNA          CHAR (1), 

  TPOW_NOTIFIED       CHAR (1), 

  TPOW_ECVNAAID       CHAR (10), 

  TPOW_ECVNAAKEY      CHAR (10), 

  TPOW_EFPBROH        CHAR (10), 

  TPOW_EFPBROC        CHAR (10), 

  TPOW_EFPPRICE       FLOAT (49), 

  TPOW_EFPMONTH       DATE, 

  TPOW_EFPTRIG        CHAR (1))

   TABLESPACE ZNSHELL NOLOGGING 

   PCTFREE 10

   INITRANS 1

   MAXTRANS 255

  STORAGE ( 

   INITIAL 342884352

   MINEXTENTS 1

   MAXEXTENTS 2147483645

 )

   NOCACHE; 

 

 

CREATE UNIQUE INDEX TPOW_UPL ON 

  TPOW(TRADE_CPTY, TRADE_BOOK, TPOW_STARTDATE, TPOW_ENDDATE, TRADE_TNUM)


  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 14680064 NEXT
PCTINCREASE  ) 

; 

 

CREATE UNIQUE INDEX TPOW_TDT ON 

  TPOW(TRADE_TDATE, TRADE_BOOK, TRADE_TNUM) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 8388608 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW1 ON 

  TPOW(TRADE_TORG) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 5242880 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_SCHEDIMPORT ON 

  TPOW(TRADE_TRDTYP, TPOW_OPT1TYPE, TPOW_SCHED, AUDIT_ACTIVE,
AUDIT_AOHM, TPOW_STARTDATE, TPOW_ENDDATE, TPOW_TRADETYPE) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 13631488 NEXT
PCTINCREASE  ) 

; 

 

CREATE UNIQUE INDEX TPOW_BOOK ON 

  TPOW(TRADE_BOOK, TRADE_TNUM) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 6291456 NEXT
PCTINCREASE  ) 

; 

 

CREATE UNIQUE INDEX TPOW ON 

  TPOW(TRADE_TNUM) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 5242880 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_TPOW_MKT ON 

  TPOW(TPOW_MKT) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 5242880 NEXT
PCTINCREASE  ) 

; 

 

CREATE UNIQUE INDEX TPOW_WHATIF ON 

  TPOW(TRADE_WHATIF, TRADE_TNUM) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 6291456 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_STARTDATE ON 

  TPOW(TPOW_STARTDATE) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 6291456 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_DATES ON 

  TPOW(TPOW_DATES) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 6291456 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_ENDDATE ON 

  TPOW(TPOW_ENDDATE) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 6291456 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_AOHM ON 

  TPOW(AUDIT_AOHM, TRADE_TNUM) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 7340032 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_SHAPE ON 

  TPOW(TPOW_TRADETYPE, TRADE_XREF, TRADE_CPTY, TRADE_BOOK) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 11534336 NEXT
PCTINCREASE  ) 

; 

 

CREATE UNIQUE INDEX TPOW_TRDTYP ON 

  TPOW(TRADE_TRDTYP, TRADE_TNUM, TPOW_TRADETYPE, TPOW_STARTDATE,
TPOW_ENDDATE, TRADE_CPTY, TRADE_BOOK) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 15728640 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_ZKEY ON 

  TPOW(AUDIT_ZKEY, AUDIT_REFRESH) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 6291456 NEXT
PCTINCREASE  ) 

; 

 

CREATE UNIQUE INDEX TPOW_ALF ON 

  TPOW(AUDIT_ALF, TRADE_TNUM) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 7340032 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_XKEY ON 

  TPOW(AUDIT_XKEY, AUDIT_UTIL2, AUDIT_REFRESH) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 8388608 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_R ON 

  TPOW(AUDIT_REFRESH) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 5242880 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_TRADELIQ ON 

  TPOW(TRADE_TRDTYP, TPOW_TRADETYPE, TPOW_BCOUNT, AUDIT_ALF, AUDIT_AOHM)


  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 10485760 NEXT
PCTINCREASE  ) 

; 

 

CREATE UNIQUE INDEX TPOW_CPTY ON 

  TPOW(TRADE_CPTY, TRADE_TNUM) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 8388608 NEXT
PCTINCREASE  ) 

; 

 

CREATE UNIQUE INDEX TPOW_XREF ON 

  TPOW(TRADE_XREF, TRADE_TNUM) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 8388608 NEXT
PCTINCREASE  ) 

; 

 

CREATE INDEX TPOW_TNUMGROUP ON 

  TPOW(TPOW_TNUMGROUP) 

  TABLESPACE ZNSHELL PCTFREE 10  STORAGE(INITIAL 5242880 NEXT
PCTINCREASE  ) 

; 

 

 

 

Please have a look

 

________________________________

From: Nathan Maves [mailto:nathan.maves@gmail.com] 
Sent: Thursday, January 31, 2008 12:12 AM
To: user-java@ibatis.apache.org
Subject: Re: iBATIS QUERRY TAKE LONG TIME AS COMAPARED TO NORMAL JDBC

 


Yuvraj ,

The ibatis community would need a while lot more information from you to
even start to assess your issue.

You gave us nothing to start with so our response will not be much more
then that.

Nathan

On Jan 29, 2008 5:14 AM, Niels Beekman <n.beekman@wis.nl> wrote:

Maybe you're typing your queries with CAPS-LOCK while your database is
case-sensitive?

 

________________________________

From: Yuvraj Shinde [mailto:Yuvraj.Shinde@sos.sungard.com] 
Sent: Tuesday, January 29, 2008 10:46 AM
To: user-java@ibatis.apache.org
Subject: iBATIS QUERRY TAKE LONG TIME AS COMAPARED TO NORMAL JDBC

 

 

 HI ALL,

 

   I WANT TO KNOW HOW TO IMPROVE THE PERFORMANCE OF SELECT QUEERY WHICH
HAS HUGE DATA.CAN SOME BODY PROVIDE ME SOLUTION

 

 

REGARDS

YUVRAJS

 

 

 


Mime
View raw message