Passthrough Query taking too long...: MS Access

by: nmcdermaidPosted on 2009-03-01 at 19:28:13ID: 23771284

How many rows are in the source tables?

Is this over a WAN or LAN?

If you dump the tables (remotely) to a text file, how big is the text file, and how long does it take to copy the text file locally?

by: tparvaizPosted on 2009-03-01 at 20:26:01ID: 23771466

No of rows = 1168160 (1 Mil)

I am using a server... both oracle DB and MS Access are on the same machine...

it took 7-10 mints to export the file from MS Access to txt... txt file is close to 0.5 GB....

by: nmcdermaidPosted on 2009-03-01 at 20:39:29ID: 23771538

So your table size has just been gradually growing over time?

I suspect you've just hit MS Access's practical size limit for a table.

Any reason you are importing it into MS Access, and not just linking to it via ODBC, or using some other solution?

by: tparvaizPosted on 2009-03-01 at 21:05:09ID: 23771631

some one is providing us this information in MS Access... I am open to use other applications but we get the raw data in MS Access...

by: nmcdermaidPosted on 2009-03-01 at 21:25:41ID: 23771695

So you're saying they won't give you a login to Oracle to get the data directly, but they will let you upload it to MS Access? Does someone else do the upload and send you the MS Access file?

by: tparvaizPosted on 2009-03-01 at 21:38:49ID: 23771740

I do have admin access to Oracle as well as to the server(as a new bee not familiar with the utilities available in Oracle)... I usually upload data using ODBC connection but this time due to data size it's taking much longer...

by: tparvaizPosted on 2009-03-29 at 19:55:44ID: 24015734

any other suggestions....?

by: nmcdermaidPosted on 2009-03-29 at 22:38:23ID: 24016189

So what do you do in MS Access? If you just run queries, then you will find that running those queries qill be alot quicker if you run them directly against the Oracle database.

Are you certain that you are exporting from an Oracle table (and not a view?) Do you have Toad or SQLPlus installed on your Oracle server?

by: tparvaizPosted on 2009-05-21 at 21:35:50ID: 24447776

Is there a utility in oracle that can do it... At the moment I've linked oracle tables to MS Access and I simply update them...

by: tparvaizPosted on 2009-05-21 at 21:36:11ID: 24447778

I have toad installed on my machine

by: nmcdermaidPosted on 2009-05-22 at 03:39:06ID: 24449368

Sorry let me clarify, is the data going from MS Access > Oracle? Are you running update or insert queries on your MS Access linked tables?

by: tparvaizPosted on 2009-05-23 at 13:14:48ID: 24459355

yes... Data is going from MS Access to Oracle and I am doing insert query on my MS Access linked tables

by: nmcdermaidPosted on 2009-05-24 at 00:41:18ID: 24460810

I'm not an Oracle expert sorry, but what version of Oracle is it?

It might be better to export your MS Access data to text and import it directly from Oracle (I think you're heading that way anyway)

Checking on google there seems to be two basic methods:

1. SQL Loader (http://www.oracleutilities.com/OSUtil/sqlldr.html , http://www.dba-oracle.com/tips_sqlldr_loader.htm)

2. Mount a text file as a table and run your insert query that way:

http://www.devshed.com/c/a/Oracle/Reading-Text-Files-using-Oracle-PLSQL-and-UTLFILE/

http://www.akadia.com/services/ora_etl.html

Sorry for just posting links. I don't actually have Oracle installed so I can't check any of this.

by: tparvaizPosted on 2009-05-24 at 11:14:42ID: 24462403

it's oracle 10G

by: nmcdermaidPosted on 2009-05-24 at 17:08:39ID: 24463614

Do you want to go through some of the info at this link:

http://www.akadia.com/services/ora_etl.html

I must stress that I am not an Oracle expert and we are both learning here!

1. Export a subset of your data (say 100 lines) from MS Access to a text file

2. Run these things in TOAD:

CREATE OR REPLACE DIRECTORY dat_dir AS 'C:\Oradata\Data';
CREATE OR REPLACE DIRECTORY log_dir AS 'C:\Oradata\Log';
CREATE OR REPLACE DIRECTORY bad_dir AS 'C:\Oradata\Bad';
GRANT READ ON DIRECTORY dat_dir TO scott;
GRANT WRITE ON DIRECTORY log_dir TO scott;
GRANT WRITE ON DIRECTORY bad_dir TO scott;

That creates some objects and gives you access to them (replace scott with your user name)

3. This is straight from that article - you'll need to alter it to reflect your source data.

CREATE TABLE revext (person VARCHAR2(20),
rev_jan NUMBER(4),
rev_feb NUMBER(4),
rev_mar NUMBER(4),
rev_apr NUMBER(4),
rev_mai NUMBER(4),
rev_jun NUMBER(4),
rev_jul NUMBER(4),
rev_aug NUMBER(4),
rev_sep NUMBER(4),
rev_oct NUMBER(4),
rev_nov NUMBER(4),
rev_dez NUMBER(4))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dat_dir -- corresponds to prior statement above
ACCESS PARAMETERS
(
records delimited by newline
badfile bad_dir:'revext%a_%p.bad' -- corresponds to prior statement above
logfile log_dir:'revext%a_%p.log' -- corresponds to prior statement above
fields terminated by ','
missing field values are null
( person,
rev_jan,
rev_feb,
rev_mar,
rev_apr,
rev_mai,
rev_jun,
rev_jul,
rev_aug,
rev_sep,
rev_oct,
rev_nov,
rev_dez
)
)
LOCATION ('revext.dat') -- Actual file being loaded
)
PARALLEL 4
REJECT LIMIT UNLIMITED;

4. Now you should be able to run select * from revext (that table that you created above) and see your text file.

5. Now just run INSERT INTO FInalTable (FIeld1,FIeld2...) SELECT FIeld1,Field2..... FROM revext; and that should copy the data into your table.

Question

Passthrough Query taking too long...

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

MS Access

Microsoft Access Database

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press