There is another nearly identical thread. I suggest you read it, too. My guess is your timing is either measuring just the preparedStatement.execute() call and/or that the object creation/population is taking up much of the time.
300,000 objects is quite a few...maybe you could use a rowhandler to minimize the impact some. ...or write a stored procedure to do the processing here? Larry On Jan 30, 2008 11:37 PM, Clinton Begin <[EMAIL PROTECTED]> wrote: > > > > > Are you kidding me? Is that an 8500 line JavaBean DTO? Good lord... Not a > criticism, just absolute, utter amazement. It doesn't look impossible to > normalize it, but that wouldn't improve performance, and my limited > knowledge of trading systems helps me to sympathize. > > > > In any case, does your JDBC test include instantiating those DTOs and > setting all of the properties on them? > > > > Cheers, > > Clinton > > > > > > From: Yuvraj Shinde [mailto:[EMAIL PROTECTED] > Sent: January-30-08 11:01 PM > > To: user-java@ibatis.apache.org > Subject: iBATIS QUERRY TAKE LONG TIME AS COMAPARED TO NORMAL JDBC > > > > > > 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 >= #FIRST_SELECTED# > > AND > > TPOW_STARTDATE <= #LAST_SELECTED# > > AND > > AUDIT_ALF <= #LOGINDATE:TIMESTAMP# > > AND > > AUDIT_AOHM > #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 ... > > [Message clipped]
