Re: How to get data into Oracle over a mediocre network connection

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: Mon, 04 Jun 2007 00:37:51 -0700
Message-ID: <1180942671.010610.290210@h2g2000hsg.googlegroups.com>

On Jun 3, 9:37 am, sybra..._at_hccnet.nl wrote:
> On Sat, 02 Jun 2007 16:13:54 -0700, "Martin T."
>
> <bilbothebagginsb..._at_freenet.de> wrote:
> >Well, there were no earlier attempts with _SQLLoader_. There were the
> >attempts with x update stmts in a sql scripts and the exp/imp approach
> >which failed utterly. The SQLLoader approach just worked ...
>
> >If anyone likes to comment on why loading a table over the network via
> >SQLLoader is 1000x faster than via imp (or SQL) I'd be more than
> >happy ...
>
> So far, you have been less than clear about the exact route you
> followed: no exact statements etc.
> So how could anyone provide any comment?
> There are numerous factors at play. Imp will be slower if you don't
> specify the buffer= parameter, the commit=y parameter, etc, etc, etc.
>

1st try: plain sqlplus script
--> It seems (with hindsight) quite obvious that this will perform very slowly, since after each end every UPDATE statement there would be an ack from the DB to the client.

---

set scan off;
UPDATE TEMP_TEXTS (TXT_ID, TEXT) VALUES (123, 'One text of ten thousands');
(...)
COMMIT;

---

2nd try: Use a begin / end; to encapsulate it --> With a few hundred of staments this seems the easiest way, but if the PL/SQL block does get too large it won't work anymore. (I think there's a limit on how large it's allowed to be.)

3rd try: Create the table locally and then exp/imp it remote --> This seemed to have the same dismal performance as the 1st variant. We went with the default parameters of imp (with TABLES=..), so that would make COMMIT=N ... as for the rest, I should probably read up on how the imp utility does its job (or rather how it differs from SQLLoader). (Reading the docs: The BUFFER param seems to be a thing that allows tweaking, but then the docs go on to say: "For tables containing (...), or DATE columns, rows are inserted individually." ... whatever that means.)

4th try: SQLLoader
--> Again, we pretty much went with the default params, and this time it 'just worked'.

All in all, I'm pretty happy with the SQL-Loader solution, it seems straightforward enough, I do everything remotely, and it's fast.

cheers,
Martin Received on Mon Jun 04 2007 - 02:37:51 CDT