Results 1 to 5 of 5
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO speed (XP/2000)

    That seems quite slow to me, but much depends on the circumstances:
    - Is the recordset sorted, and if so, is there an index on the field(s) on which you sort?
    - Are the records stored in the database running the code, or in another database (through linked tables)?
    - Is the database containing the records stored on a local hard disk or on a network disk?
    - Is the database compacted?
    - Do you have security software (e.g. antivirus) scanning every disk action?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO speed (XP/2000) and db bloat

    I've just written and run a routine to copy values in two fields in one record to two different fields in the following record.

    This took about 20 minutes for 30000 records. Does this sound about right or is it really slow?

    Basically all the code does is this (using a DAO recordset)

    Do while not EOF
    edit
    set field values (assuming default values for first run of loop)
    set variables for use in next loop
    update
    move next
    Loop

    I've realised the recordset will usually contain <1000 records but I'm curious about the speed all the same.

    Just noticed the database has ballooned to 125MB (it was about 3MB) <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO speed (XP/2000)

    It took a while to compact back down to 5MB

    The recordset comprised a table with no sorting applied. I've just checked and there are no indexes in the table.
    The table contains 6 fields (2 keys - integers - and 4 other fields - all doubles).

    The code is in a module in the same db as the table.

    The database is on a network drive.

    Seems my code was making it bloat (and I guess impacting the speed) but that's a huge amount.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO speed (XP/2000)

    Setting a primary key on the table (for example an autonumber field) should increase looping speed a bit, but proably not much in this situation.

    Temporarily copying the database to a local drive might help, depending on the speed of your network connection.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO speed (XP/2000)

    I just indexed both key fields and ran it again - 3 minutes! and no massive bloat.

    I don't know if anything was happening on our network the first time but I'm a lot happier with this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •