有一段sql为了插入一张表,我在执行计划中执行用时0.03秒,放到程序中,用了10秒,搞不明白了。
有高手启发以下。
下面这个帖子,一并给分。
http://topic.csdn.net/u/20081222/16/0152f5e7-a95f-441d-bfd2-11a1df04bfc6.htmlINSERT INTO FKOGV3A02(
ID, SEQ_NO, BLOCK_SEQ_NO, DATA_DIV, USER_ID, KIND_CODE, DEBTOR_CODE,
USER_CODE, H_SEQNO, CODE_DIV, PAYER_CODE, PAYER_NAME, DUMMY_H1,
U_BANK_CODE, U_BANK_NAME, U_BRANCH_CODE, U_BRANCH_NAME, U_ACCOUNT_DIVISION,
U_ACCOUNT_NUMBER, PAY_DATE, SETTLEMENT_DATE, DUMMY_H2, REG_DATETIME,
DELIVERY_DATETIME, REGISTERED_PERSON, REGISTERED_DT, UPDATED_PERSON, UPDATED_DT)
SELECT 'HYY000420081222151025', RN1, ROWNUM-1 RN2, '1', 'HYY0004', KIND_CODE,
DEBTOR_CODE, USER_CODE, H_SEQNO, CODE_DIV, PAYER_CODE, PAYER_NAME, DUMMY_H1,
U_BANK_CODE, U_BANK_NAME, U_BRANCH_CODE, U_BRANCH_NAME, U_ACCOUNT_DIVISION,
U_ACCOUNT_NUMBER, PAY_DATE, SETTLEMENT_DATE, DUMMY_H2, REG_DATETIME,
DELIVERY_DATETIME,
'HYY0004', SYSDATE, 'HYY0004', SYSDATE
FROM (
SELECT KIND_CODE, ROWNUM-1 RN1, CODE_DIV, PAYER_CODE, PAYER_NAME, DUMMY_H1,
U_BANK_CODE, U_BANK_NAME, U_BRANCH_CODE, U_BRANCH_NAME, U_ACCOUNT_DIVISION,
U_ACCOUNT_NUMBER, REG_DATETIME, PAY_DATE, SETTLEMENT_DATE, DUMMY_H2,
T_COUNT, T_AMOUNT, DUMMY_T1,
USER_CODE, H_SEQNO, DEBTOR_CODE, DELIVERY_DATETIME
FROM (
SELECT B.KIND_CODE, B.CODE_DIV, B.PAYER_CODE, B.PAYER_NAME, B.DUMMY_H1,
B.U_BANK_CODE, B.U_BANK_NAME, B.U_BRANCH_CODE, B.U_BRANCH_NAME, B.U_ACCOUNT_DIVISION,
B.U_ACCOUNT_NUMBER, B.REG_DATETIME, B.PAY_DATE, B.SETTLEMENT_DATE, B.DUMMY_H2,
B.T_COUNT, TRIM(B.T_AMOUNT) AS T_AMOUNT, B.DUMMY_T1,
B.USER_CODE, B.H_SEQNO, B.DEBTOR_CODE, B.DELIVERY_DATETIME
FROM FKOGD24 B
INNER JOIN
(SELECT MAX(FACTORING_CODE) AS FACTORING_CODE, DEBTOR_CODE, USER_CODE, REG_DATETIME, H_SEQNO
FROM FKOGD25
WHERE FACTORING_CODE = 'HYY'
GROUP BY DEBTOR_CODE, USER_CODE, REG_DATETIME, H_SEQNO) A
ON B.FACTORING_CODE = A.FACTORING_CODE
AND B.DEBTOR_CODE = A.DEBTOR_CODE
AND B.USER_CODE = A.USER_CODE
AND B.REG_DATETIME = A.REG_DATETIME
AND B.H_SEQNO = A.H_SEQNO
WHERE B.FACTORING_CODE = 'HYY' AND
B.DELIVERY_STATUS = '0'
ORDER BY B.DEBTOR_CODE, B.REG_DATETIME, TO_NUMBER(B.H_SEQNO), B.PAY_DATE, B.SETTLEMENT_DATE
)
)
像'HYY0004'这样的固定值都是参数形式。我跟踪了下,
try {
pstmt = conn.prepareStatement(sql);
setParams(pstmt, params);
ret = pstmt.executeUpdate(); //这句耗时最多。
}
catch (SQLException e) {
throw new DBExecuteException(e);
}
pstmt.executeUpdate();就是最耗时的地方。高手能提示下吗
有高手启发以下。
下面这个帖子,一并给分。
http://topic.csdn.net/u/20081222/16/0152f5e7-a95f-441d-bfd2-11a1df04bfc6.htmlINSERT INTO FKOGV3A02(
ID, SEQ_NO, BLOCK_SEQ_NO, DATA_DIV, USER_ID, KIND_CODE, DEBTOR_CODE,
USER_CODE, H_SEQNO, CODE_DIV, PAYER_CODE, PAYER_NAME, DUMMY_H1,
U_BANK_CODE, U_BANK_NAME, U_BRANCH_CODE, U_BRANCH_NAME, U_ACCOUNT_DIVISION,
U_ACCOUNT_NUMBER, PAY_DATE, SETTLEMENT_DATE, DUMMY_H2, REG_DATETIME,
DELIVERY_DATETIME, REGISTERED_PERSON, REGISTERED_DT, UPDATED_PERSON, UPDATED_DT)
SELECT 'HYY000420081222151025', RN1, ROWNUM-1 RN2, '1', 'HYY0004', KIND_CODE,
DEBTOR_CODE, USER_CODE, H_SEQNO, CODE_DIV, PAYER_CODE, PAYER_NAME, DUMMY_H1,
U_BANK_CODE, U_BANK_NAME, U_BRANCH_CODE, U_BRANCH_NAME, U_ACCOUNT_DIVISION,
U_ACCOUNT_NUMBER, PAY_DATE, SETTLEMENT_DATE, DUMMY_H2, REG_DATETIME,
DELIVERY_DATETIME,
'HYY0004', SYSDATE, 'HYY0004', SYSDATE
FROM (
SELECT KIND_CODE, ROWNUM-1 RN1, CODE_DIV, PAYER_CODE, PAYER_NAME, DUMMY_H1,
U_BANK_CODE, U_BANK_NAME, U_BRANCH_CODE, U_BRANCH_NAME, U_ACCOUNT_DIVISION,
U_ACCOUNT_NUMBER, REG_DATETIME, PAY_DATE, SETTLEMENT_DATE, DUMMY_H2,
T_COUNT, T_AMOUNT, DUMMY_T1,
USER_CODE, H_SEQNO, DEBTOR_CODE, DELIVERY_DATETIME
FROM (
SELECT B.KIND_CODE, B.CODE_DIV, B.PAYER_CODE, B.PAYER_NAME, B.DUMMY_H1,
B.U_BANK_CODE, B.U_BANK_NAME, B.U_BRANCH_CODE, B.U_BRANCH_NAME, B.U_ACCOUNT_DIVISION,
B.U_ACCOUNT_NUMBER, B.REG_DATETIME, B.PAY_DATE, B.SETTLEMENT_DATE, B.DUMMY_H2,
B.T_COUNT, TRIM(B.T_AMOUNT) AS T_AMOUNT, B.DUMMY_T1,
B.USER_CODE, B.H_SEQNO, B.DEBTOR_CODE, B.DELIVERY_DATETIME
FROM FKOGD24 B
INNER JOIN
(SELECT MAX(FACTORING_CODE) AS FACTORING_CODE, DEBTOR_CODE, USER_CODE, REG_DATETIME, H_SEQNO
FROM FKOGD25
WHERE FACTORING_CODE = 'HYY'
GROUP BY DEBTOR_CODE, USER_CODE, REG_DATETIME, H_SEQNO) A
ON B.FACTORING_CODE = A.FACTORING_CODE
AND B.DEBTOR_CODE = A.DEBTOR_CODE
AND B.USER_CODE = A.USER_CODE
AND B.REG_DATETIME = A.REG_DATETIME
AND B.H_SEQNO = A.H_SEQNO
WHERE B.FACTORING_CODE = 'HYY' AND
B.DELIVERY_STATUS = '0'
ORDER BY B.DEBTOR_CODE, B.REG_DATETIME, TO_NUMBER(B.H_SEQNO), B.PAY_DATE, B.SETTLEMENT_DATE
)
)
像'HYY0004'这样的固定值都是参数形式。我跟踪了下,
try {
pstmt = conn.prepareStatement(sql);
setParams(pstmt, params);
ret = pstmt.executeUpdate(); //这句耗时最多。
}
catch (SQLException e) {
throw new DBExecuteException(e);
}
pstmt.executeUpdate();就是最耗时的地方。高手能提示下吗
解决方案 »
- 救急。。我感保证很多资深JAVA程序员答不上来的基础问题。。。
- 通DatagramSocket绑定不了对方端口的问题
- 右移相当于除2,除不尽怎么办?
- [设计一问]表现层where多条件如何和处理层通信
- 启动4个线程,前2个各循环10次,控制全局变量加1,后2个各循环10次,将全局变量减1,这个问题如何实现????
- UIManager.setLookAndFeel(looks[val].getClassName());这句话为什么会产生一个NullPointerException的例外?
- 求教如何从一个jpg图像里面提取其中一个非矩形的图像的方法。
- 对你们来讲一定简单
- 谁能告诉我 :系统属性是什么?
- 我的applet在client端的computer上不能正常显示,怎么办?
- 请各位再帮忙解决~(先谢谢了)
- cookies问题
多条的建议用事务
还有pstmt用完后要释放
在执行pstmt.executeUpdate();
用手动提交事务conn.Commit();
你看看你下面这代码,没有释放pstmt,conn,在执行数据更新时,能快起来吗?你试试我给你的说法看。
try {
pstmt = conn.prepareStatement(sql);
setParams(pstmt, params);
ret = pstmt.executeUpdate(); //这句耗时最多。
}
catch (SQLException e) {
throw new DBExecuteException(e);
}
还有,你在执行计划中执行用时肯定是比放到程序中用时少的,因为首先程序要执行,类的加载等,最后才执行select这类的语句,但是10s是很长了,这肯定是跟你的事务和没有释放资源有关系。
FKOGD24这张表60000条数据,FKOGD25这张表我造了3百万条数据,用时也是10秒多。我另外造了几批数据有的
1秒就完成了。所以我才搞不明白了,到底是哪里出了问题。
你这该插的不插,不插的狂插,呵呵,我认为是“FKOGD24这张表60000条数据”,“FKOGD25这张表我造了3百万条数据”这地方就是很大的问题,主要是事务没有控制好。
ON IBJ.FKOGV3A02
(ID, DATA_DIV)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
LOGGING今天一个朋友让我执行上面这个语句,就没问题了。高手能帮忙解释下吗。
这个朋友我不好细问的,呵呵,对ORACLE也不是太明白。
CREATE TABLE IBJ.FKOGD25
(
FACTORING_CODE CHAR(3) NOT NULL,
DEBTOR_CODE CHAR(2) NOT NULL,
USER_CODE CHAR(2) NOT NULL,
S_BANK_CODE VARCHAR2(8),
S_BANK_NAME VARCHAR2(30),
S_BRANCH_CODE VARCHAR2(6),
S_BRANCH_NAME VARCHAR2(30),
DUMMY_D1 VARCHAR2(8),
S_ACCOUNT_DIVISION VARCHAR2(2),
S_ACCOUNT_NUMBER VARCHAR2(14),
RECEIVED_NAME VARCHAR2(60),
AMOUNT CHAR(10),
DUMMY_D2 VARCHAR2(2),
MANAGED_NUM VARCHAR2(20),
DUMMY_D3 VARCHAR2(38),
REG_DATETIME CHAR(14) NOT NULL,
H_SEQNO CHAR(4) NOT NULL,
D_SEQNO CHAR(5) NOT NULL,
FINAL_UPDATETIME DATE NOT NULL,
EXCLUSIVEFG CHAR(32),
REGISTERED_PERSON VARCHAR2(17),
REGISTERED_DT DATE,
UPDATED_PERSON VARCHAR2(17),
UPDATED_DT DATE,
CONSTRAINT SYS_C003749 PRIMARY KEY (FACTORING_CODE, DEBTOR_CODE, USER_CODE, REG_DATETIME, H_SEQNO, D_SEQNO) USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 3M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
)
PCTFREE 10
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 6M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING
/
这是FKOGD25建表CREATE TABLE IBJ.FKOGD24
(
FACTORING_CODE CHAR(3) NOT NULL,
DEBTOR_CODE CHAR(2) NOT NULL,
USER_CODE CHAR(2) NOT NULL,
KIND_CODE VARCHAR2(4),
CODE_DIV VARCHAR2(2),
PAYER_CODE CHAR(10) NOT NULL,
PAYER_NAME VARCHAR2(80),
DUMMY_H1 VARCHAR2(8),
U_BANK_CODE VARCHAR2(8),
U_BANK_NAME VARCHAR2(30),
U_BRANCH_CODE VARCHAR2(6),
U_BRANCH_NAME VARCHAR2(30),
U_ACCOUNT_DIVISION VARCHAR2(2),
U_ACCOUNT_NUMBER VARCHAR2(14),
PAY_DATE CHAR(6) NOT NULL,
SETTLEMENT_DATE CHAR(6) NOT NULL,
DUMMY_H2 VARCHAR2(10),
T_COUNT CHAR(6) NOT NULL,
T_AMOUNT CHAR(12) NOT NULL,
DUMMY_T1 VARCHAR2(202),
REG_DATETIME CHAR(14) NOT NULL,
H_SEQNO CHAR(4) NOT NULL,
DELIVERY_STATUS CHAR(1) NOT NULL,
DELIVERY_DATETIME CHAR(14),
FINAL_UPDATETIME DATE NOT NULL,
UPLOAD_DATETIME DATE,
MAIL_STATUS CHAR(1) DEFAULT '0',
EXCLUSIVEFG CHAR(32),
REGISTERED_PERSON VARCHAR2(17),
REGISTERED_DT DATE,
UPDATED_PERSON VARCHAR2(17),
UPDATED_DT DATE,
CONSTRAINT SYS_C003741 PRIMARY KEY (FACTORING_CODE, DEBTOR_CODE, USER_CODE, REG_DATETIME, H_SEQNO) USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 320K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
)
PCTFREE 10
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 576K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING
/
这是FKOGD24建表
CREATE TABLE IBJ.FKOGD25
(
FACTORING_CODE CHAR(3) NOT NULL,
DEBTOR_CODE CHAR(2) NOT NULL,
USER_CODE CHAR(2) NOT NULL,
S_BANK_CODE VARCHAR2(8),
S_BANK_NAME VARCHAR2(30),
S_BRANCH_CODE VARCHAR2(6),
S_BRANCH_NAME VARCHAR2(30),
DUMMY_D1 VARCHAR2(8),
S_ACCOUNT_DIVISION VARCHAR2(2),
S_ACCOUNT_NUMBER VARCHAR2(14),
RECEIVED_NAME VARCHAR2(60),
AMOUNT CHAR(10),
DUMMY_D2 VARCHAR2(2),
MANAGED_NUM VARCHAR2(20),
DUMMY_D3 VARCHAR2(38),
REG_DATETIME CHAR(14) NOT NULL,
H_SEQNO CHAR(4) NOT NULL,
D_SEQNO CHAR(5) NOT NULL,
FINAL_UPDATETIME DATE NOT NULL,
EXCLUSIVEFG CHAR(32),
REGISTERED_PERSON VARCHAR2(17),
REGISTERED_DT DATE,
UPDATED_PERSON VARCHAR2(17),
UPDATED_DT DATE,
CONSTRAINT SYS_C003749 PRIMARY KEY (FACTORING_CODE, DEBTOR_CODE, USER_CODE, REG_DATETIME, H_SEQNO, D_SEQNO) USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 3M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
)
PCTFREE 10
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 6M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING
/
这是FKOGD25建表CREATE TABLE IBJ.FKOGD24
(
FACTORING_CODE CHAR(3) NOT NULL,
DEBTOR_CODE CHAR(2) NOT NULL,
USER_CODE CHAR(2) NOT NULL,
KIND_CODE VARCHAR2(4),
CODE_DIV VARCHAR2(2),
PAYER_CODE CHAR(10) NOT NULL,
PAYER_NAME VARCHAR2(80),
DUMMY_H1 VARCHAR2(8),
U_BANK_CODE VARCHAR2(8),
U_BANK_NAME VARCHAR2(30),
U_BRANCH_CODE VARCHAR2(6),
U_BRANCH_NAME VARCHAR2(30),
U_ACCOUNT_DIVISION VARCHAR2(2),
U_ACCOUNT_NUMBER VARCHAR2(14),
PAY_DATE CHAR(6) NOT NULL,
SETTLEMENT_DATE CHAR(6) NOT NULL,
DUMMY_H2 VARCHAR2(10),
T_COUNT CHAR(6) NOT NULL,
T_AMOUNT CHAR(12) NOT NULL,
DUMMY_T1 VARCHAR2(202),
REG_DATETIME CHAR(14) NOT NULL,
H_SEQNO CHAR(4) NOT NULL,
DELIVERY_STATUS CHAR(1) NOT NULL,
DELIVERY_DATETIME CHAR(14),
FINAL_UPDATETIME DATE NOT NULL,
UPLOAD_DATETIME DATE,
MAIL_STATUS CHAR(1) DEFAULT '0',
EXCLUSIVEFG CHAR(32),
REGISTERED_PERSON VARCHAR2(17),
REGISTERED_DT DATE,
UPDATED_PERSON VARCHAR2(17),
UPDATED_DT DATE,
CONSTRAINT SYS_C003741 PRIMARY KEY (FACTORING_CODE, DEBTOR_CODE, USER_CODE, REG_DATETIME, H_SEQNO) USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 320K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
)
PCTFREE 10
MAXTRANS 255
TABLESPACE IBJ_DATA
STORAGE(INITIAL 576K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING
/
这是FKOGD24建表
你要针对自己的sql来优化索引,索引太多查询快了,但是写入就会比较慢。自己权衡一下。