Horribly Slow Query on Large Linked Table

[Tracy B]

I've got a make-table query that searches for and copies a single record from a linked table with upwards of 500,000 records (about 200 MB).

SELECT Database.* INTO CopyTable
FROM [Database]
WHERE (((Database.LN)=[Forms]![MForm]![LN_FORM]));

Both the Access file and the linked table (tab delimited text) are in the same folder on a network share.

If I copy the linked table into the database and index it, the query is instantaneous, but the copy takes about five minutes (and would need to be performed daily) and I cannot set an index on the linked table.

I can't modify the format of the linked table; it's being exported daily from our T-SQL server (as our IT department won't let me query the database with Access directly), and a delimited text file is my only viable option.

As I suspct the work is all being done on my local machine, I'm afraid this will be a lot slower for my colleagues with older PCs, and they just won't abide that.

The query is taking about 90 seconds to return a result; can I improve the performance at all?

[NeoPa]

Have you considered importing the Text file in as a table first?

I'm struggling to understand what can cause a single record table to respond so slowly, but then it seems you're linking to the text file (which doesn't seem too clever an idea to me).

[jimatqsi]

Could you explain this more: "and a delimited text file is my only viable option."
If you're truly stuck with a delimited text file, you're truly stuck, Text files cannot be indexed so you're limited to sequential searches to the record you want. No way around that and the only improvements can come from faster hardware.

So, can the admins of the T-SQL server output something more friendly for you? They could create an Access table or something else. I wonder if you would get a better result if they created an Excel file instead of text?

How many times is the process on your end being done? If the query is to be done multiple times each day then it behooves you to make a one-time process to convert that text to an Access table, or better yet a T-SQL table. You'd have to set up a separate SQL db for that table so your server guy(s) won't be annoyed by you touching their database with Access. (I've run into that attitude before and it irks me to no end)

Okay, here's my real answer, this one doesn't require the SQL guy(s) to change their grubby little process :): Make a new Access DB that monitors the folder for the text file to be created. Schedule that to launch every day at whatever time it should. When the text file appears, immediately import the text into a temporary table - Access or otherwise. Then copy the contents of that temp table into a fully indexed table. That table is the one all of your client workstations link to. Then the monitor/import program should terminate. End of problem,right?

Jim

[Tracy B]

Thanks for the quick and detailed response!

Our T-SQL program (proprietary) does allow Excel exports, but it uses Excel 2003, when it had the row limit around 60,000. Since I'm exporting about 500,000, it won't even think about it. I can't approach IT about anything regarding this, due to various issues of office politics... :(

How would I schedule Access to run something? Is there a way within the progam, or would I have to set it up through the OS on my local machine?

[jimatqsi]

Use the Windows task scheduler (found in the control panel). You can either set the Startup parameters in the Access mdb (Tools/Startup) to run a macro at startup or you can use the Access command line options to force the mdb to launch a macro or form at startup.

Jim

[NeoPa]

Check out Invoking a Database From the Command Line.