Home » SQL & PL/SQL » SQL & PL/SQL » can someone tell me why this sp takes so much time? (11g,toad 12.1)
| can someone tell me why this sp takes so much time? [message #610041] |
Mon, 17 March 2014 03:18  |
 |
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
When executing the below stored procedure it takes 20 mins .The expected time is 2-3 mins. why this is happening? can someone tell me why this sp takes so much time?what can be done to make it run faster? Thanks a lot in advance.The logic of this procedure is ,it checks whether the order number is present in the table if not then inserts into that table
Procedure code
CREATE OR REPLACE PROCEDURE sp_load_order
IS
l_ds_ds_id data_source.ds_id%TYPE;
l_carrier stage_na_shipment.haulier_name%TYPE;
l_vehicle_number stage_na_shipment.haulier_num%TYPE;
l_ship_date stage_na_shipment.ship_date`enter code here`%TYPE;
l_source_key VARCHAR2 (20);
l_source_key_1 VARCHAR2 (20);
l_quantity stage_na_shipment.quantity%TYPE;
l_ship_from stage_na_shipment.ship_from%TYPE;
l_prod_prod_id product.prod_prod_id%TYPE;
l_client stage_na_shipment.client%TYPE;
l_ord_id ordr.ord_id%TYPE;
l_sf_sf_id ship_from.sf_id%TYPE;
l_cust_cust_id customer.cust_cust_id%TYPE;
l_ord_stat_ord_stat_id ordr_status.ord_stat_id%TYPE;
l_le_le_id legal_entity.le_id%TYPE;
l_mkt_mkt_id market.mkt_id%TYPE;
l_code bill_type.code%TYPE;
l_ord_ord_id ordr.ord_id%TYPE;
l_ord_prod_id ordr_product.ord_prod_id%TYPE;
l_po_number stage_na_shipment.po_number%TYPE;
l_expct_ship_date stage_na_shipment.ship_date%TYPE;
l_bt_bt_id bill_type.bt_id%TYPE;
l_order_process_date stage_na_shipment.ship_date%TYPE;
l_storage_loc stage_na_shipment.stor_loc%TYPE;
l_ship_unit_code_8 stage_na_shipment.ship_unit_code_8%TYPE;
l_del_point stage_na_shipment.DEL_POINT%type;
l_r_num NUMBER;
n_1 NUMBER;
l_error_msg VARCHAR2 (500);
start_time timestamp;
end_time timestamp;
BEGIN
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('Procedure started' ||start_time);
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', 'START', NULL, NULL, NULL);
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('DS_ID start_time' ||start_time);
SELECT ds_id
INTO l_ds_ds_id
FROM data_source
WHERE ds_id = 1;
DBMS_OUTPUT.put_line ('DS_ID' || l_ds_ds_id);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('DS_ID start_time' ||end_time);
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('ord_stat_id start_time' ||start_time);
SELECT ord_stat_id
INTO l_ord_stat_ord_stat_id
FROM ordr_status
WHERE ord_stat_id = 2;
DBMS_OUTPUT.put_line ('ORD_STAT_ID' || l_ord_stat_ord_stat_id);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('ORD_STAT_ID end_time' ||end_time);
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('le_id start_time' ||start_time);
SELECT le_id
INTO l_le_le_id
FROM legal_entity
WHERE le_id = 1;
DBMS_OUTPUT.put_line ('LE_ID' || l_le_le_id);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('LE_ID end_time' ||end_time);
SELECT COUNT (*)
INTO l_r_num
FROM frontroom.ordr;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
'MESSAGE',
'FRONTROOM_BEF_INS',
l_r_num,
NULL
);
SELECT COUNT (*)
INTO l_r_num
FROM frontroom.ordr_product;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
'MESSAGE',
'ORDRPRODUCT_BEFORE_INS',
l_r_num,
NULL
);
DBMS_OUTPUT.put_line ('loop started' ||start_time);
FOR i IN (SELECT order_num_cos, ship_date, haulier_name, haulier_num,
po_number, stor_loc, ship_unit_code, ship_unit_code_8,
so_item, ship_from, del_point, currency, client, quantity
FROM stage_na_shipment)
LOOP
l_source_key := TO_CHAR (i.order_num_cos);
l_carrier := TO_CHAR (i.haulier_name);
l_vehicle_number := TO_CHAR (i.haulier_num);
l_source_key_1 := TO_CHAR (i.order_num_cos || i.so_item);
l_po_number := i.po_number;
l_storage_loc := TO_CHAR (i.stor_loc);
l_del_point :=i.del_point;
l_ship_date := i.ship_date;
l_ship_from := TO_CHAR (i.ship_from);
l_ship_unit_code_8 := TO_CHAR (i.ship_unit_code_8);
l_client := TO_CHAR (i.client);
DBMS_OUTPUT.put_line ('order_number' || i.order_num_cos);
BEGIN
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('mkt_id start_time' ||start_time);
SELECT mkt_id INTO l_mkt_mkt_id
FROM market
WHERE code = DECODE (SUBSTR (l_client, 1, 4), 'US23', 'MM',
'US99', 'AFFCO' );
DBMS_OUTPUT.put_line ('MKT_ID' || l_mkt_mkt_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, 'THE MKT ID NOT FOUND :',
NULL, NULL );
GOTO end_loop;
WHEN TOO_MANY_ROWS THEN
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, 'MKT ID TWO MANY ROWS FOUND :'||i.order_num_cos,
NULL, NULL );
WHEN others
THEN
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, SQLERRM, NULL, NULL );
END;
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('mkt_id end_time' ||end_time);
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('bt_id start_time' ||start_time);
BEGIN
IF i.quantity >= 0
THEN
l_code := 'O';
ELSE
l_code := 'C';
END IF;
SELECT bt_id INTO l_bt_bt_id FROM bill_type
WHERE code = l_code
AND ds_ds_id = l_ds_ds_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry
(SYSDATE,
'SP_LOAD_ORDER',
NULL,
'BILL TYPE ID NOT FOUND',
NULL,
NULL
);
GOTO end_loop;
WHEN TOO_MANY_ROWS THEN
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, 'BILL TYPE ID TWO MANY ROWS FOUND :'||i.order_num_cos,
NULL, NULL );
WHEN others
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
SQLERRM,
NULL,
NULL
);
END;
DBMS_OUTPUT.put_line ('BT_ID :' || l_bt_bt_id);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('BT_ID end_time' ||end_time);
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('prod_id start_time' ||start_time);
BEGIN
SELECT prod_prod_id
INTO l_prod_prod_id
FROM product
WHERE secondary_source_key = LTRIM (l_ship_unit_code_8, 0);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
'THE PRODUCT ID ' ||i.ship_unit_code_8 ||' could not be found in product table :',
'order_num_cos = ' || i.order_num_cos
,
NULL
);
GOTO end_loop;
WHEN TOO_MANY_ROWS THEN
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, 'THE PRODUCT ID TWO MANY ROWS FOUND :' ||i.order_num_cos,
NULL, NULL );
WHEN OTHERS
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
SQLERRM,
i.ship_unit_code_8 || SQLERRM,
NULL
);
END;
DBMS_OUTPUT.put_line (' PROD_PROD_ID :' || l_prod_prod_id);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('PROD_ID end_time' ||end_time);
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('sf_id start_time' ||start_time);
BEGIN
SELECT sf_id
INTO l_sf_sf_id
FROM ship_from
WHERE source_key = l_ship_from;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
'sf_id NOT FOUND',
NULL,
NULL
);
GOTO end_loop;
WHEN TOO_MANY_ROWS THEN
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, 'sf_id TWO MANY ROWS FOUND :'||i.order_num_cos,
NULL, NULL );
WHEN others
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
SQLERRM,
NULL,
NULL
);
END;
DBMS_OUTPUT.put_line ('SF_ID :' || l_sf_sf_id);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('sf_id end_time' ||end_time);
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('cust_id start_time' ||start_time);
BEGIN
SELECT cust_id
INTO l_cust_cust_id
FROM customer
WHERE true_gcdb_source_key = to_char(i.del_point);
-- AND ds_ds_id = l_ds_ds_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
'THE CUST ID ' || l_del_point || 'COULD NOT BE FOUND IN THE CUSTOMER TABLE''order_num_cos = '||i.order_num_cos,
NULL,
NULL
);
GOTO end_loop;
WHEN TOO_MANY_ROWS THEN
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, 'cust_id TWO MANY ROWS FOUND :'||i.order_num_cos,
NULL, NULL );
WHEN others
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
SQLERRM,
NULL,
NULL
);
END;
DBMS_OUTPUT.put_line ('CUST_ID :' || l_cust_cust_id);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('CUST_ID end_time' ||end_time);
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('INSERTING INTO frontroom.ordr' ||start_time);
BEGIN
INSERT INTO frontroom.ordr
(ord_number, ds_ds_id, erlst_ship_dt, carrier,
vehicle_number, ord_dt, source_key, sf_sf_id,
cust_cust_id, ord_stat_ord_stat_id, le_le_id,
mkt_mkt_id, sc_sc_id, ctrl_dt, po_number,
expct_ship_date, bt_bt_id, order_process_dt,
storage_loc)
SELECT l_source_key, l_ds_ds_id, i.ship_date, l_carrier,
l_vehicle_number, i.ship_date, l_source_key, l_sf_sf_id,
l_cust_cust_id, l_ord_stat_ord_stat_id, l_le_le_id,
l_mkt_mkt_id, 0, i.ship_date, l_po_number, i.ship_date,
l_bt_bt_id, i.ship_date, l_storage_loc
FROM DUAL
WHERE NOT EXISTS (SELECT 1--ord_number
FROM frontroom.ordr
WHERE ord_number = l_source_key);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'order_num_cos = ' || i.order_num_cos,
NULL
);
GOTO end_loop;
END;
DBMS_OUTPUT.put_line ('Inserted into order table');
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('Inserted into order table' ||end_time);
SELECT ord_id
INTO l_ord_ord_id
FROM frontroom.ordr
WHERE ord_number = l_source_key;
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('INSERTING INTO frontroom.ordr_product' ||start_time);
BEGIN
INSERT INTO ordr_product
(volume, revenue, source_key, ord_ord_id, prod_prod_id,
local_currency)
SELECT i.ship_unit_code, 0, l_source_key_1, l_ord_ord_id,
l_prod_prod_id, i.currency
FROM DUAL
WHERE NOT EXISTS (
SELECT 1--ord_ord_id
FROM ordr_product
WHERE ord_ord_id =
(SELECT ord_id
FROM ordr
WHERE ord_number = l_source_key));
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'order_num_cos = ' || i.order_num_cos,
NULL
);
GOTO end_loop;
END;
n_1 := n_1 + 1;
if n_1 = 100 then
COMMIT;
n_1 := 0;
end if;
<<end_loop>>
null;
END LOOP;
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('Inserted into order table' ||end_time);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('Loop ends' ||end_time);
SELECT COUNT (*)
INTO l_r_num
FROM frontroom.ordr;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
'MESSAGE',
'FRONTROOM_AFTER_INS',
l_r_num,
NULL
);
SELECT COUNT (*)
INTO l_r_num
FROM frontroom.ordr_product;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
'MESSAGE',
'ORDRPRODUCT_AFTER_INS',
l_r_num,
NULL
);
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', 'COMPLETE', NULL, NULL, NULL);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('Procedure ends' ||end_time);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, l_error_msg, NULL, NULL);
END sp_load_order;
/
[EDITED by LF: applied [spoiler] tags to save everyone from too much scrolling]
[Updated on: Mon, 17 March 2014 06:12] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610046 is a reply to message #610041] |
Mon, 17 March 2014 03:33  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It looks possible that the whole procedure has a number of fundamental flaws. In no particular order:
Your use of WHEN OTHERS is going to make de-bugging very difficult.
The row-by-row procedural processing of your data set is always going to perform worse than using SQL to operate on the complete set of rows in one operation.
The manner in which you run queries at various points is liable to give inconsistent results of any other sessions are connected while running this procedure.
The complete absence of comments in the code makes it hard to work out what you are trying to do.
All that having been said, you have placed many dbms_output calls in the code, but you have omitted to show the results. If you can show the output, with how many rows and which part is slow, perhaps someone can advise. You might want to consider using dbms_hprof or dbms_profiler to determine where the time is being spent.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610087 is a reply to message #610067] |
Mon, 17 March 2014 08:40 |
|
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
The changes made to the existing code.now the time got reduced by 8 times.but thats not enough
BEGIN
INSERT INTO frontroom.ordr
(ord_number, ds_ds_id, erlst_ship_dt, carrier,
vehicle_number, ord_dt, source_key, sf_sf_id,
cust_cust_id, ord_stat_ord_stat_id, le_le_id,
mkt_mkt_id, sc_sc_id, ctrl_dt, po_number,
expct_ship_date, bt_bt_id, order_process_dt,
storage_loc)
VALUES(l_source_key, l_ds_ds_id, i.ship_date, l_carrier,
l_vehicle_number, i.ship_date, l_source_key, l_sf_sf_id,
l_cust_cust_id, l_ord_stat_ord_stat_id, l_le_le_id,
l_mkt_mkt_id, 0, i.ship_date, l_po_number, i.ship_date,
l_bt_bt_id, i.ship_date, l_storage_loc);
COMMIT;
/* SELECT l_source_key, l_ds_ds_id, i.ship_date, l_carrier,
l_vehicle_number, i.ship_date, l_source_key, l_sf_sf_id,
l_cust_cust_id, l_ord_stat_ord_stat_id, l_le_le_id,
l_mkt_mkt_id, 0, i.ship_date, l_po_number, i.ship_date,
l_bt_bt_id, i.ship_date, l_storage_loc
FROM DUAL
WHERE NOT EXISTS (SELECT 1--ord_number
FROM frontroom.ordr
WHERE ord_number = l_source_key);*/
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'DUPLICATE RECORD FOUND FOR order_num_cos = ' || i.order_num_cos,
NULL
);
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'order_num_cos = ' || i.order_num_cos,
NULL
);
GOTO end_loop;
END; |
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610112 is a reply to message #610087] |
Mon, 17 March 2014 12:53 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You could post the results of the query, you know, with the timings.
I think your problem is now that you are inserting 500 rows in 500 statements. Row-by-row processing! SQL is a set oriented language, and you should use it accordingly. Do not read 500 rows into a cursor and insert them individually. Rather, insert all 500 in one statement:
INSERT INTO frontroom.ordr ... SELECT ... FROM stage_na_shipment;
|
|
|
|
|
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610160 is a reply to message #610145] |
Tue, 18 March 2014 03:25 |
|
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
here is the code
CREATE OR REPLACE PROCEDURE sp_load_order
IS
l_ds_ds_id data_source.ds_id%TYPE;
l_carrier stage_na_shipment.haulier_name%TYPE;
l_vehicle_number stage_na_shipment.haulier_num%TYPE;
l_ship_date stage_na_shipment.ship_date%TYPE;
l_source_key VARCHAR2 (20);
l_source_key_1 VARCHAR2 (20);
l_quantity stage_na_shipment.quantity%TYPE;
l_ship_from stage_na_shipment.ship_from%TYPE;
l_prod_prod_id product.prod_prod_id%TYPE;
l_client stage_na_shipment.client%TYPE;
--l_ord_id ordr.ord_id%TYPE;
l_sf_sf_id ship_from.sf_id%TYPE;
l_cust_cust_id customer.cust_cust_id%TYPE;
l_ord_stat_ord_stat_id ordr_status.ord_stat_id%TYPE;
l_le_le_id legal_entity.le_id%TYPE;
l_mkt_mkt_id market.mkt_id%TYPE;
l_code bill_type.code%TYPE;
l_ord_ord_id ordr.ord_id%TYPE;
l_ord_ord_id_1 ordr.ord_id%TYPE;
l_ord_prod_id ordr_product.ord_prod_id%TYPE;
l_po_number stage_na_shipment.po_number%TYPE;
l_expct_ship_date stage_na_shipment.ship_date%TYPE;
l_bt_bt_id bill_type.bt_id%TYPE;
l_order_process_date stage_na_shipment.ship_date%TYPE;
l_storage_loc stage_na_shipment.stor_loc%TYPE;
l_ship_unit_code_8 stage_na_shipment.ship_unit_code_8%TYPE;
l_del_point stage_na_shipment.DEL_POINT%type;
l_r_num NUMBER;
n_1 NUMBER;
l_error_msg VARCHAR2 (500);
start_time timestamp;
end_time timestamp;
BEGIN
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('Procedure started' ||start_time);
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', 'START', NULL, NULL, NULL);
SELECT ds_id
INTO l_ds_ds_id
FROM data_source
WHERE ds_id = 1;
SELECT ord_stat_id
INTO l_ord_stat_ord_stat_id
FROM ordr_status
WHERE ord_stat_id = 2;
SELECT le_id
INTO l_le_le_id
FROM legal_entity
WHERE le_id = 1;
SELECT COUNT (*)
INTO l_r_num
FROM frontroom.ordr;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
'MESSAGE',
'FRONTROOM_BEF_INS',
l_r_num,
NULL
);
SELECT COUNT (*)
INTO l_r_num
FROM frontroom.ordr_product;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
'MESSAGE',
'ORDRPRODUCT_BEFORE_INS',
l_r_num,
NULL
);
FOR i IN (SELECT order_num_cos, ship_date, haulier_name, haulier_num,
po_number, stor_loc, ship_unit_code, ship_unit_code_8,
so_item, ship_from, del_point, currency, client, quantity
FROM stage_na_shipment)
LOOP
l_source_key := TO_CHAR (i.order_num_cos);
l_carrier := TO_CHAR (i.haulier_name);
l_vehicle_number := TO_CHAR (i.haulier_num);
l_source_key_1 := TO_CHAR (i.order_num_cos || i.so_item);
l_po_number := i.po_number;
l_storage_loc := TO_CHAR (i.stor_loc);
l_del_point :=TO_CHAR (i.del_point);
l_ship_date := i.ship_date;
l_ship_from := TO_CHAR (i.ship_from);
l_ship_unit_code_8 := TO_CHAR (i.ship_unit_code_8);
l_client := TO_CHAR (i.client);
BEGIN
SELECT mkt_id INTO l_mkt_mkt_id
FROM market
WHERE code = DECODE (SUBSTR (l_client, 1, 4), 'US23', 'MM',
'US99', 'AFFCO' );
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, 'THE MKT ID NOT FOUND :',
NULL, NULL );
GOTO end_loop;
END;
BEGIN
IF i.quantity >= 0
THEN
l_code := 'O';
ELSE
l_code := 'C';
END IF;
SELECT bt_id INTO l_bt_bt_id FROM bill_type
WHERE code = l_code
AND ds_ds_id = l_ds_ds_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry
(SYSDATE,
'SP_LOAD_ORDER',
NULL,
'BILL TYPE ID NOT FOUND',
NULL,
NULL
);
GOTO end_loop;
END;
BEGIN
SELECT prod_prod_id
INTO l_prod_prod_id
FROM product
WHERE secondary_source_key = LTRIM (l_ship_unit_code_8, 0);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
'THE PRODUCT ID ' ||i.ship_unit_code_8 ||' could not be found in product table :',
'order_num_cos = ' || i.order_num_cos
,
NULL
);
GOTO end_loop;
END;
BEGIN
SELECT sf_id
INTO l_sf_sf_id
FROM ship_from
WHERE source_key = l_ship_from;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
'sf_id NOT FOUND',
NULL,
NULL
);
GOTO end_loop;
END;
DBMS_OUTPUT.put_line ('cust_id start_time' ||start_time);
BEGIN
SELECT cust_id
INTO l_cust_cust_id
FROM customer
WHERE true_gcdb_source_key = TO_CHAR (i.del_point);
-- AND ds_ds_id = l_ds_ds_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
'THE CUST ID ' || i.del_point || 'COULD NOT BE FOUND IN THE CUSTOMER TABLE''order_num_cos = '||i.order_num_cos,
NULL,
NULL
);
GOTO end_loop;
END;
BEGIN
INSERT INTO frontroom.ordr
(ord_number, ds_ds_id, erlst_ship_dt, carrier,
vehicle_number, ord_dt, source_key, sf_sf_id,
cust_cust_id, ord_stat_ord_stat_id, le_le_id,
mkt_mkt_id, sc_sc_id, ctrl_dt, po_number,
expct_ship_date, bt_bt_id, order_process_dt,
storage_loc)
VALUES(l_source_key, l_ds_ds_id, i.ship_date, l_carrier,
l_vehicle_number, i.ship_date, l_source_key, l_sf_sf_id,
l_cust_cust_id, l_ord_stat_ord_stat_id, l_le_le_id,
l_mkt_mkt_id, 0, i.ship_date, l_po_number, i.ship_date,
l_bt_bt_id, i.ship_date, l_storage_loc);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'DUPLICATE RECORD FOUND FOR order_num_cos = ' || i.order_num_cos,
NULL
);
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'order_num_cos = ' || i.order_num_cos,
NULL
);
GOTO end_loop;
END;
SELECT ord_id
INTO l_ord_ord_id
FROM frontroom.ordr
WHERE ord_number = l_source_key;
BEGIN
INSERT INTO ordr_product
(volume, revenue, source_key, ord_ord_id, prod_prod_id,
local_currency)
VALUES( i.ship_unit_code, 0, l_source_key_1, l_ord_ord_id,
l_prod_prod_id, i.currency);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'DUPLICATE RECORD FOUND FOR order_num_cos = ' || i.order_num_cos,
NULL
);
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'order_num_cos = ' || i.order_num_cos,
NULL
);
GOTO end_loop;
END;
n_1 := n_1 + 1;
if n_1 = 100 then
COMMIT;
n_1 := 0;
end if;
<<end_loop>>
null;
END LOOP;
SELECT COUNT (*)
INTO l_r_num
FROM frontroom.ordr;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
'MESSAGE',
'FRONTROOM_AFTER_INS',
l_r_num,
NULL
);
SELECT COUNT (*)
INTO l_r_num
FROM frontroom.ordr_product;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
'MESSAGE',
'ORDRPRODUCT_AFTER_INS',
l_r_num,
NULL
);
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', 'COMPLETE', NULL, NULL, NULL);
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('Procedure ends' ||end_time);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE, 'SP_LOAD_ORDER', NULL, l_error_msg, NULL, NULL);
END sp_load_order;
/
output:
SQL> exec sp_load_order
Procedure started18-MAR-14 04.14.25.754121 AM
Inserted into order product table
Procedure ends18-MAR-14 04.18.41.774409 AM
PL/SQL procedure successfully completed.
Elapsed: 00:04:16.83
SQL> spool off
the disk is capable of running the sp faster
[EDITED by LF: applied [spoiler] tags]
[Updated on: Tue, 18 March 2014 15:34] by Moderator Report message to a moderator |
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610162 is a reply to message #610160] |
Tue, 18 March 2014 03:58 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I do not understand. you said this
Quote:INSERT INTO frontroom.ordr ... SELECT ... FROM stage_na_shipment;
after making the changes now the sp takes 4-5 mins to execute.the actual time taken should be 2-3 mins for 1000+rows which suggests that you have followed my advice. But you haven't. You are doing row-by-row processing in loop. That is why your code is slow.
|
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610165 is a reply to message #610163] |
Tue, 18 March 2014 04:18 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
He means rewrite the whole thing as two insert/select statements.
Write a single select statement that returns all the data that needs to be inserted into one of the tables then turn it into an insert select:
INSERT INTO ordr_product
(volume, revenue, source_key, ord_ord_id, prod_prod_id, local_currency)
SELECT <columns>
FROM <tables>
WHERE <where clause>
Get that working then do the same for the other table.
You need a fraction of the code you currently have.
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610190 is a reply to message #610165] |
Tue, 18 March 2014 06:22 |
|
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
i have rewritten my query as
INSERT INTO frontroom.ordr
(ord_number, ds_ds_id, erlst_ship_dt, carrier,
vehicle_number, ord_dt, source_key, sf_sf_id,
cust_cust_id, ord_stat_ord_stat_id, le_le_id,
mkt_mkt_id, sc_sc_id, ctrl_dt, po_number,
expct_ship_date, bt_bt_id, order_process_dt,
storage_loc)
SELECT l_source_key, l_ds_ds_id, i.ship_date, l_carrier,
l_vehicle_number, i.ship_date, l_source_key, l_sf_sf_id,
l_cust_cust_id, l_ord_stat_ord_stat_id, l_le_le_id,
l_mkt_mkt_id, 0, i.ship_date, l_po_number, i.ship_date,
l_bt_bt_id, i.ship_date, l_storage_loc
FROM DUAL where l_source_key=i.order_num_cos;
INSERT INTO ordr_product
(volume, revenue, source_key, ord_ord_id, prod_prod_id,
local_currency)
SELECT i.ship_unit_code, 0, l_source_key_1, l_ord_ord_id,
l_prod_prod_id, i.currency
FROM DUAL where l_source_key=i.order_num_cos;
this didn't make any difference in time
|
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610194 is a reply to message #610190] |
Tue, 18 March 2014 06:38 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'll have one more go. Look at this:
orclz>
orclz> create table t1(c1 number);
Table created.
orclz> insert into t1 select empno from emp;
14 rows created.
orclz>
That is using SQL: inserting a set of 14 rows with one statement.
SQL is a set oriented language, not a procedural language. Do NOT operate on rows individually.
|
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610210 is a reply to message #610206] |
Tue, 18 March 2014 08:30 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
By writing a single select statement that gets all the values needed.
So for starters you have these:
SELECT order_num_cos, ship_date, haulier_name, haulier_num,
po_number, stor_loc, ship_unit_code, ship_unit_code_8,
so_item, ship_from, del_point, currency, client, quantity
FROM stage_na_shipment
.........
SELECT mkt_id INTO l_mkt_mkt_id
FROM market
WHERE code = DECODE (SUBSTR (l_client, 1, 4), 'US23', 'MM',
'US99', 'AFFCO' )
They can be combined into
SELECT sns.order_num_cos, sns.ship_date, sns.haulier_name, sns.haulier_num,
sns.po_number, sns.stor_loc, sns.ship_unit_code, sns.ship_unit_code_8,
sns.so_item, sns.ship_from, sns.del_point, sns.currency, sns.client, sns.quantity,
m.mkt_id
FROM stage_na_shipment sns, market m
WHERE m.code = DECODE (SUBSTR (TO_CHAR (sns..client), 1, 4), 'US23', 'MM',
'US99', 'AFFCO' )
Now combine all the other selects into the above until you have one select that returns all the data that you need to insert, then make that select into an insert/select.
|
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610480 is a reply to message #610041] |
Thu, 20 March 2014 09:31 |
|
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
This i wat i was trying
CREATE OR REPLACE PROCEDURE STAGING.sp_load_order_test
IS
-- n_1 NUMBER;
l_error_msg VARCHAR2 (500);
l_ord_ord_id ordr.ord_id%TYPE;
l_ord_ord_id_1 ordr.ord_id%TYPE;
start_time timestamp;
end_time timestamp;
BEGIN
start_time:=systimestamp;
DBMS_OUTPUT.put_line ('Procedure started' ||start_time);
/*doing a bulk insert as row by row processing is going to take too much of time */
BEGIN
INSERT INTO frontroom.ordr
(ord_number, ds_ds_id, erlst_ship_dt, carrier,
vehicle_number, ord_dt, source_key, sf_sf_id,
cust_cust_id, ord_stat_ord_stat_id, le_le_id,
mkt_mkt_id, sc_sc_id, ctrl_dt, po_number,
expct_ship_date, bt_bt_id, order_process_dt,
storage_loc)
SELECT sns.order_num_cos, 1,sns.ship_date, sns.haulier_name,
sns.haulier_num,sns.ship_date,sns.order_num_cos,sf.sf_id,c.cust_id,2,1,m.mkt_id,0,sns.ship_date,sns.po_number,sns.ship_date,bt.bt_id, sns.ship_date,sns.stor_loc
FROM stage_na_shipment sns, market m,ship_from sf,customer c,bill_type bt
WHERE m.code = DECODE (SUBSTR (TO_CHAR (sns.client), 1, 4), 'US23', 'MM',
'US99', 'AFFCO' ) and sf. source_key = sns.ship_from
and c.true_gcdb_source_key = TO_CHAR (sns.del_point) and bt.code='O' ;
COMMIT;
/*this works as all rows gets inserted to the table in a flash*/
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER_TEST',
NULL,
l_error_msg,
'DUPLICATE RECORD FOUND ' ,
NULL
);
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (current_timestamp,
'SP_LOAD_ORDER_TEST',
NULL,
l_error_msg,
NULL,
NULL
);
-- GOTO end_loop;
END;
DBMS_OUTPUT.put_line ('Inserted into order table');
/*how can i find the ord id for each and every ord number being inserted into ordr table*/
SELECT ord_id
INTO l_ord_ord_id
FROM frontroom.ordr,stage_na_shipment sns
WHERE ord_number = sns.order_num_cos;
DBMS_OUTPUT.put_line (l_ord_ord_id);
BEGIN
INSERT INTO ordr_product
(volume, revenue, source_key, ord_ord_id, prod_prod_id,
local_currency)
select sns.ship_unit_code, 0, sns.order_num_cos || sns.so_item, l_ord_ord_id,
p.prod_prod_id, sns.currency from stage_na_shipment sns,product p where p.secondary_source_key = LTRIM (sns.ship_unit_code_8, 0) ;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
l_error_msg := SQLERRM;
sp_log_entry (SYSDATE,
'SP_LOAD_ORDER',
NULL,
l_error_msg,
'DUPLICATE RECORD FOUND ',
NULL);
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (current_timestamp,
'SP_LOAD_ORDER_TEST',
NULL,
l_error_msg,
NULL,
NULL
);
-- GOTO end_loop;
END;
end loop;
-- n_1 := n_1 + 1;
-- if n_1 = 100 then
-- COMMIT;
-- n_1 := 0;
-- end if;
-- <<end_loop>>
-- null;
end_time:=systimestamp;
DBMS_OUTPUT.put_line ('Procedure ends' ||end_time);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := SQLERRM;
sp_log_entry (current_timestamp,'SP_LOAD_ORDER_TEST', NULL, l_error_msg, NULL, NULL);
END sp_load_order_test;
/
[Updated on: Thu, 20 March 2014 09:38] Report message to a moderator |
|
|
|
|
|
|
|
| Re: can someone tell me why this sp takes so much time? [message #610488 is a reply to message #610485] |
Thu, 20 March 2014 11:00 |
|
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
how to find the bt_id using this condn
IF i.quantity >= 0
THEN
l_code := 'O';
ELSE
l_code := 'C';
END IF;
SELECT bt_id INTO l_bt_bt_id FROM bill_type
WHERE code = l_code
AND ds_ds_id = 1;
how can i append bt_id to my existing query
INSERT INTO frontroom.ordr
(ord_number, ds_ds_id, erlst_ship_dt, carrier,
vehicle_number, ord_dt, source_key, sf_sf_id,
cust_cust_id, ord_stat_ord_stat_id, le_le_id,
mkt_mkt_id, sc_sc_id, ctrl_dt, po_number,
expct_ship_date, bt_bt_id, order_process_dt,
storage_loc)
SELECT sns.order_num_cos,1,sns.ship_date, sns.haulier_name, sns.haulier_num,sns.ship_date,sns.order_num_cos,sf.sf_id ,c.cust_id,2,1,m.mkt_id,0,sns.ship_date,sns.po_number,sns.ship_date,bt.bt_id,sns.ship_date,sns.stor_loc
FROM stage_na_shipment sns, market m,ship_from sf ,customer c,bill_type bt WHERE m.code = DECODE (SUBSTR (TO_CHAR (sns.client), 1, 4), 'US23', 'MM',
'US99', 'AFFCO' ) and sf. source_key = sns.ship_from and c.true_gcdb_source_key = TO_CHAR (sns.del_point);
[Updated on: Thu, 20 March 2014 11:01] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 21:31:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
