Betreft: RE: [AccessD] ..Performance ADO - DAO ..

Charlotte Foust cfoust at infostatsystems.com
Tue Oct 21 12:38:45 CDT 2003


Remember though that DBEngine is faster but then you have to force a refresh to see the most current data.  CurrentDb refreshes automatically, which is one reason it's slower.  If you factor in the refresh, the speed is about comparable for either method.

Charlotte Foust

-----Original Message-----
From: MartyConnelly [mailto:martyconnelly at shaw.ca] 
Sent: Tuesday, October 21, 2003 9:18 AM
To: Access Developers discussion and problem solving
Subject: Re: Betreft: RE: [AccessD] ..Performance ADO - DAO ..


 CurrentDb returns a *copy* of DBEngine(0)(0).  If you don't assign it 
to an object variable and then use that object, you're in danger of it 
going out of scope - thus invalidating any object that
points to it.
Actually, CurrentDb is somewhat inefficient because it validates all the existing objects to create the copy.  This can be measurably slower than just referencing DBEngine(0)(0) in databases that have thousands of objects. I tend to use CurrentDb a lot because I'm lazy.  But in most cases, I'm establishing a database object that the procedure references many times (opening recordsets and the like). However if you use wizards, access opens DBEngine(0)(1) and things can 
go a little awry.
Larry Linson had a long series of posts on this in 
comp.databases.ms-access a few years back on all the qualifications.

if you replace use of your persistent variable with a function that returns an object of type database, and that passes a reference to a persistent variable, you don't have to worry about re-initializing your persistent variable (this was Michael Kaplan's
solution):

  Global dbCurr As DAO.Database

  Public Function dbCurrent() As DAO.Database
    If dbCurr Is Nothing Then
       Set dbCurr = CurrentDB()
    End If
    Set dbCurrent = dbCurr
  End Function

Then in code, you simply use dbCurrent ever time you would call
DBEngine(0)(0) or CurrentDB() or in place of any database variables. Instead of:

  Dim tdf As TableDef

  For Each tdf in DBEngine(0)(0).TableDefs
   ...
  Next tdf

  For Each tdf in CurrentDB().TableDefs
   ...
  Next tdf

  Dim db As DAO.Database

  Set db = DBEngine(0)(0)
  For Each tdf in db.TableDefs
   ...
  Next tdf

  Set db = CurrentDB()
  For Each tdf in db.TableDefs
   ...
  Next tdf

  Set tdf = Nothing

you would use:

  Dim tdf As TableDef

  For Each tdf in dbCurrent.TableDefs
   ...
  Next tdf

  Set tdf = Nothing

You don't have to worry about anything at all. You will initialize the global variable only when you need to, and the reference returned will always be guaranteed to be accurate.


marcel.vreuls at achmea.nl wrote:

>Andrew,
>
>I noticed:-). I've got some work to do........
>Just a question. Can I declare the DB and current db globaly in a 
>module or do I have to declare it in every class/module seperate?
>
>Thanks, marcel
>
>
>
>
>"Haslett, Andrew" <andrew.haslett at ilc.gov.au>@databaseadvisors.com op 
>21-10-2003 11:39:22
>
>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 ..
>
>
>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
>
>
>*****************************************************************
>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
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list