Slow preformance over netwrok (A2K SR-1)

[zinger]

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

[HansV]

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.

[zinger]

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

[MarkLiquorman]

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).

[johnhutchison]

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.

[Granville]

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]

<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

[johnhutchison]

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.

[Granville]

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>