S
Scoop
I have a database with a link table in it for a many-to-many
relationship. The table has 2 fields that together are a primary key.
The only index is the index for the dual-field primary key. The table
has ~1 million records and we sometimes run an append query that adds
100,000 records or so at a time. The query is executed from code
using:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry..."
DoCmd.SetWarnings True
The database itself is split and there are multiple users (4-5 on
average). When the query is executed, it takes 10-15 minutes to run
and the database seems to freeze for anyone using it, regardless of
what forms they are using and which tables those forms are bound to.
Does anyone have any idea as to what might be going on? Is there any
way to either speed the query execution up, prevent other users from
being impacted, or both?
relationship. The table has 2 fields that together are a primary key.
The only index is the index for the dual-field primary key. The table
has ~1 million records and we sometimes run an append query that adds
100,000 records or so at a time. The query is executed from code
using:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry..."
DoCmd.SetWarnings True
The database itself is split and there are multiple users (4-5 on
average). When the query is executed, it takes 10-15 minutes to run
and the database seems to freeze for anyone using it, regardless of
what forms they are using and which tables those forms are bound to.
Does anyone have any idea as to what might be going on? Is there any
way to either speed the query execution up, prevent other users from
being impacted, or both?