[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Make table query speed

Posted on 2002-04-19
11
Medium Priority
?
212 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:Catt999
  • 6
  • 3
  • 2
11 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6954594
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
 

Author Comment

by:Catt999
ID: 6955536
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
 

Author Comment

by:Catt999
ID: 6955540
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 54

Expert Comment

by:nico5038
ID: 6955813
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
 

Author Comment

by:Catt999
ID: 6956598
OK...will check this out next week.  Thanks!!

Connie
0
 
LVL 46

Accepted Solution

by:
aikimark earned 400 total points
ID: 6957201
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
 

Author Comment

by:Catt999
ID: 6961520
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
 

Author Comment

by:Catt999
ID: 6977364
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6977810
Thanks for notifying ;-)

Nic;o)
0
 

Author Comment

by:Catt999
ID: 6992415
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
 
LVL 46

Expert Comment

by:aikimark
ID: 6992421
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question