Re: How I dramatically sped up DB file updating

Bob Richardson wrote:
>
> A few weeks ago I was bemoaning the length of time it took to update a mere

I don't remember seeing that posting... well, this is no DB specific
newsgroup either. Also, why didn't you add this solution to the original
thread, that had been more professional behaviour in newsgroups:)

> By substantially reducing the number of times I actually updated the DB, the
> time was cut substantially.

I am glad you found a working solution. To those of us that have worked
'for ages' with databases, your findings are not very surprising. Less
work to do takes less time.

Yet, if it takes 28 seconds to update 500 records on a Access database,
then to me that still sounds like a substantial bottle neck and a
technical problem. At least when considering to write any multi user DB
applications, with a reasonable size database, maybe running the next
5..7 years on customers site.

With those record amounts, something like < 5 seconds or less on a
single user database would make more sense. Yet I have no experience
about Access DB, and those high numbers may be the right ones in there.
MS itself seems to have abandoned Access, and suggests everyone to
choose MSDE database instead.

> I was surprised to find that adLockPessimistic seemed to run
> a bit faster than adLockOptimistic.

I do not remember to have found noticeable speed differences when batch
updating with either of these locking mechanisms. So do you have you
any numbers about these speed differences?

I think that I could have told these speed differences in milliseconds
right away:) When I have to optimize DB actions, find faster methods to
get some DB operations done, I start the job adding some Start / End
timer lines to my app. Check how many seconds each operation takes, and
then show the results on Memo on screen or write them to a log file.
DB1
.