Re: How I dramatically sped up DB file updating


"DB-1" <db1@xxxxxxxxxxxxxxxxxxx> wrote in message
news:43A3FC6B.6D89A84A@xxxxxxxxxxxxxxxxxxxxxx
> 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.

I have never worked much with DBs. Most of my programming has used random
number generators and simulation.

> 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.

Yet MS just about gives its software away, at least to public entities,
which is my current client. They have Access as part of Office 2003 and
that's what they want - so that's what I use.

>> 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 averaged 30.5 seconds with adLockOptimistic and 29.5 seconds with
adLockPessimistic. IIRC the BatchUpdate approach was about 10% faster than
Update. All of these times are when I updated every record, whether or not
I needed to.

The way I get execution time is:

Prev := Time;

loop

looptime := SecsPerDay * (Time - Prev)

This approach isn't great...it doesn't give consistent times. The times
varied from 30.5 to 29.5, just rerunning the exact same program. I've
attributed this to the fact that there's always "stuff" going on in the
background of Windows - which affects the overall time. How do you get
consistent, accurate times?

> 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


.