Opening Query performance issue?

[Kevin Ensign]

Sitting "second chair" attempting to learn how to build a DB. My co-worker and I have stumbled upon what we think is a performance issue and trying to search for ways to improve.

Currently we store an excel sheet with data concerning all purchased parts and its information on the network, its about 14 megabyte file. We created two queries using this excel file. The first provides current record data. The second one we created pulls specific items we need for one of the tables.

The issue is when we open either of these queries it takes 30+seconds to open. Any of the other queries open ( not using the excel file) immediately. Because of the size of excel file is this normal?

As of this posting we have performed a compact and repair and saved the excel file as a csv.file and no changes.

[aytee111]

I presume this is a linked file? Linking slows things down and Excel is slower than most. Local tables are the best - you may want to import the file to a local table before running your queries.

[Kevin Ensign]

Yes the file is linked. The reason for this is we plan to update it monthly or quarterly as information changes periodically.

[aytee111]

Have a standard import routine - either a macro or VBA - that happens before you run the queries. Either that or get used to the slow performance.