Slow performance when adding new record

[Mark L]

I've got a customer who is reporting that when they hit my SAVE button to add a new Order, there is a very long wait before the system allows them to continue. It apparently seems to happen for 2 customers. As it turns out, these are by far the 2 biggest customers. Of the approximately 200,000 orders in the table, about 40,000 are for one customer and 20,000 for another. Since the Orders table has a CustomerID index, I'm wondering if Access is having some trouble inserting another record into this index because there are so many matching entries already for that key.

Any thoughts?

[HansV]

It might be caused by the index, but it's hard to say - you probably don't want to remove the index from CustomerID because that could cause other, potentially bigger problems. You may be caught between a rock and a hard place...

[Mark L]

Yes, removing the index isn't an option; it's used too much. I've had databases with tables that have as many or more records in it, so just that shouldn't be a problem. I just didn't realize how big a percentage that one customer's records were of the overall # of records. I can't attribute it to anything else; I can't find anything in the BeforeUpdate, AfterUpdate, or AfterInsert events that would cause this.

[HansV]

Would it be an option to move the backend to SQL Server?

[Mark L]

That's in the works, but not an option now (they are in midst of their busy season).

[Wendell]

Can you take a test copy of the database and reproduce the problem? If so, you could try removing and re-adding the index and see if that solves the problem. You could also try adding an order in the test database before adding the index and see if it still takes a long time. If you can't create a test database, things get sticky. But you will surely want a test version to work with before you upsize to SQL Server. These kind of problems are always difficult to isolate.

[Mark L]

I'm thinking I may delete that index and create another one that is comprised of 2 fields: the CustomerID and perhaps the OrderNo. This will actually be a unique index (since the OrderNo is unique), although I don't have to declare it as such. I'm hoping it would make it easier for Access to insert into the index; and since the CustomerID is the first field in the index, Access should still be able to utilize this index when it needs to read records for a customer. Something I'd like to try "off-hours".