By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Oracle Query: Make Table works 5x faster than Append

P: n/a
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

Oct 31 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ben
Indexes on the existing table would make it slower in this example.
RichardP via AccessMonster.com 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
Oct 31 '06 #2

P: n/a
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

Oct 31 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.