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
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 messageGo to next message
John Watson
Messages: 8922
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 #550398 is a reply to message #550394] Mon, 09 April 2012 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there a faster way to improve this


Just a single statement:
INSERT INTO ... SELECT ...

Regards
Michel
Re: Bulk Inserts [message #550402 is a reply to message #550394] Mon, 09 April 2012 04:53 Go to previous messageGo to next message
John Watson
Messages: 8922
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 messageGo to next message
cookiemonster
Messages: 13917
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 messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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
Littlefoot
Messages: 21806
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?
Previous Topic: tuning against count with group by clause
Next Topic: sql_id and sql_child_id not are null for SQL statement
Goto Forum:
  


Current Time: Thu Mar 28 19:39:26 CDT 2024