Update query on access database very slow

Thread view:

Michiel Oliemans - 28 Oct 2005 13:05 GMT

I've got an access database with approximately 47000 records in a
table. These records are inserted through Java in 30 seconds using a
PreparedStatement INSERT query.

When I want to update those records in that table using an UPDATE
query, it takes very long. It takes 1:15 min to update 34 of those
records!!!

Any ideas why this takes so long? If wanted I could supply some sample
code.

I've tried manually committing the updates.

Thnx,
Michiel

Reply to this Message

zhaoyh.hxtt@gmail.com - 28 Oct 2005 14:27 GMT

Check your WHERE clause. Maybe there's some index need to update? Mabye
they are other concrrent connections using that mdb files? Anyway, 1:15
min seems too longer.

BTW, you can try HXTT Access, a pure Java type 4 JDBC packages for MS
Access. It's quicker than ODBC bridge. You can find it at
http://www.hxtt.net/access.html .

Yonghong Zhao
System Analyst
www.hxtt.net

Reply to this Message

Thomas Hawtin - 28 Oct 2005 20:25 GMT

> I've got an access database with approximately 47000 records in a
> table. These records are inserted through Java in 30 seconds using a
[quoted text clipped - 3 lines]
> query, it takes very long. It takes 1:15 min to update 34 of those
> records!!!

Over two seconds a record? Ouch. Even a full table scan of 47,000
records shouldn't take that long.

Do you know where the bottle neck is? Disk, network, server cpu, client
cpu? Is perhaps some GUI updating? Does it take half as long with half
as many updates? Perhaps some timeout going on?

Tom Hawtin

Signature

Unemployed English Java programmer
http://jroller.com/page/tackline/

Reply to this Message

Michiel Oliemans - 31 Oct 2005 15:31 GMT

>> I've got an access database with approximately 47000 records in a
>> table. These records are inserted through Java in 30 seconds using a
[quoted text clipped - 12 lines]
>
>Tom Hawtin

That's the funny part. It already takes this slow when I test it
locally. No GUI updating going on. When it's updating it takes a big
chunk of processor capacity. And when I want to update twice as many
records, it takes twice as much time.

The other funny thing is that the insert (and delete for that matter)
runs normally (insert of 47k records in about 30 seconds).

In my WHERE clause I check 3 attributes, two of which are strings.
Could that be a bottleneck? I need to check those fields, but I might
be able to convert those two strings to integers.

In the beginning of development I didn't have this problem with
updating, it went about just as fast as insertion. Since then the main
things that have changed is de addition of multiple tables. I've tried
it with losing those tables, but that didn't change anything.
Furthermore I've had to compact and repair the database several times
(since during this testing I've had to delete records a couple of
times and the database seemed to explode otherwise).

Michiel Oliemans

Reply to this Message

Update query on access database very slow

Free Magazines