|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ADO.net + MS Access = performance issuesI 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... Show quoteHide quote "Dmitry Akselrod" <dmitry@nospam.com> wrote in message news:-oSdnWN1Mckck3XeRVn-qQ@comcast.com... > 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,
> SQL Server would be a much better solution, to bad it isn't just a single SQLExpress can be used like that.> file like Access... > http://msdn2.microsoft.com/library(d=robot)/ms239722.aspx AFAIK can this as well be done in the connectionstring Cor 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 Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:OamOA36KGHA.2320@TK2MSFTNGP11.phx.gbl... > 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 > > > On Mon, 6 Feb 2006 22:24:37 -0600, "Brooke" <tbroo***@hotmail.com> wrote: ¤ 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) 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 ------------- Show quoteHide quote "Dmitry Akselrod" <dmitry@nospam.com> wrote in message news:-oSdnWN1Mckck3XeRVn-qQ@comcast.com... > 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 > 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 Dmitry Akselrod wrote: Show quoteHide quote >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 > > > > 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. -- Show quoteHide quoteDennis in Houston "Dmitry Akselrod" wrote: > 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 > > > On Mon, 6 Feb 2006 22:05:41 -0500, "Dmitry Akselrod" <dmitry@nospam.com> wrote: ¤ 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) 
Other interesting topics
VB.NET Structures and Union Help.
how to group similar events? 2nd Post: Problem adding events to controls created at run-time KeyUp + KeyDown Event Handler Calculate elapsed time Multiple threads using a shared printer resource Problems with ByRef parameters Help needed on creating Shared Directory continue debugging without interrupting progress bar |
|||||||||||||||||||||||
