各位朋友,我现在写了一个存储过程,以前是用java调SQL写得,在处理小量数据(几KB)的时候,速度会比用java写得程序快一倍
但是数据量稍微增大(几十KB)的时候,速度会比java写得程序慢很多,也是1-2倍的样子吧,请问这事为什么啊?
代码中很多用到了如下的代码格式
INSERT INTO T030_SHIPMENT_REQ_IMP_ERR
(T030_CREATE_YMD,
T030_UPDATE_YMD,
T030_TRAN_KBN,
T030_TANTO_CD,
T030_TERM_CD,
T030_WAREH_CD,
T030_OWNER_CD,
T030_GET_ID,
T030_SEND_NO,
T030_ERR_LINE,
T030_ERR_COL,
T030_ERR_CD,
T030_ERR_TXT,
T030_ERR_VAL,
T030_MEMO)
SELECT TO_CHAR(SYSDATE, 'yyyyMMddhh24miss'),
TO_CHAR(SYSDATE, 'yyyyMMddhh24miss'),
'1',
'System',
null,
AR.T501_WAREH_CD,
AR.T501_OWNER_CD,
'99999999999',
T501_SLIP_NO,
AR.num,
'',
'1',
'行番号に値がありません',
AR.T501_LINE_NO,
''
from (SELECT rownum as num,
T501_WAREH_CD,
T501_OWNER_CD,
T501_LINE_NO,
T501_SLIP_NO
FROM T501_AEON_ORDER
LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =
M029_DESTINATION.M029_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M029_DESTINATION.M029_OWNER_CD
AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
AND M029_DESTINATION.M029_TRAN_KBN < '3'
WHERE T501_WAREH_CD = p_Wareh_CD
AND T501_OWNER_CD = p_owner_CD
AND T501_CUSTOM_CD2 = p_custom_cd2
AND T501_SHOP_CD = p_shop_cd
AND T501_DELI_PLAN_YMD = p_deli_plan_YMD
AND T501_ORDER_KBN = p_order_kbn
AND T501_RESULT_FLG = '2'
AND T501_TRAN_KBN < '3'
ORDER BY T501_CUSTOM_CD2,
T501_SHOP_CD,
T501_DELI_PLAN_YMD,
T501_ORDER_KBN,
T501_SLIP_NO,
T501_LINE_NO) AR
WHERE AR.T501_LINE_NO IS NULL;还有这种
SELECT
'System'
FROM T501_AEON_ORDER
LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =
M029_DESTINATION.M029_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M029_DESTINATION.M029_OWNER_CD
AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
AND M029_DESTINATION.M029_TRAN_KBN < '3'
LEFT JOIN M028_CUSTOM ON T501_AEON_ORDER.T501_WAREH_CD =
M028_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M028_OWNER_CD
AND M028_CUSTOM_CD =
RPAD(T501_AEON_ORDER.T501_CUSTOM_CD2,
12,
' ')
WHERE ROWNUM = 1
AND T501_WAREH_CD = p_wareh_cd
AND T501_OWNER_CD = p_owner_cd
AND T501_CUSTOM_CD2 = p_custom_cd2
AND T501_SHOP_CD = p_shop_cd
AND T501_DELI_PLAN_YMD = p_deli_plan_ymd
AND T501_ORDER_KBN = p_order_kbn
AND T501_RESULT_FLG = '2'
AND T501_TRAN_KBN < '3'
---------------------------------------
AND T501_AEON_ORDER.T501_SLIP_NO NOT IN
(SELECT T030_SHIPMENT_REQ_IMP_ERR.T030_Send_No
FROM T030_SHIPMENT_REQ_IMP_ERR
WHERE T030_SHIPMENT_REQ_IMP_ERR.T030_GET_ID = p_get_id)
AND T501_AEON_ORDER.T501_SLIP_NO IN
(SELECT column_value FROM TABLE(CAST(p_slip_NO_array AS T_AEON_SLIP_NO)))
---------------------------------------
ORDER BY T501_CUSTOM_CD2,
T501_SHOP_CD,
T501_DELI_PLAN_YMD,
T501_ORDER_KBN,
T501_SLIP_NO,
T501_LINE_NO;请大家予以指点啊!~!!
但是数据量稍微增大(几十KB)的时候,速度会比java写得程序慢很多,也是1-2倍的样子吧,请问这事为什么啊?
代码中很多用到了如下的代码格式
INSERT INTO T030_SHIPMENT_REQ_IMP_ERR
(T030_CREATE_YMD,
T030_UPDATE_YMD,
T030_TRAN_KBN,
T030_TANTO_CD,
T030_TERM_CD,
T030_WAREH_CD,
T030_OWNER_CD,
T030_GET_ID,
T030_SEND_NO,
T030_ERR_LINE,
T030_ERR_COL,
T030_ERR_CD,
T030_ERR_TXT,
T030_ERR_VAL,
T030_MEMO)
SELECT TO_CHAR(SYSDATE, 'yyyyMMddhh24miss'),
TO_CHAR(SYSDATE, 'yyyyMMddhh24miss'),
'1',
'System',
null,
AR.T501_WAREH_CD,
AR.T501_OWNER_CD,
'99999999999',
T501_SLIP_NO,
AR.num,
'',
'1',
'行番号に値がありません',
AR.T501_LINE_NO,
''
from (SELECT rownum as num,
T501_WAREH_CD,
T501_OWNER_CD,
T501_LINE_NO,
T501_SLIP_NO
FROM T501_AEON_ORDER
LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =
M029_DESTINATION.M029_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M029_DESTINATION.M029_OWNER_CD
AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
AND M029_DESTINATION.M029_TRAN_KBN < '3'
WHERE T501_WAREH_CD = p_Wareh_CD
AND T501_OWNER_CD = p_owner_CD
AND T501_CUSTOM_CD2 = p_custom_cd2
AND T501_SHOP_CD = p_shop_cd
AND T501_DELI_PLAN_YMD = p_deli_plan_YMD
AND T501_ORDER_KBN = p_order_kbn
AND T501_RESULT_FLG = '2'
AND T501_TRAN_KBN < '3'
ORDER BY T501_CUSTOM_CD2,
T501_SHOP_CD,
T501_DELI_PLAN_YMD,
T501_ORDER_KBN,
T501_SLIP_NO,
T501_LINE_NO) AR
WHERE AR.T501_LINE_NO IS NULL;还有这种
SELECT
'System'
FROM T501_AEON_ORDER
LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =
M029_DESTINATION.M029_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M029_DESTINATION.M029_OWNER_CD
AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
AND M029_DESTINATION.M029_TRAN_KBN < '3'
LEFT JOIN M028_CUSTOM ON T501_AEON_ORDER.T501_WAREH_CD =
M028_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M028_OWNER_CD
AND M028_CUSTOM_CD =
RPAD(T501_AEON_ORDER.T501_CUSTOM_CD2,
12,
' ')
WHERE ROWNUM = 1
AND T501_WAREH_CD = p_wareh_cd
AND T501_OWNER_CD = p_owner_cd
AND T501_CUSTOM_CD2 = p_custom_cd2
AND T501_SHOP_CD = p_shop_cd
AND T501_DELI_PLAN_YMD = p_deli_plan_ymd
AND T501_ORDER_KBN = p_order_kbn
AND T501_RESULT_FLG = '2'
AND T501_TRAN_KBN < '3'
---------------------------------------
AND T501_AEON_ORDER.T501_SLIP_NO NOT IN
(SELECT T030_SHIPMENT_REQ_IMP_ERR.T030_Send_No
FROM T030_SHIPMENT_REQ_IMP_ERR
WHERE T030_SHIPMENT_REQ_IMP_ERR.T030_GET_ID = p_get_id)
AND T501_AEON_ORDER.T501_SLIP_NO IN
(SELECT column_value FROM TABLE(CAST(p_slip_NO_array AS T_AEON_SLIP_NO)))
---------------------------------------
ORDER BY T501_CUSTOM_CD2,
T501_SHOP_CD,
T501_DELI_PLAN_YMD,
T501_ORDER_KBN,
T501_SLIP_NO,
T501_LINE_NO;请大家予以指点啊!~!!
解决方案 »
- 到底应该怎么办?事业到底要怎么走?
- 工作中遇到一个sql,请教各位以下问题,点拨一下思路和解决办法,谢谢!!
- 求查询SQL优化
- 关于dbms_alert的一个问题
- 在ADO.Net中如何实验一个函数同时往两个表中插入数据
- 环境hp unix + oracle 8.1.7 装了多个数据库实例,如何指定一个实例启动,麻烦哪个大哥写下命令
- 求救触发器?急??????//
- 关于ORACLE里的DES加密算法--
- 急!!!怎样才能在Oracle 9i中设置大小写不敏感
- 怎么连接到oms
- 我用TOAD里面建表,插入数据提示都正常,但查询数据时提示:no row select.
- 求教动态SQL里的in条件如何绑定进去
难道能够在数据量增加的情况下,会越来越快!!!
目的是为了提高速度。
而且呢,使用小量数据的情况呢,存储过程的确比java程序要快。
可问题是使用大量的数据的情况下,存储过程反而比java程序慢了,
不清楚的地方在这,为什么反而慢了。谢谢各位朋友了
我不至于笨到认为数据量大,认为速度越来越快吧。你这样的回答未免。
我要说的是为什么
本来是比java快的,可后来为什么会比java慢了,希望您能明白。
我的程序开始也是存储过程比JAVA慢
例如:使用绑定变量和批量提交的java程序在效率上就很有可能高于一个不使用绑定变量或批量提交,又或者硬解析非常高的存储过程。效率一般来说都是相对的,不是说存储过程的效率一定高于java程序