ADO.net + MS Access = performance issues

[Dmitry Akselrod]

Hi,

I have a recursive application that walks through a directory structure on a
Hard Drive and writes various file information to a single table in an
Access 2003 database. I am opening a connection to the database at the
start of a worker thread that does all of the work. The connection is
closed when the worker thread completes. I am using a single method to
write a bunch of information to the table.

The application flies initially. However, when there are approximately
500,000 rows in the database, performance begins to decrease exponentially.
After about 800,000 or 900,000 rows, the program is only writing about 2/3
rows per second. If I stop the process and create a new Access DB, the
performance issues disappear until about 500,000 rows are reached again.
Usually the database size is around 150 mb at that time. Interestingly
enough, restarting the application without a new database does not improve
the performance.

Is this just the nature of Access? I am thinking about writing to a new
table once I pass 500,000 rows, or should I create a entirely new database?
Or is there an Access performance tweak for my issue?

Unfortunately, MSDE or SQL is not an option, since I need the app to be
highly portable.

Thanks,

Dmitry

 

[Brooke]

Have you tried to split the data into multiple tables? You could then
create a union query to join all of the data in one view. Maybe limit each
table to 100,000 rows, or even less...

If that doesn't work then split the data into multiple Access DB's using
multiple tables.

SQL Server would be a much better solution, to bad it isn't just a single
file like Access...

 

[Cor Ligthert [MVP]]

Brooke,

SQL Server would be a much better solution, to bad it isn't just a single
file like Access...

SQLExpress can be used like that.

http://msdn2.microsoft.com/library(d=robot)/ms239722.aspx

AFAIK can this as well be done in the connectionstring

Cor

 

[m.posseth]

If an instance of sql server ( MSDE / SQL Express ) it can be used like
that
however the portable issue is probably exactly that ( installing an
instance of SQL )


it would be better if there was an embedded version ( a version that does
not need an install at all )

regards

Michel Posseth

 

[Ken Tucker [MVP]]

Hi,

Maybe doing a repair on the mdb every couple of 100000 records
will help. I know the repair process is not that quick but it will reduce
the size of the database and reindex it.

http://www.knowdotnet.com/articles/compactmdb.html

Ken

 

[tomb]

MS Access is a toy - you need a real database. Even Foxpro would be
better, and it operates like a database file rather than a database server.

Tom

 

[Guest]

Not sure how you are inserting the records but you may be better off creating
a dataset using the dataadaptor then adding the records to the dataset then
using the update method to actually update the database.

 

[Paul Clement]

¤ Hi,
¤
¤ I have a recursive application that walks through a directory structure on a
¤ Hard Drive and writes various file information to a single table in an
¤ Access 2003 database. I am opening a connection to the database at the
¤ start of a worker thread that does all of the work. The connection is
¤ closed when the worker thread completes. I am using a single method to
¤ write a bunch of information to the table.
¤
¤ The application flies initially. However, when there are approximately
¤ 500,000 rows in the database, performance begins to decrease exponentially.
¤ After about 800,000 or 900,000 rows, the program is only writing about 2/3
¤ rows per second. If I stop the process and create a new Access DB, the
¤ performance issues disappear until about 500,000 rows are reached again.
¤ Usually the database size is around 150 mb at that time. Interestingly
¤ enough, restarting the application without a new database does not improve
¤ the performance.
¤
¤ Is this just the nature of Access? I am thinking about writing to a new
¤ table once I pass 500,000 rows, or should I create a entirely new database?
¤ Or is there an Access performance tweak for my issue?
¤
¤ Unfortunately, MSDE or SQL is not an option, since I need the app to be
¤ highly portable.

You may want to try compacting the database if you haven't recently done so. I don't see 150 mb as a
big issue at this point. The max size for an Access database is 2 gb.

There could be other issues, such as the use of an index and/or primary key.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Paul Clement]

¤ Have you tried to split the data into multiple tables? You could then
¤ create a union query to join all of the data in one view. Maybe limit each
¤ table to 100,000 rows, or even less...
¤
¤ If that doesn't work then split the data into multiple Access DB's using
¤ multiple tables.
¤
¤ SQL Server would be a much better solution, to bad it isn't just a single
¤ file like Access...

It wouldn't be a *better* solution because it lacks portability (as he requires).


Paul
~~~~
Microsoft MVP (Visual Basic)