Random performances of same query??

[Ahmed Kendoussi]

Question to an expert...

I have built a query that inserts something like 50.000 rows into a
table and i can notice that most of the time it executes without any
issue and completes very quickly, but on some other times, it can take
up to one hour...

I understand there might be many cause for this issue (network, etc).
But from your point of view, is there a way to avoid such pb by, i
don't know, cleaning some cache somewhere, re-building the db ,
whatever??)

Thanks,
Ahmed

 

[Tom van Stiphout]

On Fri, 3 Jul 2009 09:04:12 -0700 (PDT), Ahmed Kendoussi

If the situation is that a query normally runs in a few seconds but
sometimes takes an hour, your suggestions are not going to make the
difference. The only things I can think of is a very poor network (but
this would more likely cause corruption) or other users locking
records.

-Tom.
Microsoft Access MVP

 

[Jerry Whittle]

Is it the same 50K records? Could some be violating a unique constraint or
something?

Check your table for excessive indexes. Access has a bad habit of
automatically creating indexes for any field with ID; key; code; or num in
the field name. For example if you made a field named "Emp_ID" the primary
key, Access will also create yet another index. Excess indexes can make
inserts take much longer.

A rebuild or compact and repair might actually slow things down. If adding
records also causes the database file to grow, that could take a while longer.