CODEWITHSUNDEEP
sqlexcelms-access
user8128167
user8128167

Reputation: 7676

Excel 2010 Data -> "Refresh All" slow

Presently am working with an Excel spreadsheet that makes at least 10 database queries to external Microsoft Access *.mdb files. For each project my company works on, we have a specific excel file related to that project, so we have hundreds of these files. Usually when an analyst opens the Data tab and click on "Refresh All" the refresh completes in a minute or two; however, on a new project for a given excel file it is taking at least an hour to complete the refresh. Here is an example of one of the connection strings:

DSN=MS Access Database;DBQ=W:\Projects\Analysis\project.mdb;DefaultDir=W:\Projects\Analysis\Analysis;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

And here is the associated query:

SELECT Field.FieldNumber, Field.FieldName, Field.GroupMnemonic, Field.ClientFieldID
FROM Field Field
ORDER BY Field.FieldName

I have spent time studying various websites discussing slow excel issues like http://msdn.microsoft.com/en-us/library/ff700515.aspx; however, these websites deal more with calculations and VBA whereas I suspect the performance problem is somewhere in an access file. Does anyone have any suggestions on how to troubleshoot and resolve this issue? TIA.

UPDATE: As suggested in the answer below by JohnFx, I checked the queries and found that they had no definite keys, so and so added keys in the Microsoft Access database generation like this:

CREATE UNIQUE INDEX PIndex ON [myTable] ([KEY])

Upvotes: 1

Views: 2307

Answers (1)

JohnFx
JohnFx

Reputation: 34909

Run the queries individually directly in Access to rule Excel in or out as part of the problem. If the queries are still slow in Access consider adding indexes on any columns that are in being sorted or filtered on.

Upvotes: 2

Related Questions