Anyone ever do any timing benchmarking on writing to an Access Database from VB?

jscharpf
jscharpf used Ask the Experts™
on
I'm trying to get a feel for how much time Visual Basic takes to write to a database.
I realize it probably depends on many factors, but I wonder if anyone has done any tests regarding this issue.
I found it took approximately 40 to 50 milliseconds to write a row of twenty single precision values into an Access Database using a P3 833MHZ Windows 2000 machine.
Does this sound correct?

Thanks

Jeff
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I have done some benchmarking and what you have sounds correct given a few constraints:

1) It sounds like you are using an active connection, no Disconnected recordsets
2) You are using pessimistic locking
3) There are indexes on the table
4) You are using synchronous processing
5) The database is on a local network drive.

If any of these is NOT true, I'd say the 40 - 50 MS is pretty high and you should consider optimization techniques.  COnsider the type of cursor you are using, the type of locking, consider using batchupdates (from disconnected recordsets), etc if this eems high to you.

Author

Commented:
Thanks tw.

I am rather new at Access programming, so I don't understand many of the terms. but..

This time includes creating a connection, opening the recordset, putting the data in, and setting the connection = nothing.. It was copied code basically ( I understand this probably isn't the most efficient :)

I think I'm using optimistic.. I got some errors using pessimistic but I can't remember why.. again, i don't understand these, I just copied them.

indexes.. I am not sure.. :(

same for synchronous.. not sure..

the data is on the local C: drive

Actually I now see numbers closer to 100 ms..

I will probably check the things you talked about again.. and I will see about keeping the database open..

Is there a limit on the number of records I can have in an access database?
Is it faster to write one at a time, every second, or to just accumulate, say 10000 data points, and dump them at one time?

I will increase the points because of more questions..

Thanks

Jeff
There is a limit to the size of an Access DB.  It is 2Gig.  Actually this is a file system limitation.  I'm not sure about Access itself, but I can tell you that I've had very large Access DBs (Over 120 MB).  However, it begs the question.  If you think that Access won't be capable of handling the amount of data you want to put into it, you're probably right.  

It is much faster (although more prone to errors) to do bulk data transfers.  These errors increase with the number of users of the database.  Also consider making "stored procedures" bu creating Append/Update queries, etc.  This way you pass less info to/from the database.
If you do large recordset copies, consider the disconnected recordset option.  However, this isn't real good in a multi-user environment as several records can be changed and overwritten by 1 person only to have them changed again by another.

If you have specific concerns regarding Access and your application, I am happy to answer them.


Author

Commented:
Thanks tw, you've given me alot of information!
I did a test and found that it took about 15 seconds to write 6000 records. This will be unacceptable so I have no choice but to write one record at a time..

I will have to consider some alternative I guess...

I guess Access isn't useful to store large amounts of data..
Thanks again for the help!

Jeff
I hope you find the solutions you are looking for.  Perhaps you should look into the scaled down version of SQL Server.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial