473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Oracle Query: Make Table works 5x faster than Append

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
2 4402
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: JWM | last post by:
I am trying to implement Oracle connection pooling for the following code, which was written by someone else. Here is my main question -- this java file creates code that is executed every hour,...
1
by: girlkordic | last post by:
I have a linked table from an Oracle database (that contains HR information for my company)in an Access database. The Oracle DB is updated on a daily basis. I would like one of my tables in...
4
by: robboll | last post by:
When I try to use an append query from an oracle link it takes forever. I am exploring the idea of doing an append action using a pass-through query. If I have an Oracle ODBC connection to...
2
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with...
6
by: BillCo | last post by:
I've reached the limit of my knowledge here and I'm starting to go mad - any help would be greatfully recieved!!!! I'm having a strange problem with making pass through queries to an oracle db...
4
by: MN | last post by:
I have to import a tab-delimited text file daily into Access through a macro. All of the data needs to be added to an existing table. Some of the data already exists but may be updated by the...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
7
by: =?Utf-8?B?QVRT?= | last post by:
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g. Please help, I'm trying to write an ASP page to use ADO to run a long query against an Oracle 10g database, to create tables,...
2
by: Vinod Sadanandan | last post by:
All, Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features . Precompilers:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.