Andy Lacey
andy at minstersystems.co.uk
Tue Oct 21 06:43:54 CDT 2003
Absolutely endorse this. I've got a big MDB with lots of tables. It was taking about 20 MINUTES to relink before I kept a pointer to the data open. Now does it in 10-15 SECONDS. Massive difference. Andy Lacey http://www.minstersystems.co.uk > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > Stuart Sanders > Sent: 21 October 2003 11:06 > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] ..Performance ADO - DAO .. > > > Not necessarily true... > > For example if you attempt to relink or refresh links on a > multiuser backend, maintaining an open db connection to the > db can save minutes. I've seen a 5 minute relink operation > without an open connection go down to 15 seconds with an open > connection. As opposed to opening and closing the BE db for > each table to be relinked. > > Stuart > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Haslett, > > Andrew > > Sent: Tuesday, 21 October, 2003 5:39 PM > > To: 'Access Developers discussion and problem solving' > > Subject: RE: [AccessD] ..Performance ADO - DAO .. > > > > > > I believe you are don't it correctly IMO. > > > > There is negligible performance loss in closing and re-opening > > connections that I'm aware of. > > > > Cheers, > > A > > > > -----Original Message----- > > From: marcel.vreuls at achmea.nl [mailto:marcel.vreuls at achmea.nl] > > Sent: Tuesday, 21 October 2003 6:37 PM > > To: Access Developers discussion and problem solving > > Subject: [AccessD] ..Performance ADO - DAO .. > > > > > > Dear group, > > > > Just to let you know. Turning of the subdatasheets and keeping the > > connection open shows the following figure. > > > > 10MB network (both tables contain about 15.000 records) > > Reading a customertable was 10 seconds now 3 seconds > > Reading a order table was 7 seconds > > now 2 seconds > > > > 100 MB network (both tables contain about 15.000 records) > > Reading a customertable was 3 seconds now > 1 seconds > > Reading a order table was 2 seconds > > now 1 seconds > > > > > > In the replies someone asked why I closed all database connections > > after use. I do this to keep memory use to a minimum. When I keep > > those database > > connections open at a certain time the database gives memory > > errors. But > > reading and thinking I just thought of something. The structure I > > currently use is > > > > Function fncUpdateOrderStatus() as boolean > > Dim db as dao.databse > > Dim rs as dao.recordset > > > > set db = currentdb() > > > > > the works> > > > > db.close > > rs.close > > set db = nothing > > set rs= nothing > > end function > > > > > > So I define the db many, many times and close it also. Is > it better to > > dim the db as global-public variable and use this in each > > function???????? so > > in each function i can remove the Dim db and close.db??? > > > > Gr. marcel > > > > > > > > > > > > > > > > "Charlotte Foust" > <cfoust at infostatsystems.com>@databaseadvisors.com op > > 20-10-2003 17:47:40 > > > > Antwoord aub aan Access Developers discussion and problem solving > > <accessd at databaseadvisors.com> > > > > Verzonden door: accessd-bounces at databaseadvisors.com > > > > > > Aan: "Access Developers discussion and problem solving" > > <accessd at databaseadvisors.com> > > cc: > > > > Onderwerp: RE: [AccessD] Performance ADO - DAO or ?? > > > > > > Two things that bog down Access 2000 and 2002 are Name > Autocorrect and > > Subdatasheets. If you turn those two things off, you'll see an > > improvement in performance. Subdatasheets also need to be > turned off > > in the backend. > > > > Charlotte Foust > > > > -----Original Message----- > > From: marcel.vreuls at achmea.nl [mailto:marcel.vreuls at achmea.nl] > > Sent: Monday, October 20, 2003 3:37 AM > > To: Access Developers discussion and problem solving > > Subject: [AccessD] Performance ADO - DAO or ?? > > > > > > Dear group, > > > > It has been a while since I looked into this group. My daughter (7 > > months) took up most of my time recently. But work goes on > so I would > > like to ask > > you the following. > > > > I am strugling with the following. I have about 6 access 2000 > > applications distributed among several customers. All database use > > DAO and have a FE > > and BE. It works fine but know customers start complaining about > > performance. Expecially on a network envirnement. I have > > - Looked through all the queries and changed all the SELECT > > * queries > > with only the data wich is used > > - closed all recordset and database connection at the end of > > eacht function > > of module. > > - Removed databound forms as much as possible. This is a lot > > of work and I > > do not know if i want to do this. > > > > But this all did not improved much. > > > > I am thinking about the following > > - Is it a option to move to ADO instead of DAO. Does this improve > > performance?. In my tests it does not matter much but perhaps I am > > missing something > > - The performance analyser in Access tells me to use fewer > > controls on a > > form. If I do this does this improve performance > > - moving to sql server. But this will cost my customer a lot > > of money and > > the won´t be happy. > > > > > > Any suggestions, ideas?? > > I am thinking to rewrite the programms to VB, anyone > experience, tips, > > tricks in this. > > > > Thansk marcel > > > > > > > > > > > > "dave sharpe" <davesharpe2 at cox.net>@databaseadvisors.com op > 19-10-2003 > > 06:32:54 > > > > Antwoord aub aan Access Developers discussion and problem solving > > <accessd at databaseadvisors.com> > > > > Verzonden door: accessd-bounces at databaseadvisors.com > > > > > > Aan: "Access Developers discussion and problem solving" > > <accessd at databaseadvisors.com> > > cc: > > > > Onderwerp: Re: [AccessD] ado recordcount > > > > > > > > John - This article may be of benefit > > Dave > > > > PRB: ADO: Recordcount May Return -1 > > > > http://support.microsoft.com/default.aspx?scid=http://support. > > microsoft.com: 80/support/kb/articles/q194/9/73.asp&NoWebContent=1 > > > > > > > > ----- Original Message ----- > > From: "John Colby" <jcolby at colbyconsulting.com> > > To: "AccessD" <AccessD at databaseadvisors.com> > > Sent: Saturday, October 18, 2003 11:38 PM > > Subject: [AccessD] ado recordcount > > > > > > I am opening a recordset (raw table) that contains records. > > Move last, move > > first. EOF and BOF are both false. > > > > Recordcount = -1 > > > > What gives? How do I tell how many records in the recordset? > > > > John W. Colby > > www.colbyconsulting.com > > > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > ***************************************************************** > > Dit bericht is bij binnenkomst gecontroleerd op de > > aanwezigheid van virussen. Er zijn geen (bekende) > > virussen gevonden. Active > > ***************************************************************** > > > > > > > > > > > > > > ********************* DISCLAIMER ********************* > > De informatie in dit e-mail bericht is uitsluitend > > bestemd voor de geadresseerde. Verstrekking aan > > en gebruik door anderen is niet toegestaan. > > Door de electronische verzending van het bericht > > kunnen er geen rechten worden ontleend aan de > > informatie. > > ************************************************************ > > > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > ***************************************************************** > > Dit bericht is bij binnenkomst gecontroleerd op de > > aanwezigheid van virussen. Er zijn geen (bekende) > > virussen gevonden. Active > > ***************************************************************** > > > > > > > > > > > > > > ********************* DISCLAIMER ********************* > > De informatie in dit e-mail bericht is uitsluitend > > bestemd voor de geadresseerde. Verstrekking aan > > en gebruik door anderen is niet toegestaan. > > Door de electronische verzending van het bericht > > kunnen er geen rechten worden ontleend aan de > > informatie. > > ************************************************************ > > > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > IMPORTANT - PLEASE READ ******************** > > This email and any files transmitted with it are > confidential and may > > contain information protected by law from disclosure. > > If you have received this message in error, please notify the sender > > immediately and delete this email from your system. > > No warranty is given that this email or files, if attached to this > > email, are free from computer viruses or other defects. They > > are provided on the basis the user assumes all responsibility for > > loss, damage or consequence resulting directly or indirectly from > > their use, whether caused by the negligence of the sender or not. > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > >