If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access and Linked tables to Oracle
In the past I created an Access database front end for our end users
for the purposes of querying our Oracle data. I noticed that in this older Access database that one of our large Oracle tables was linked without an index (primary key in this case). The interesting part of this issue is that this older Access database returns results from a query against this large Oracle table very, very quickly. Something around 2 seconds if Oracle has cached the query results and 8 seconds if it has not. My primary issue now is that when I refresh the linked tables an index is getting defined for the primary key in the large Oracle table. What this has done is cause this query to take up to 2 1/2 minutes to return the same results! I am not able to delete the primary key (index) from the linked table so I am not able to test to see if removing it would make the refreshed Access database respond in a more timely fashion. Does anyone know a way around the index properties screen in Access in order to make this change? Is there way way to prevent the defining of indexes when programatically creating these linked tables? Any suggestions would be greatly appreciated! Sincerely, Tim Frawley |
#2
|
|||
|
|||
Hi Tim,
From your descriptions, I understood that you would like to why your new database Generally, performance issues can be caused by various factors, and it is difficult to locate the root cause in a newsgroup thread. If the issue still exists after you have used the troubleshooting steps above, to efficiently troubleshoot a performance issue, we recommend that you contact Microsoft Customer Service and Support and open a support incident and work with a dedicated Support Professional. To obtain the phone numbers for specific technology request please take a look at the web site listed below. http://support.microsoft.com/default...S;PHONENUMBERS For now, I would like to collect the following information narrowing down this issue 1) What's the differenct between your old database and new database? (for example, Access 97 and Access 2000?) 2) Make sure you are using the latest MDAC If you are using XP SP2, you could ignore this point, otherwise, MDAC 2.8 is available in the link below http://www.microsoft.com/downloads/d...fe3-c795-4b7d- b037-185d0506396c&DisplayLang=en 3) Make sure your are using the latest Jet 4.0 SP8 Information about Jet 4.0 Service Pack 8 http://support.microsoft.com/kb/829558 4) What Dirver you are using for ODBC? Oracle's driver? 5) Please have a look at the articles below and let me know whether it will help you more Information about query performance in an Access database http://support.microsoft.com/kb/209126 Microsoft Access Performance FAQ http://www.granite.ab.ca/access/performancefaq.htm Sincerely yours, Michael Cheng Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === Business-Critical Phone Support (BCPS) provides you with technical phone support at no charge during critical LAN outages or "business down" situations. This benefit is available 24 hours a day, 7 days a week to all Microsoft technology partners in the United States and Canada. This and other support options are available he BCPS: https://partner.microsoft.com/US/tec...rview/40010469 Others: https://partner.microsoft.com/US/tec...pportoverview/ If you are outside the United States, please visit our International Support page: http://support.microsoft.com/common/international.aspx ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. This document contains references to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet. |
#3
|
|||
|
|||
Michael,
The initial Access database was created in Access 2000. The tables are linked to our Oracle production database. We have since upgraded to Access 2003; however, this issue occurs in Access 2000, 2002 as well as 2003. We are running XP SP2. My machine has Jet 4.0 SP8. I have tested the database on multiple machines with similar results in all cases. We are using the Oracle ODBC drivers for the database connectivity. I have tested Microsoft ODBC for Oracle and the only noticeable difference was that all of the queries ran a little slower. The Oracle ODBC drivers I have used are 9.2.0.3, 9.2.0.6 and now 10.1.0.3.1. I wrote a .NET application to change the linked table connection string (which was when I found this issue) using ADOX. I tried having this program create the connection string exactly as it appears in MySysObjects in the original Access database to no avail. I have attempted multiple ODBC connection strings but they all exhibit similar results. This one particular query using one particular linked table is really slow if the primary key (index) gets created on that table when it is linked. This Oracle table contians only one primary key and close to half a million records. Unfortunately there are no other columns that could be indexed to increase the performance. Also, Access created the index in Ascending order and I cannot even change the order as the records we are looking at are going to be last on the list in this order. I have tried multiple ways of re-creating this query in new Access databases by relinking the tables and creating the query manually all to no avail. Just last week I realized what could be the cause simply by having eliminating all other possibilities. The original Access database, when the linked tables where first created, did not create a primary key (index) for each of the linked tables. Access now does create the primary key (indexes) when I recreate/refresh or update the linked tables. The Linked Table Manager will also create the index if I simply refresh the linked tables. The primary issue is that Access does not provide me with a way of not creating this index when linking the tables or removing the index once the table is linked. The index is causing one particular query to run for 2 1/2 minutes whereas, without the index, the query only takes 8 seconds. I need to know if there is a way to setup the linked table without creating the index or a way to remove the index once the table is linked. I will look over the articles you referred to but I believe the problem is as I have described above. Thank you for your help!! I would be happy to contact Microsoft and create a support incident but they charge for the service and we are not in a position to throw money at this problem. I simply hoped someone would have a suggestion that may help. Maybe the articles you referrenced will be some help, I will give it a try. Until then, again, thank you for the response! Sincerely, Timothy Frawley |
#4
|
|||
|
|||
Hi Tim,
Based on my knowledge, When you are linking the table, you will be prompt to add a unique identifier if no primary key exists in the source Table. The unique identifer is only available in the Access table schema, which means it should not affect the backend. If you meet this senario, I am afraid this is by design feature of Access and we are not able to setup a linked table without creating that unique identifer. Sincerely yours, Michael Cheng Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. |
#5
|
|||
|
|||
Tim,
You might want to consider using a Pass-through query for this, as long as you do not need to update your Oracle tables through Access. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html "Tim Frawley" wrote in message ups.com... Michael, The initial Access database was created in Access 2000. The tables are linked to our Oracle production database. We have since upgraded to Access 2003; however, this issue occurs in Access 2000, 2002 as well as 2003. We are running XP SP2. My machine has Jet 4.0 SP8. I have tested the database on multiple machines with similar results in all cases. We are using the Oracle ODBC drivers for the database connectivity. I have tested Microsoft ODBC for Oracle and the only noticeable difference was that all of the queries ran a little slower. The Oracle ODBC drivers I have used are 9.2.0.3, 9.2.0.6 and now 10.1.0.3.1. I wrote a .NET application to change the linked table connection string (which was when I found this issue) using ADOX. I tried having this program create the connection string exactly as it appears in MySysObjects in the original Access database to no avail. I have attempted multiple ODBC connection strings but they all exhibit similar results. This one particular query using one particular linked table is really slow if the primary key (index) gets created on that table when it is linked. This Oracle table contians only one primary key and close to half a million records. Unfortunately there are no other columns that could be indexed to increase the performance. Also, Access created the index in Ascending order and I cannot even change the order as the records we are looking at are going to be last on the list in this order. I have tried multiple ways of re-creating this query in new Access databases by relinking the tables and creating the query manually all to no avail. Just last week I realized what could be the cause simply by having eliminating all other possibilities. The original Access database, when the linked tables where first created, did not create a primary key (index) for each of the linked tables. Access now does create the primary key (indexes) when I recreate/refresh or update the linked tables. The Linked Table Manager will also create the index if I simply refresh the linked tables. The primary issue is that Access does not provide me with a way of not creating this index when linking the tables or removing the index once the table is linked. The index is causing one particular query to run for 2 1/2 minutes whereas, without the index, the query only takes 8 seconds. I need to know if there is a way to setup the linked table without creating the index or a way to remove the index once the table is linked. I will look over the articles you referred to but I believe the problem is as I have described above. Thank you for your help!! I would be happy to contact Microsoft and create a support incident but they charge for the service and we are not in a position to throw money at this problem. I simply hoped someone would have a suggestion that may help. Maybe the articles you referrenced will be some help, I will give it a try. Until then, again, thank you for the response! Sincerely, Timothy Frawley |
#6
|
|||
|
|||
Lynn,
Thanks for the suggestion. The main problem with a pass-through query is the loss of the designer. This is not necessarily a static report other wise I would create an ASP report for it on our intranet site. The users have a difficult enough time just figuring out how to work with the existing queries without throwing SQL at them too. No biggie, I will keep looking. Thanks, Tim |
#7
|
|||
|
|||
No problem Michael,
I figured this would be the response but I had to give it a shot. Thanks for your help! Sincerely, Tim Frawley |
#8
|
|||
|
|||
Hi Tim,
Thanks for your kindly understanding! If you have any questions or concerns, don't hesitate to let me know. We are always here to be of assistance! Sincerely yours, Michael Cheng Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
query problem with linked SQL tables when importing to new mdb file | Keith G Hicks | Running & Setting Up Queries | 2 | March 22nd, 2005 09:44 PM |
2003 Access Linked Tables: Doug Steele, I need your help. | The parawon | General Discussion | 2 | January 15th, 2005 06:05 PM |
Linked Oracle db in Access 2000 | slapana | Database Design | 6 | December 14th, 2004 07:43 PM |
Linked tables and conversion from Access 2k to Access 97 | Ed Warren | New Users | 0 | June 8th, 2004 11:45 AM |