Haslett, Andrew
andrew.haslett at ilc.gov.au
Wed Oct 22 01:13:14 CDT 2003
Ooops, noticed you were using a custom DLL - pretty certain if the same implications apply, however not completely sure. Drawbacks are more apparent in a multi-user environment rather than a low traffic one.. Cheers, Andrew -----Original Message----- From: Haslett, Andrew [mailto:andrew.haslett at ilc.gov.au] Sent: Wednesday, 22 October 2003 2:50 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] ..Performance ADO - DAO .. No No No! Never place objects in session scope (assume you're using ASP). Causes thread affinity and you lose connection pooling. http://www.devx.com/asp/Article/16802/0/page/2 http://support.microsoft.com/default.aspx?scid=kb;EN-US;243543 -----Original Message----- From: Drew Wutka [mailto:DWUTKA at marlow.com] Sent: Wednesday, 22 October 2003 1:54 PM To: ''Access Developers discussion and problem solving' ' Subject: RE: [AccessD] ..Performance ADO - DAO .. Actually, I keep the connection open in some situations. We have a little delay between our web and Intranet servers, going to our main file server. If I need to connect to a db on the file server, I put a connection object (actually, a class from a .dll, that has a 'live connection') as a session variable. On all of the pages then, it determines if the object exists or not, if so, it uses the existing object, and it's connection, if not, it creates it again. Speeds things up immensely. Drew -----Original Message----- From: Haslett, Andrew To: 'Access Developers discussion and problem solving' Sent: 10/21/03 6:41 PM Subject: RE: [AccessD] ..Performance ADO - DAO .. Agreed on the split DB. I was thinking of a single database. Guess I've done a fair bit of web stuff lately and keeping connections open is a 'no-no'. Especially with ADO where you can utilise connection poooling. Access is a different story through, especially considering its often pulling entire tables/recordsets across the network to the FE. Cheers, A -----Original Message----- From: Stuart Sanders [mailto:stuart at pacific.net.hk] Sent: Tuesday, 21 October 2003 7:36 PM 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 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 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 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.