Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: slow migration path

Re: slow migration path

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 30 Oct 2007 16:28:15 -0700
Message-ID: <1193786895.154835@bubbleator.drizzle.com>


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

>
> as I say, writing speed for a c# app to an oracle database on a home
> machine goes from 30 rows a second to 300 rows a second with 3 million
> rows if you remove the index. What sort of writing speed should I be
> expecting?

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;
   COMMIT;
END;
/

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;

   END LOOP;
   COMMIT;
   CLOSE r;
END test;
/

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.org
Received on Tue Oct 30 2007 - 18:28:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US