ADO.net + MS Access = performance issues

  • Thread starter Thread starter Dmitry Akselrod
  • Start date Start date
D

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
 
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...
 
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
 
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
 
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.
 
¤ 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)
 
¤ 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)
 
Back
Top