Hello,

I have a fairly large .ACCDB file that is used to house standard queries to retrieve data from ODBC linked tables (Oracle 11g)

The file has about 150 linked tables and approximately 450 queries.

This setup is working fine in Access 2007

We are now migrating to Access 2013 and experiencing a severe performance degradation when using the query designer.

Listing tables and adding fields to the grid seem to work ok.  However when one places the cursor in a criteria field, the CPU spikes to 100% and the UI hangs for 20 -30 seconds.  When the UI becomes responsive again, we notice that the criteria box has become a "search box" of sorts ... it has loaded all the available functions in access and every table and query name in the database. (Pressing the letter "A" for example results in a drop down display of all functions that begin with A along with any queries and tables that also begin with A.      It "seems" that the poor performance is due to Access attempting to load the entries into memory to provide for "type ahead"  functionality. 

 

Could this be the case?  If yes, is there a way to modify this behavior (it does not seem to work this way in Access 2007) 

 

Thanks

Vince Tabor

Hi,

According to your description, did this issue only occur with the particular large Access database? Have you created a new database and used the queries to test?

Please try to use Windows Performance Toolkit to collect log data for analysis.

How to install the Windows Performance Recorder

Download

PS: The log uses ETL file edition.

Before this, please disable add-ins, hardware acceleration etc... You can analyze by yourself if you would like to or send to us (ibsofc@microsoft.com)

Regards,

George Zhao
TechNet Community Support

Thank you for your response.  Since my post I have continued to research.  I believe the issue is caused by the Intellisense feature added in 2010.  This feature tries to list all the possible entries I might place in the query grid, and since the number of objects in the database is large, it takes 10-20  seconds to load and respond.  I have been unable to determine a method to turn off intellisense in the query grid.  Are there any workarounds?

(Smaller databases --fewer objects-- do not slow down much. After removing most objects from a copy of this database also minimized the slowdown)

Thanks

Vince

Hi Vince,

Based on my research, there is no setting that one can toggle on or off in the Access Options to adjust how intellisense works in a database or in Access as a whole.

http://answers.microsoft.com/en-us/office/forum/office_2010-access/how-do-you-disable-intellisense-in-access-2010/c298a666-5349-40e5-948f-de017e92a437?page=1

I recommend you ask the question Access forum, they might give you some ideas.

http://answers.microsoft.com/en-us/office/forum/access?tab=Threads

Regards,

George Zhao
TechNet Community Support