Make table query speed

Hi experts,

I am working in Access 2002 on a Windows 2000 system.  The database contains linked Oracle tables.  I have a Make Table query that uses 2 of these linked tables.  The query will not run!!!  I have let it go 5.5 hrs and it hasn't completed.  On a Windows 95 system running Access 97 the query ran in minutes.  Any ideas?

Thanks in advance!!

Connie
Catt999Asked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
Your performance problem is caused by the joining of two attached tables.  What Access does is bring the contents of the attached tables into the query and then attempts to join them without benefit of indexes.

In addition to the pass-thru query solution, the best performing configuration requires you to create a view of the two joined tables in the Oracle database.  You alter your current query, replacing the table references with the new view reference.
0
 
nico5038Commented:
You can optimize this by using a so-called pass-through query.
This needs to be in (PL/)SQL as it's executed by the Oracle database.
Thus only the resulting rows will be passed back to Access.
At the moment Access will extract the data of both tables and execute the query on the completely imported data.
Running a passthrough query will only pass the resulting rows, normally speeding things up.

Just create a new query and don't assign table(s). Select from the query menu the SQL special for the PassThrough query. There you can specify your query in Oracle format and by pressing the properties button you can construct the connection string e.g. using an ODBC connection.

Save this query and use that for a Make-table query.

Clear ?

Nic;o)
0
 
Catt999Author Commented:
Nic...

Yes, that's about clear as mud!!!  Actually, I am not the database developer/administrator.  I was just given the task of trying to find a fix for this.  I understand what a PassThrough query is meant for, but I have no idea as to how to construct one using Oracle tables.  But, I do have access to the system where this all runs.  Would it be possible for you to give me the syntax of what to type in for the SQL?  Say, for example, I wanted to make a table containing employee first name, last name, id, dept, and projects where id was the same in both tables (table1 and table2).  I will up the points as required.

Connie
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Catt999Author Commented:
btw...the linked oracle tables are connected via odbc.  Have tried both the MS Access driver and the Oracle driver but query still doesn't want to run.

Connie
0
 
nico5038Commented:
Then start creating a query in the Oracle system.
This will have to look something like:
SELECT  firstName, lastName, id, dept, projects From employee1 as A JOIN employee2 as B ON A.employeeID = B.employeeID;

Next copy/paste this to access into a passthrough query.
Just create a new query and don't assign table(s). Select from the query menu the SQL special for the
PassThrough query. There you can copy/paste your query in Oracle format
By pressing the properties button you can construct the connection string e.g. using an ODBC connection.

Clear sofar? If you have trouble with the Oracle part, then checkout with your companies Oracle expert. As I don't have Oracle at my computer...

Nic;o)
0
 
Catt999Author Commented:
OK...will check this out next week.  Thanks!!

Connie
0
 
Catt999Author Commented:
aikimark,

Thanks for your reply.  I will pass on both possible solutions to the appropriate people and get back to you on what the outcome is.

Connie
0
 
Catt999Author Commented:
Hi experts,

Just to let you know that I haven't forgotten about this question.  I have passed both comments to the person in charge of this database but haven't heard back yet either way if either solution worked.  Will keep you posted.

Connie
0
 
nico5038Commented:
Thanks for notifying ;-)

Nic;o)
0
 
Catt999Author Commented:
Experts,

The solution to the query problem is solved.  It appears that the query was already using an Oracle view.  The developer changed the query to use the actual Oracle tables instead of the view and it worked.  I am going to award the points to aikimark seeing as his solution prompted the developer to try using the tables instead of the view.

Thanks for all your help!!

Connie
0
 
aikimarkCommented:
Connie,

Thanks for the points.

You might have your DBA/developer merge the two views into one joined view if that is applicable to your problem.

This joined view is probably the equivalent to the SQL solution you now have.  However, a joined view might be a little faster if your SQL solution is dynamic SQL.  Of course, if you're happy with your current performance, don't worry about it.

Mark
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.