Oracle Query: Make Table works 5x faster than Append

[RichardP via AccessMonster.com]

Hi there,

I have a query running against Oracle which returns approx. 140,000 records.

I need to store all this data locally in my BE database. Conventionally I
would set up a table to contain this data and then run an Insert Into (append)
query. Perhaps I might also experiment by toggling the UseTransaction
property if performance was inadequate.

When I run an append query to a linked table on a B/E database on my C drive,
it takes a very slow 10-12 minutes on average. (Changing UseTransaction to
False makes little difference here.)

If instead I run a select into (make table) query to the B/E database on my C
drive, it takes 2-3minutes on average.

Can anyone shed any light onto why the Select Into query is faster than then
Insert Into, or alternatively how I might help improve the performance of
Insert Into query?

Thanks in advance,
Richard

--
Message posted via http://www.accessmonster.com

[Ben]

Indexes on the existing table would make it slower in this example.
RichardP via AccessMonster.c om wrote:

Hi there,

I have a query running against Oracle which returns approx. 140,000 records.

I need to store all this data locally in my BE database. Conventionally I
would set up a table to contain this data and then run an Insert Into (append)
query. Perhaps I might also experiment by toggling the UseTransaction
property if performance was inadequate.

When I run an append query to a linked table on a B/E database on my C drive,
it takes a very slow 10-12 minutes on average. (Changing UseTransaction to
False makes little difference here.)

If instead I run a select into (make table) query to the B/E database on my C
drive, it takes 2-3minutes on average.

Can anyone shed any light onto why the Select Into query is faster than then
Insert Into, or alternatively how I might help improve the performance of
Insert Into query?

Thanks in advance,
Richard

--
Message posted via http://www.accessmonster.com

[RichardP via AccessMonster.com]

Yes maybe I should have clarified, there aren't any indexes on the existing
table.
Ben wrote:

>Indexes on the existing table would make it slower in this example.

>Hi there,

[quoted text clipped - 18 lines]

>Thanks in advance,
Richard

--
Message posted via http://www.accessmonster.com