Insert Query taking a long time to execute. (1 Viewer)

[Minty]

Background - Access 2010 FE linked to SQL Server backend on Gigabit network.

On one table only I have an insert query that takes many seconds to run from an unbound form into a table. It's only inserting one record. The table only has approx 34,000 records, has an identity primary key autonumber field, and has 3 indexes on it in addition to the unique index on the PK.

I have tried the following

1. using a created SQL Insert query string using DOA and currentdb.execute (INSERT INTO x (fields, ) Values (Me.Fields)....
2. a similar ADO recordset based new / update (Opening a empty recordset add new Blah blah)
3. a saved parameter query def. ( Set qdf = db.QueryDefs("insNewInvoiceDetails") etc etc

All three methods take ages (5-15 seconds), and on some end user machines have timed out.

A similar insert into a different table within the same database works instantly - on a table with over 1,000,000 records, but with only the primary key, no other indexes. The only difference appears to be the additional indexes - surely they can't be making such a massive difference especially on a relatively small table. The indexes aren't fragmented (<1%) .

Does anybody have any clue as to why the insert takes so long?

 

[NauticalGent]

Had to do a double-take...usually you are the one answering questions, not asking!

It will be interesting to see what the issue is.

 

[Minty]

Had to do a double-take...usually you are the one answering questions, not asking!

It will be interesting to see what the issue is.

This is commonly know as me being puzzled.com

I'm tempted to remove the other indexes out of hours, and see what happens - they are there to improve other background process queries and stored procedures used for complex reporting.

 

[The_Doc_Man]

What formats are the 3 non-PK indexes? Integer-family? String? Floating-point family? Date? And how long are the key fields?