Solved

Make table query speed

Posted on 2002-04-19
11
203 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Accepted Solution

by:
aikimark earned 100 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 45

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now