Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Slow preformance over netwrok (A2K SR-1)

    I have a database which links to an Excel workbook.
    The workbook is denormalised and I use VBA code, the concept of which was provided on this site,
    to traverse through the Excel columns and insert these into a normalised table.

    With the db and spreadsheet on my local Pc or with
    the db on my local Pc with the linked spreadsheet on the file server
    it takes less than 10 seconds to create ~22k lines from a 600 row by 50 column spreadsheet (not all cells are filled)

    With both the db and spreadsheet sitting on the network file server the whole process takes ~20 minutes.

    This app needs to be accessed by other users so it needs to be available from the file server.
    What can I do to increase the performance when both sit on the file server?

    Regards
    Zinger

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

    Re: Slow preformance over netwrok (A2K SR-1)

    You might check (or have the network people check) the virus scan settings for the network. Since Access saves each to disk each time a record is updated, it generates a lot of write activity. If the virus scanner is set to check each write to a network disk, it causes considerable overhead. The way writes to a network disk are cached may also matter, but you'd need a network expert to analyze and fine-tune that.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow preformance over netwrok (A2K SR-1)

    Thanks Hans,

    We have an outsourcer who looks after our network and our internal
    IT staff have told me that these sort of queries re virus checking and
    caching are outside the scope of the SLA's of our current contract.

    Looks like the users will have to accept the performance issues unless
    I can put up a business case to include the deployment of the Access db
    into a Zen package. (I won't hold my breath)

    Regards
    Zinger

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,430
    Thanks
    1
    Thanked 33 Times in 33 Posts

    Re: Slow preformance over netwrok (A2K SR-1)

    Adding on to what Hans' said, you might check the virus scanning of the workstations to make sure they aren't scanning network drives (leave that to the server).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Slow preformance over netwrok (A2K SR-1)

    When I look at Help on this on a machine running XP, it tells me that the Page Timeout is measured in tenths of a second.
    Regards
    John



  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Cairns, Queensland, Australia
    Posts
    885
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow preformance over netwrok (A2K SR-1)

    You're right! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> That'll teach me to take MS KB doco for gospil.

    I will adjust my original post accordingly, to avoid confusion.

    Thank you.
    Granville

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Cairns, Queensland, Australia
    Posts
    885
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow preformance over netwrok (A2K SR-1)

    <P ID="edit" class=small>(Edited by Granville on 14-Apr-05 10:25. Changed "milliseconds" to "tenth of a second" to avoid confusion, see subsequent posts. - Granville)</P>If you happen to be running either 2000 or XP versions of Access you should check your setting for ODBC. Acording to what I have read earlier version under seconds for "Page Timeout" while the updated one use tenth of a second. This will cause extremely slow responses accross a network link.

    In WindowsXP click "Settings" - "Control Panel" - "Admin Tools" - "Data Source (ODBC)" - select the "User DNS" tab - "MS Access Database" - Configure - Options - "Page Timeout" and change "5" to "50".

    HTH
    Granville

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Slow preformance over netwrok (A2K SR-1)

    I have just looked at this article in the Knowledge base.

    It looks like you were right all along, and it is help that is worng.
    Regards
    John



  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Cairns, Queensland, Australia
    Posts
    885
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow preformance over netwrok (A2K SR-1)

    I've actually asked MS to clarify whether the setting is on millis or tenths.

    Sorry about the spelling mistook, I got confused 'cos I was trying to make the db performance wrok. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Granville

Posting Permissions

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