Re: Can my loader run as fast as SQL*Loader?

From: <christophd_at_bigfoot.com>
Date: Tue, 28 Sep 1999 17:16:28 GMT
Message-ID: <7sqt4v$ff1$1@nnrp1.deja.com>

In article <7soa2r$kab$1_at_nnrp1.deja.com>,   gaop_at_my-deja.com wrote:
>
>
> I loaded some data using Oracle SQL*Loader, using Conventional Path,
> since a column of my data is of object type.
> I also loaded the same data using JDBC with a prepared INSERT
> statement.
>
> The SQL*Loader loaded the data in about 20 secs, but my loader
> loaded the same data in 30 Minutes!
>
> As I understanded, SQL*Loader Conventional Path goes through the
> same path as a user program. Anyway I can tune my loader to go
> as fast as SQL*Loader?
>
> Thanks for any help.
>
> Gao Peng
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

You will never beat SQL*Loader, but try inserting with 'well' sized arrays. I copied a part of ORACLE documentation about it. It is really not stupid to check the proper array sizing in your existing network environment.

---

Inserting with Arrays

You can use host arrays as input variables in an INSERT statement. Just make sure your program populates the arrays with data before executing the INSERT
statement.

If some elements in the arrays are irrelevant, you can use the FOR clause to control the number of rows inserted. See the section "Using the FOR Clause" .

An example of inserting with host arrays follows:

char emp_name[50][20];
int emp_number[50];
float salary[50];

/* populate the host arrays */

...

EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL)     VALUES (:emp_name, :emp_number, :salary);

The cumulative number of rows inserted can be found in the rows-processed count, sqlca.sqlerrd[2].

In the following example, the INSERT is done one row at a time. This is much less efficient than the previous example, since a call to the server must be made for
each row inserted.

for (i = 0; i < array_dimension; i++)

    EXEC SQL INSERT INTO emp (ename, empno, sal)

        VALUES (:emp_name[i], :emp_number[i], :salary[i]);

Restrictions

You cannot use an array of pointers in the VALUES clause of an INSERT statement; all array elements must be data items. Mixing simple host variables with host arrays in the VALUES clause of an INSERT statement is not allowed. If any of the host variables is an array, all must be arrays.

---

Rgrds
Ch.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 28 1999 - 12:16:28 CDT