We have been writing information to a MS Sql Server database (v2005) for a few years now without much difficulty. After upgrading our Oracle db to 11.2.0.4 (from 11.1.0.7) this past weekend we are finding the process extremely slow. For example, to write 100 records used to take 5 seconds and now takes 50. With almost 30,000 records, it's going to be a problem if we can't figure out how to do it better. The destination table is truncated before beginning. Here is the code we used for the 100 record test. Any tips you have to speed this up are appreciated.
BEGIN
FOR rec IN
(SELECT * FROM my_orders_v WHERE item_number like '52548%'
)
LOOP
INSERT
INTO bill_test_so@sqldatabase
(
ORDER_NUMBER ,
item_number ,
ITEM_DESCRIPTION ,
QUANTITY ,
uom_code,
ORDER_TYPE_TEXT ,
NEW_DUE_DATE ,
ACTION ,
PLANNER_CODE ,
ORGANIZATION_ID ,
INVENTORY_ITEM_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATED_BY ,
CREATION_DATE ,
TRANSACTION_ID
)
VALUES
(
rec.ORDER_NUMBER ,
rec.item_number ,
rec.DESCRIPTION ,
rec.QUANTITY ,
rec.uom_code,
rec.ORDER_TYPE_TEXT ,
rec.NEW_DUE_DATE ,
rec.ACTION ,
rec.PLANNER_CODE ,
rec.ORGANIZATION_ID ,
rec.INVENTORY_ITEM_ID ,
rec.LAST_UPDATE_DATE ,
rec.LAST_UPDATED_BY ,
rec.CREATED_BY ,
rec.CREATION_DATE ,
rec.TRANSACTION_ID
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
errbuf := SQLERRM;
ROLLBACK;
END ;
Thanks for your help! Bill
Aucun commentaire:
Enregistrer un commentaire