Append Query - very slow

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken Valenti - 30 Apr 2005 17:07 GMT

I have a SQL database with only about 500 records. It originates from an
Excel file that I use to append the records. I created an append query in
Access to only appending one field (the KEY).

When I ran the query, (with 550 records - forty some new records) - it
didn't even run (I let it go over night). It runs in about 10 minutes on my
local SQL server. I went ahead and wrote a proceedure in Excel to download
the entire table and create a new file that contains only new records and one
field. I imported this table to an Access table (local) and ran the query
and it took an entire hour, just for forty some records.

Am I missing something or ??? I've already used Excel to identify which
records to append (which takes only a few seconds - including a SQL download
of the entire table) and just want to add the keys to this table - so it
could append blindly (without comparing) if this is an option.

I am using Access 2003 with a linked ODBC SQL tables and imported the Excel
file as a table in Access (with Access 2002-2003 file format)

Reply to this Message

Ofer - 30 Apr 2005 22:41 GMT

Its hard to tell what the problem without looking at your tables, it should
not take it such a long time, every one will tell you to check the index in
your table.
But I had it before and I found out that adding the records with a function,
takes seconds, one records at a time.
Open a recordset and add to the table, with a loop.

> I have a SQL database with only about 500 records. It originates from an
> Excel file that I use to append the records. I created an append query in
[quoted text clipped - 14 lines]
> I am using Access 2003 with a linked ODBC SQL tables and imported the Excel
> file as a table in Access (with Access 2002-2003 file format)

Reply to this Message

Ken Valenti - 01 May 2005 02:35 GMT

Thanks - you were right. When I transferred over the SQL table, it did not
transfer the key, so it ended up duplicating records and there were some
40,000 records.

I deleted the records, added the key and it now only takes seconds.

> Its hard to tell what the problem without looking at your tables, it should
> not take it such a long time, every one will tell you to check the index in
[quoted text clipped - 21 lines]
> > I am using Access 2003 with a linked ODBC SQL tables and imported the Excel
> > file as a table in Access (with Access 2002-2003 file format)

Reply to this Message