?
Solved

Make table query speed

Posted on 2002-04-19
11
Medium Priority
?
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

777 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