Known issues mixing linked tables with imported excel data in Access

[maxandy]

All,

One of my colleagues has just asked me an interesting question.

Using Access, he has linked data from an ODBC compliant database. To this, he has added an imported Excel spreadsheet.

When running a query, he has noticed that it's taking a very long time. Now, the linked tables only contain about 65K rows of data and the imported sheet contains about 35K rows.

It shouldn't take a long time so, my question is - are there any known problems in combining linked ODBC tables and imported Excel sheets in Access?

Cheers

 

[k5tm]

The Access SQL engine does not have any cardinality information about the linked table, so it may be doing a very bad job of optimizing (optimising) the query. You very well may be doing 35K full table scans of the 65K row table.

Does the linked data have a unique index? If so, did your colleague specify this index when creating the link? If so, does the join specify the unique index in a way that is useful?

You can get a better understanding of what is happening at the ODBC API level by enabling ODBC tracing.

Access is notorious for this...

Tom Morrison

http://www.liant.com

 

[genomon]

Better to import the Excel data into a local table and index it or create a primary key. Probably slow due to the lack of either.

The early bird may get the worm, but the second mouse gets the cheese in the trap.