[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Make table query speed

Posted on 2002-04-19
11
Medium Priority
?
211 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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