Home » RDBMS Server » Performance Tuning » Bulk Inserts (Oracle 10.1.0.2)
Bulk Inserts [message #550394] |
Mon, 09 April 2012 04:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
I am running one report and due to improve the speed of the reports i am inserting data into a temporary table and then pulling this data to run the report.The problem is its taking long time for inserts as there are multiple records , is there a faster way to improve this . my code is .
PROCEDURE ins_mat_data
IS
CURSOR c1
IS
SELECT mii_acth_code jci_str_no, bsl_txn_code jci_mih_txn_code,
bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
SUBSTR (item_short_name,1,DECODE (INSTR (item_short_name, 'x'),0,
LENGTH (item_short_name),INSTR (item_short_name, 'x') - 1)) item_short_name,
item_ig_code, item_anly_code_01, item_anly_code_02,
NVL (bsl_qty_bu, 0) / 1000 jci_wt_kg, bsl_cost_1 jci_cost,
bsl_val_1 jci_val, 0 jcrf_wt_kg, 0 jcrf_val, 0 jcro_wt_kg,
0 jcro_val
FROM os_batch_stk_ledger, om_item, ot_mat_iss_head, ot_mat_iss_item
WHERE bsl_batch_no = :rep_value_1
AND mih_sys_id = mii_mih_sys_id
AND mih_charge_code = :rep_value_1
AND mii_acth_code BETWEEN :rep_value_3 AND :rep_value_4
AND bsl_i_sys_id = mii_sys_id
AND bsl_h_sys_id = mii_mih_sys_id
AND mii_item_code = bsl_item_code
AND bsl_item_code = item_code
AND bsl_txn_code IN ('SS-MIS', 'SC-MIS')
AND ( item_ig_code BETWEEN '11' AND '19'
OR item_ig_code BETWEEN '71' AND '79'
OR item_ig_code BETWEEN '91' AND '96'
)
UNION ALL
SELECT mii_acth_code jci_str_no, bsl_txn_code jci_mih_txn_code,
bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
SUBSTR (item_short_name,1,DECODE (INSTR (item_short_name, 'x'),0,
LENGTH (item_short_name),INSTR (item_short_name, 'x') - 1)) item_short_name,
item_ig_code, item_anly_code_01, item_anly_code_02, 0 jci_wt_kg,
bsl_cost_1 jci_cost, 0 jci_val, 0 jcrf_wt_kg, 0 jcrf_val,
NVL (bsl_qty_bu, 0) / 1000 jcro_wt_kg, bsl_val_1 jcro_val
FROM os_batch_stk_ledger, om_item, ot_mat_iss_head, ot_mat_iss_item
WHERE bsl_batch_no = :rep_value_1
AND mih_sys_id = mii_mih_sys_id
AND mih_charge_code = :rep_value_1
AND mii_acth_code BETWEEN :rep_value_3 AND :rep_value_4
AND bsl_i_sys_id = mii_sys_id
AND bsl_h_sys_id = mii_mih_sys_id
AND mii_item_code = bsl_item_code
AND bsl_item_code = item_code
AND bsl_txn_code IN ('SS-MIO', 'S-MIOC')
AND ( item_ig_code BETWEEN '11' AND '19'
OR item_ig_code BETWEEN '71' AND '79'
OR item_ig_code BETWEEN '91' AND '96'
)
UNION ALL
SELECT mri_acth_code jci_str_no, bsl_txn_code jci_mih_txn_code,
bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
item_flex_06 item_short_name, item_ig_code, item_anly_code_01,
item_anly_code_02, 0 jci_wt_kg, bsl_cost_1 jci_cost, 0 jci_val,
NVL (bsl_qty_bu, 0) / 1000 jcrf_wt_kg, bsl_val_1 jcrf_val,
0 jcro_wt_kg, 0 jcro_val
FROM os_batch_stk_ledger, om_item, ot_mat_ret_head, ot_mat_ret_item
WHERE bsl_batch_no = :rep_value_1
AND mrh_sys_id = mri_mrh_sys_id
AND mrh_charge_code = :rep_value_1
AND mri_acth_code BETWEEN :rep_value_3 AND :rep_value_4
AND bsl_i_sys_id = mri_sys_id
AND bsl_h_sys_id = mri_mrh_sys_id
AND mri_item_code = bsl_item_code
AND bsl_item_code = item_code
AND bsl_txn_code IN ('SS-MTR')
AND ( item_ig_code BETWEEN '11' AND '19'
OR item_ig_code BETWEEN '71' AND '79'
OR item_ig_code BETWEEN '91' AND '96'
)
UNION ALL
SELECT adjh_flex_05 jci_str_no, bsl_txn_code jci_mih_txn_code,
bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
SUBSTR (item_short_name,1,DECODE (INSTR (item_short_name, 'x'),0,
LENGTH (item_short_name),INSTR (item_short_name, 'x') - 1)) item_short_name,
item_ig_code, item_anly_code_01,
item_anly_code_02, 0 jci_wt_kg, bsl_cost_1 jci_cost, 0 jci_val,
NVL (bsl_qty_bu, 0) / 1000 jcrf_wt_kg, bsl_val_1 jcrf_val,
0 jcro_wt_kg, 0 jcro_val
FROM os_batch_stk_ledger, om_item, ot_adj_head, ot_adj_item
WHERE bsl_batch_no = :rep_value_1
AND adjh_sys_id = adji_adjh_sys_id
AND adjh_flex_02 = :rep_value_1
AND adjh_flex_05 BETWEEN :rep_value_3 AND :rep_value_4
AND bsl_i_sys_id = adji_sys_id
AND bsl_h_sys_id = adji_adjh_sys_id
AND adji_item_code = bsl_item_code
AND bsl_item_code = item_code
AND bsl_txn_code IN ('SS-MRC')
AND ( item_ig_code BETWEEN '11' AND '19'
OR item_ig_code BETWEEN '71' AND '79'
OR item_ig_code BETWEEN '91' AND '96'
)
UNION ALL
SELECT adjh_flex_05 jci_str_no, bsl_txn_code jci_mih_txn_code,
bsl_no jci_mih_no, bsl_item_code jci_mii_item_code,
SUBSTR (item_short_name,1,DECODE (INSTR (item_short_name, 'x'),0,
LENGTH (item_short_name),INSTR (item_short_name, 'x') - 1)) item_short_name,
item_ig_code, item_anly_code_01,
item_anly_code_02, 0 jci_wt_kg, bsl_cost_1 jci_cost, 0 jci_val,
0 jcrf_wt_kg, 0 jcrf_val, NVL (bsl_qty_bu, 0) / 1000 jcro_wt_kg,
bsl_val_1 jcro_val
FROM os_batch_stk_ledger, om_item, ot_adj_head, ot_adj_item
WHERE bsl_batch_no = :rep_value_1
AND adjh_sys_id = adji_adjh_sys_id
AND adjh_flex_02 = :rep_value_1
AND adjh_flex_05 BETWEEN :rep_value_3 AND :rep_value_4
AND bsl_i_sys_id = adji_sys_id
AND bsl_h_sys_id = adji_adjh_sys_id
AND adji_item_code = bsl_item_code
AND bsl_item_code = item_code
AND bsl_txn_code IN ('SSMRO')
AND ( item_ig_code BETWEEN '11' AND '19'
OR item_ig_code BETWEEN '71' AND '79'
OR item_ig_code BETWEEN '91' AND '96'
);
BEGIN
FOR i IN c1
LOOP
INSERT INTO ow_mat_usage_temp
(jci_str_no, jci_mih_txn_code, jci_mih_no,
jci_mii_item_code, item_short_name, item_ig_code,
item_anly_code_01, item_anly_code_02, jci_wt_kg,
jci_cost, jci_val, jcrf_wt_kg, jcrf_val,
jcro_wt_kg, jcro_val
)
VALUES (i.jci_str_no, i.jci_mih_txn_code, i.jci_mih_no,
i.jci_mii_item_code, i.item_short_name, i.item_ig_code,
i.item_anly_code_01, i.item_anly_code_02, i.jci_wt_kg,
i.jci_cost, i.jci_val, i.jcrf_wt_kg, i.jcrf_val,
i.jcro_wt_kg, i.jcro_val
);
END LOOP;
END;
[Updated on: Mon, 09 April 2012 04:42] by Moderator Report message to a moderator
|
|
|
Re: Bulk Inserts [message #550397 is a reply to message #550394] |
Mon, 09 April 2012 04:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Two things jump out:
First, You have not given the tables aliases. This means that your predicate, which is a jumble of filters and joins in no order, is incomprehensible. I would strongly advise re-writing the query to use ANSI join syntax, which will give a clean separation between join and filter conditions, and to use table aliases.
Second, you are inserting the rows one at a time in a loop. It will be astronomically faster to do it in a single SQL statement: do not use pl/sql at all.
[Updated on: Mon, 09 April 2012 04:43] Report message to a moderator
|
|
|
|
Re: Bulk Inserts [message #550402 is a reply to message #550394] |
Mon, 09 April 2012 04:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
And another thing:
I put your code through the formatter on http://www.dpriver.com/pp/sqlformat.htm which makes it a bit easier to read, and it looks to me as though you are unioning 5 queries that are actually identical except for one filter, variations on this:
AND bsl_txn_code IN ( 'SS-MIS', 'SC-MIS' )
so you are in effect running the same query 5 times, when (if you use an OR to merge the queries) once would do.
|
|
|
Re: Bulk Inserts [message #550495 is a reply to message #550402] |
Mon, 09 April 2012 15:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Are you really sure you need to insert into a temporary table at all?
That very rarely improves performance.
|
|
|
Re: Bulk Inserts [message #550510 is a reply to message #550495] |
Tue, 10 April 2012 00:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
I agree with you cookiemonster but what to do i need to link mulitple tables at one time by using union and very strange that when i run the query in sql or toad its faster but when run from reports 6i thru form interface it slows down a lot , ofcourse there is a formula column.
|
|
|
Re: Bulk Inserts [message #550523 is a reply to message #550510] |
Tue, 10 April 2012 01:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
What does a formula column do? Could you calculate that value in SELECT statement itself?
|
|
|
Goto Forum:
Current Time: Sat Sep 07 23:39:48 CDT 2024
|