I imported approximately 187,000 records from an Access Database into a table using TOAD and it took a little over two hours? Is there anything that I can check to have the import go faster? By year end, I will have a little over 12 million records and at that speed it would take over 128 hours just for the import process to complete. Any ideas?
Math a little off today…the number of import records was 879,855.
I’m still looking at 27 hours of import time. Does this sound like a
reasonable time?
Darlene R. Ulmet
Financial Analyst II
Decision Support Services
Baylor Health Care System
Phone: (214) 820-1936
Fax: (214)820-8284
Email: Darlene.Ulmet@BaylorHealth.edu
That import wizard wasn’t really intended for big time data loads –
the idea was for small, quick things, so you wouldn’t have to mess with
SQL Loader.
SQL Loader is more complicated, but it’s a lot faster. So I’d
suggest that you export your data out of Access and into a text file, then get a
SQL Loader job set up for it.
I’m thinking that a rewrite of the import is in order though – there
are some things that I could do to make it much faster, and you aren’t the
first person to bring this up.
With those kind of volumes, I would take Toad out of the loop, export the data
from Access, and use database level import.
Best regards,
Mike
Michael S. Zarzycki, MTS | Manager of Engineering IT | Sensata Technologies,
Inc. | voice: 508-236-1015 | fax: 508-236-3701 | www.sensata.com | The World
Depends on Sensors and Controls
Afternoon cwgapprentice,
I imported approximately 187,000 records from an Access
Database into a table using TOAD and it took a little over
two hours?
My advice, where possible, avoid Access. It does strange things to your
queries!
Is there anything that I can check to have the
import go faster?
Yes. Normal "performance problems" Check V$SESSION_WAIT, use the Session
Browser in Toad to see what's going on, run a 10046 trace with waits
(and binds possibly) turned on etc etc.
By year end, I will have a little over 12
million records and at that speed it would take over 128
hours just for the import process to complete. Any ideas?
Yes. Export the data from Access to a CSV file. Use SQL*Loader to import
the data. It will be the fastest possible import and can be run directly
on the Oracle Database server and thus, assuming your listener is set up
right, should cut out the network.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Sounds like a cool ER, have Toad take the access tables automagically to a
loader session, or even as external tables.
Agreed. SQL*Loader or Datapump import or external tables. Should see times more
like 10 minutes for the full load even if you don’t partition or use parallel
loading, it’s not that much data. So it’s worth some investment. I don’t think
any of those will natively read Access data, though.
The other thing to check is whether you’re loading into a table with constraints
and indexes. If so, you’ll save some time by removing them. Could reduce your
big one from 27 hours to maybe 10 if you’re really lucky.
Overall, though, the times seem slow for the simple import. I did an Toad import
from a flat file with 670K records in 1-2 hours yesterday here. So you might
have something else slowing things down – network bandwidth, number of table
columns, etc.
– jim
avoid Access. It does strange things to your queries!
What… You don’t like Access:
Darlene,
As others have said you really need to look into using SQLLoader. Toad is works
fine for one off table loads under 100,000 rows. Once you get above that or you
are doing the same loads regularly, SQLLoader is the way to go.
I loaded 6.2 million rows into a table last week with SQL*Loader and it took
just over 11 minutes.
Total logical records skipped: 0
Total logical records read: 6227630
Total logical records rejected: 86
Total logical records discarded: 0
Run began on Thu Mar 02 12:34:08 2010
Run ended on Thu Mar 02 12:45:22 2010
Elapsed time was: 00:11:14.39
CPU time was: 00:01:31.69
Ed
[TeamT]
External tables is so much easier than SQL Loader – and it’s
essentially the same thing just exposed in a much friendlier way. I can load
about one million records on my notebook in under 10 minutes – and in
under 2 minutes when database is on a real server (and not my notebook).
Now that said – I suspect that the SQL Loader wizard in TOAD can be used
to scan the sample data and help you generate 80+% of your create table
statement. Then simply add hints like /*+ parallel append */ and it will run oh
so fast – don’t blink J
Bert
The only problem with external tables is it requires access to the server OS- to
place the file(s). But if you have that, then it’s very handy as Bert
says.
You might consider abandoning the initial storage of data in Access all together.
I don't know your situation, but if you cannot abandon the Access-database completely (i.e. because of forms or reports) it might be worthwile to store the data itself in Oracle and use Access as a mere shell. (using linked tables)
Because the data is stored realtime in Oracle, copying or re-using the data is a lot easier and faster.
Also the datatransfer using ODBC is fast enough to supplying any data Access needs to display.
Regards,
Jörgen Adriaanse
Gemeente Renkum
"cwgapprentice" 10-3-2010 16:38 >>>
I imported approximately 187,000 records from an Access Database into a table using TOAD and it took a little over two hours? Is there anything that I can check to have the import go faster? By year end, I will have a little over 12 million records and at that speed it would take over 128 hours just for the import process to complete. Any ideas?
You might consider abandoning the initial storage of data in Access all
together.
I don't know your situation, but if you cannot abandon the Access-database
completely (i.e. because of forms or reports) it might be worthwile to store the
data itself in Oracle and use Access as a mere shell. (using linked tables)
Because the data is stored realtime in Oracle, copying or re-using the data is a
lot easier and faster.
Also the datatransfer using ODBC is fast enough to supplying any data Access
needs to display.
Regards,
Jörgen Adriaanse
Gemeente Renkum
"cwgapprentice" 10-3-2010 16:38 >>>
I imported approximately 187,000 records from an Access Database into a table
using TOAD and it took a little over two hours? Is there anything that I can
check to have the import go faster? By year end, I will have a little over 12
million records and at that speed it would take over 128 hours just for the
import process to complete. Any ideas?
att1.dat (43 Bytes)
You might consider abandoning the initial storage of data in Access all together.
I don't know your situation, but if you cannot abandon the Access-database completely (i.e. because of forms or reports) it might be worthwile to store the data itself in Oracle and use Access as a mere shell. (using linked tables)
Because the data is stored realtime in Oracle, copying or re-using the data is a lot easier and faster.
Also the datatransfer using ODBC is fast enough to supplying any data Access needs to display.
Regards,
Jörgen Adriaanse
Gemeente Renkum
"cwgapprentice" 10-3-2010 16:38 >>>
I imported approximately 187,000 records from an Access Database into a table using TOAD and it took a little over two hours? Is there anything that I can check to have the import go faster? By year end, I will have a little over 12 million records and at that speed it would take over 128 hours just for the import process to complete. Any ideas?
You might consider abandoning the initial storage of data in Access all
together.
I don't know your situation, but if you cannot abandon the Access-database
completely (i.e. because of forms or reports) it might be worthwile to store the
data itself in Oracle and use Access as a mere shell. (using linked tables)
Because the data is stored realtime in Oracle, copying or re-using the data is a
lot easier and faster.
Also the datatransfer using ODBC is fast enough to supplying any data Access
needs to display.
Regards,
Jörgen Adriaanse
Gemeente Renkum
"cwgapprentice" 10-3-2010 16:38 >>>
I imported approximately 187,000 records from an Access Database into a table
using TOAD and it took a little over two hours? Is there anything that I can
check to have the import go faster? By year end, I will have a little over 12
million records and at that speed it would take over 128 hours just for the
import process to complete. Any ideas?
Afternoon all,
Because the data is stored real-time in Oracle, copying or
re-using the data is a lot easier and faster.
Also the data transfer using ODBC is fast enough to supplying
any data Access needs to display.
If I may, beware of using any queries from Access to an Oracle database.
Having been on the wrong end of a couple of bad ones, if they are not
defined as "pass through" queries, Access/ODBC can and will destroy a
perfectly good SQL statement and butcher it beyond all possible
recognition.
And, if a query takes too long in Access, and the user clicks on the 'X'
to close Access done, the query continues executing on the database. I
know the users need to be educated, but that's what they do!
It really has to be pass through.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk