Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: slow migration path
codefragment_at_googlemail.com wrote:
> On 29 Oct, 15:33, DA Morgan <damor..._at_psoug.org> wrote:
>> codefragm..._at_googlemail.com wrote: >>> On 27 Oct, 18:38, codefragm..._at_googlemail.com wrote: >>>> Sorry, ignore this, the problem seemed to be the garbage collecting. I >>>> now call GC.Collectand it does about 300 rows a second >>> <doh><blush>Also, if you are going to insert 3 million rows into a >>> table it might be an idea to remove the index on that table first.</ >>> blush></doh> >> For only 3 million rows? Likely just a waste of time. >> -- >> Daniel A. Morgan >> University of Washington >> damor..._at_x.washington.edu (replace x with u to respond) >> Puget Sound Oracle Users Groupwww.psoug.org
Sorry but I'm not buying what you're selling. The speed at which Oracle can insert rows has nothing to do with the client or C#. I can easily insert 3M rows in less than 10 seconds.
If you can't match that perhaps you should consider using direct load and Oracle software rather than Microsoft's.
Open a SQL*Plus session and try this:
CREATE TABLE parent (
part_num NUMBER,
part_name VARCHAR2(15));
CREATE TABLE child AS
SELECT *
FROM parent;
DECLARE
j PLS_INTEGER := 1;
k parent.part_name%TYPE := 'Transducer';
BEGIN
FOR i IN 1 .. 200000
LOOP
SELECT DECODE(k, 'Transducer', 'Rectifier', 'Rectifier', 'Capacitor', 'Capacitor', 'Knob', 'Knob', 'Chassis', 'Chassis', 'Transducer') INTO k FROM dual; INSERT INTO parent VALUES (j+i, k);END LOOP;
CREATE OR REPLACE PROCEDURE test IS
TYPE myarray IS TABLE OF parent%ROWTYPE; l_data myarray;
CURSOR r IS
SELECT part_num, part_name
FROM parent;
BEGIN
OPEN r;
LOOP
FETCH r BULK COLLECT INTO l_data LIMIT 1000;
FOR j IN 1 .. l_data.COUNT LOOP l_data(1).part_num := l_data(1).part_num * 10; END LOOP; FORALL i IN 1..l_data.COUNT INSERT INTO child VALUES l_data(i); EXIT WHEN r%NOTFOUND;
SELECT COUNT(*) FROM child;
set timing on
exec test
SELECT COUNT(*) FROM child;
Here's my result running it just now:
SQL> exec test
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
SQL> SELECT COUNT(*) FROM child;
COUNT(*)
200000
Elapsed: 00:00:00.01
200,000 rows in 0.62 sec = 3.1 sec/million = 9.3 sec/3 million
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Tue Oct 30 2007 - 18:28:15 CDT