Minty
AWF VIP
- Local time
- Today, 08:18
- Joined
- Jul 26, 2013
- Messages
- 10,371
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
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?
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
- using a created SQL Insert query string using DOA and currentdb.execute (INSERT INTO x (fields, ) Values (Me.Fields)....
- a similar ADO recordset based new / update (Opening a empty recordset add new Blah blah)
- 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?