MS Access Multiuser - Enormous performance hit and network traffic (locking problem?)

Author

Enormous performance hit and network traffic (locking problem?)

Peter

2005-05-16, 3:24 am

Hi,

I use Access 2002/XP with the latest service pack.
I have my database split in a FE with linked tables to a BE that
resides on a network share on Windows 2000.

I use VBA in the FE to automatedly recieve data that I write to a
table in the back-end. For this I use ADO to the back-end (a
'seperate' ADO Connection, not CurrentProject.Connection).

I also have a form on which some controls are data-bound.

I noticed that writing the data took rather long and brought an
amazing network load. This could be as much as 150megabyte of traffic
over the LAN recieve+send for writing 1000 records to 1 table with 1
field per row inserted. The whole operation could take over 10
minutes, but often less as well (few minutes).

When I started investigating using a simple test setup, I noticed that
as soon as I had NO controls on the form data-bound nor the form
itself, the writing of 1000 records in a For Next loop, took almost 2
seconds.

As soon as I data-bound the form in my test setup again, it could take
anywhere from 10 seconds to over 5 minutes, generating LOADS of
network traffic @ 600kbyte/second or so. The back-end is on a Pentium
233MHz, but since it can also perform the operation in less than 2
seconds with minimal network load, this is not any cause.

I tried writing the records as SQL over the ADODB.Connection.Execute,
I tried a recordset and .Addnew and then .Update, various locking
options, cursor type to client, also .UpdateBatch with locking set to
BatchLockOptimistic,
none of which seemed to help the issue.

I also disabled OpLocks on the fileserver via HKLM\System
\CurrentControlSet\S
ervices\lanmanserver
\parameters\EnableOp
locks set
to a 0 REG_DWORD.

I also used ADO 2.1 as well as version 2.7.

The same problem occurs if I use ADO + VB 6.0 to connect to the back-
end.
It also does not matter if I bind the form to just 1 table and write
to another table in the same MDB, the performance hit is there. (and
it's HUGE)

Perhaps I should not be using ADO? Why I use ADO? Because I recieve
the data to insert into the table automatedly, not using a Form. I
have wanted to discard ADO completely, but I can't figure out how to
pass parameters to an Action Query via DoCmd.OpenQuery or otherwise
use a resolveable reference in my query. I did see it's possible to
make the query call a public function that returns a publically
defined variable but didn't think I had to go that far.

Also I set both MDBs to SHARED mode (not exclusive) and I have tried
several record locking options in the data-bound control (the MDB
setting is set to no locking).

I posted this to multi-user, because first of all if my form is bound
to tables (should I try binding it to queries??) and on top I open an
ADO Connection (that makes 2 'connections' somewhat) and I intend to
have my back-end accessed via ASP (using ADO) for web publishing.

I am a beginner but I have now tried various options and quite some
reading and am out of ideas. Last thing I can mention is that an LDB
file is created at the backend, I don't know if that is useful.

Thanks in advance,

Peter.

TomU

2005-05-16, 7:24 am

Obviously, you've done your homework on this so perhaps this is old news to
you but take a look at this list of performance tips:
http://www.granite.ab.ca/access/performancefaq.htm

I don't think that using ADO is the issue here. I hear varying claims about
the relative speed of ADO vs DAO but the differences appear marginal to me.

Have you considered whether network traffic is an issue? Your missive seems
to show a great deal of variability in elapsed time doing the same test so I
wonder if there is activity on the LAN sometimes causing the problem or maybe
a PC with a bad NIC card generting lots of bad packets and error messages.

FWIW.

TomU
"Peter" wrote:

> Hi,
>
> I use Access 2002/XP with the latest service pack.
> I have my database split in a FE with linked tables to a BE that
> resides on a network share on Windows 2000.
>
> I use VBA in the FE to automatedly recieve data that I write to a
> table in the back-end. For this I use ADO to the back-end (a
> 'seperate' ADO Connection, not CurrentProject.Connection).
>
> I also have a form on which some controls are data-bound.
>
> I noticed that writing the data took rather long and brought an
> amazing network load. This could be as much as 150megabyte of traffic
> over the LAN recieve+send for writing 1000 records to 1 table with 1
> field per row inserted. The whole operation could take over 10
> minutes, but often less as well (few minutes).
>
> When I started investigating using a simple test setup, I noticed that
> as soon as I had NO controls on the form data-bound nor the form
> itself, the writing of 1000 records in a For Next loop, took almost 2
> seconds.
>
> As soon as I data-bound the form in my test setup again, it could take
> anywhere from 10 seconds to over 5 minutes, generating LOADS of
> network traffic @ 600kbyte/second or so. The back-end is on a Pentium
> 233MHz, but since it can also perform the operation in less than 2
> seconds with minimal network load, this is not any cause.
>
> I tried writing the records as SQL over the ADODB.Connection.Execute,
> I tried a recordset and .Addnew and then .Update, various locking
> options, cursor type to client, also .UpdateBatch with locking set to
> BatchLockOptimistic,
none of which seemed to help the issue.
>
> I also disabled OpLocks on the fileserver via HKLM\System
> \CurrentControlSet\S
ervices\lanmanserver
\parameters\EnableOp
locks set
> to a 0 REG_DWORD.
>
> I also used ADO 2.1 as well as version 2.7.
>
> The same problem occurs if I use ADO + VB 6.0 to connect to the back-
> end.
> It also does not matter if I bind the form to just 1 table and write
> to another table in the same MDB, the performance hit is there. (and
> it's HUGE)
>
> Perhaps I should not be using ADO? Why I use ADO? Because I recieve
> the data to insert into the table automatedly, not using a Form. I
> have wanted to discard ADO completely, but I can't figure out how to
> pass parameters to an Action Query via DoCmd.OpenQuery or otherwise
> use a resolveable reference in my query. I did see it's possible to
> make the query call a public function that returns a publically
> defined variable but didn't think I had to go that far.
>
> Also I set both MDBs to SHARED mode (not exclusive) and I have tried
> several record locking options in the data-bound control (the MDB
> setting is set to no locking).
>
> I posted this to multi-user, because first of all if my form is bound
> to tables (should I try binding it to queries??) and on top I open an
> ADO Connection (that makes 2 'connections' somewhat) and I intend to
> have my back-end accessed via ASP (using ADO) for web publishing.
>
> I am a beginner but I have now tried various options and quite some
> reading and am out of ideas. Last thing I can mention is that an LDB
> file is created at the backend, I don't know if that is useful.
>
> Thanks in advance,
>
> Peter.
>

Peter

2005-05-16, 8:24 pm

Hello,

Thanks for your reply. I had a look before indeed at that site you
gave
me, I will visit it again though.

You suggested that network traffic that exists external to the
operation
could be the issue, but I am as close to certainty as possible that it
is not. Alone the fact that the table inserts over ADO become fast
again
(and generate almost no network traffic) each time I unbind the form,
and that the excessive network traffic (and CPU usage) only occurs
just
as I hit my command button to trigger the record insert operation, and
stops as soon as Access GUI becomes responsive again, makes me
strongly
think of Access being into play.

My network concists of 2 computers cross-linked on 100Mbit, the LAN
performs well otherwise, over network shares or other traffic.

Is this not some 'simple' concurrency/locking problem? Access trying
to
write to the table, getting denied, trying again, and all this very
rapidly successive, and sometimes it being 'lucky' and able to insert
the record? I think also locally this is the issue, not only over
network, but just locally I notice it much less, apart from CPU being
eaten 95% by Access.

Peter.

"=?Utf-8?B?VG9tVQ==?=" <TomU@discussions.microsoft.com> wrote in
news:48B268FB-D05E-489F-8989- 8511A3A0C4D9@microso
ft.com:

> Obviously, you've done your homework on this so perhaps this is old
> news to you but take a look at this list of performance tips:
> http://www.granite.ab.ca/access/performancefaq.htm

>
> I don't think that using ADO is the issue here. I hear varying
claims
> about the relative speed of ADO vs DAO but the differences appear
> marginal to me.
>
> Have you considered whether network traffic is an issue? Your
missive
> seems to show a great deal of variability in elapsed time doing the
> same test so I wonder if there is activity on the LAN sometimes
> causing the problem or maybe a PC with a bad NIC card generting lots
> of bad packets and error messages.
>
> FWIW.
>
> TomU
> "Peter" wrote:
>

david epsom dot com dot au

2005-05-16, 8:24 pm

Windows will 'opportunistically' upgrade a shared
file lock to an exclusive lock if possible. When
you have an exclusive lock on a file, Windows will
cache the file locally. And if you are working
against the local network client cache, there won't
be much network traffic at all.

If you then try to make a second connection to the
file, Windows will try for a shared lock, fail,
notify the caching client, wait for the file to
be copied back across the network from the client,
set the shared lock, and then respond. Then all
reads and writes will be written through to the
server: you won't get read ahead caching or write
caching.

To check the effect of this behaviour, turn off
OPLOCKS at the network client and/or the file server:
http://support.microsoft.com/kb/296264/
Configuring opportunistic locking in Windows

To avoid this behaviour you should avoid making a
second connection to a file. To enforce this, you
should make all your linked table links 'exclusive'.
This has the effect of opening the file in exclusive
mode, which prevents you from making a second connection,
and also reduces Access/Jet overhead.

Conversely, if you intend to use the data in a multi-user
situation, you should always make sure that the file
is open before use. This is not just for testing: opening
a file has a lot of overhead, (and in Access, may include
the creation of a lock file at the same time). You
should open a link to a shared file at application
startup, and maintain it until the application is
closed, so that you don't have the overhead of repeatedly
opening and closing the connection. When you have
a local file, or opportunistic locking, the overhead
is not so bad, but when you have a shared file
across the network it can introduce noticeably
delays in your processes.

(david)

"Peter" <p@x.com> wrote in message
news:Xns965834B80AD6
5pxcom@194.109.133.133...
> Hi,
>
> I use Access 2002/XP with the latest service pack.
> I have my database split in a FE with linked tables to a BE that
> resides on a network share on Windows 2000.
>
> I use VBA in the FE to automatedly recieve data that I write to a
> table in the back-end. For this I use ADO to the back-end (a
> 'seperate' ADO Connection, not CurrentProject.Connection).
>
> I also have a form on which some controls are data-bound.
>
> I noticed that writing the data took rather long and brought an
> amazing network load. This could be as much as 150megabyte of traffic
> over the LAN recieve+send for writing 1000 records to 1 table with 1
> field per row inserted. The whole operation could take over 10
> minutes, but often less as well (few minutes).
>
> When I started investigating using a simple test setup, I noticed that
> as soon as I had NO controls on the form data-bound nor the form
> itself, the writing of 1000 records in a For Next loop, took almost 2
> seconds.
>
> As soon as I data-bound the form in my test setup again, it could take
> anywhere from 10 seconds to over 5 minutes, generating LOADS of
> network traffic @ 600kbyte/second or so. The back-end is on a Pentium
> 233MHz, but since it can also perform the operation in less than 2
> seconds with minimal network load, this is not any cause.
>
> I tried writing the records as SQL over the ADODB.Connection.Execute,
> I tried a recordset and .Addnew and then .Update, various locking
> options, cursor type to client, also .UpdateBatch with locking set to
> BatchLockOptimistic,
none of which seemed to help the issue.
>
> I also disabled OpLocks on the fileserver via HKLM\System
> \CurrentControlSet\S
ervices\lanmanserver
\parameters\EnableOp
locks set
> to a 0 REG_DWORD.
>
> I also used ADO 2.1 as well as version 2.7.
>
> The same problem occurs if I use ADO + VB 6.0 to connect to the back-
> end.
> It also does not matter if I bind the form to just 1 table and write
> to another table in the same MDB, the performance hit is there. (and
> it's HUGE)
>
> Perhaps I should not be using ADO? Why I use ADO? Because I recieve
> the data to insert into the table automatedly, not using a Form. I
> have wanted to discard ADO completely, but I can't figure out how to
> pass parameters to an Action Query via DoCmd.OpenQuery or otherwise
> use a resolveable reference in my query. I did see it's possible to
> make the query call a public function that returns a publically
> defined variable but didn't think I had to go that far.
>
> Also I set both MDBs to SHARED mode (not exclusive) and I have tried
> several record locking options in the data-bound control (the MDB
> setting is set to no locking).
>
> I posted this to multi-user, because first of all if my form is bound
> to tables (should I try binding it to queries??) and on top I open an
> ADO Connection (that makes 2 'connections' somewhat) and I intend to
> have my back-end accessed via ASP (using ADO) for web publishing.
>
> I am a beginner but I have now tried various options and quite some
> reading and am out of ideas. Last thing I can mention is that an LDB
> file is created at the backend, I don't know if that is useful.
>
> Thanks in advance,
>
> Peter.

Peter

2005-05-17, 8:25 pm

Hi,

Thank you for explaining the processes that take place.
I have changed "tons" of things, and have indeed been able to
optimize performance.

I found out that writing like 12000 records to a table with
referential integrity to another, takes a LOT more resources
than when it is 'un-related'.

When having a FE/BE setup, and using an action query in the FE
(with the 1 source table on which the query is based also in
the FE) to update a linked table in the BE, it generates a LOT
more network traffic when this target table has relations with
ref. integrity than when it has not! In time this difference
is about 1 to 30, so 30 times faster without relations in the
target table (2 seconds opposed to 1 minute). Also during the
entire minute network traffic is quite high (800kbyte/s), as
opposed to otherwise it's just 2 seconds of that throughput.

Is this normal behaviour or can I do anything about it? The
field in the target table is set to TEXT, 255chars, Unicode
compress set to YES and INDEXED set to YES but dupes OK. This
works fast if that field is not related.

The table has got 300 records now, and I want to add 12000 to
it, and I will regularly add quite big amounts of records, so
I'd want it optimized. I can of course remove relations
programmatically and re-add them after writing, not sure if
it's a good idea.

I disabled opportunistic locking, in fact my MDB is intended
for multi-user (serve a few users over ASP).

This MS site had tons of tips too of which I am still to try
one:

http://support.microsoft.com/defaul...kb;en-us;889588

"david epsom dot com dot au" < david@epsomdotcomdot
au> wrote in
news:#2m$4enWFHA.3188@TK2MSFTNGP09.phx.gbl:

> Windows will 'opportunistically' upgrade a shared
> file lock to an exclusive lock if possible. When
> you have an exclusive lock on a file, Windows will
> cache the file locally. And if you are working
> against the local network client cache, there won't
> be much network traffic at all.
>
> If you then try to make a second connection to the
> file, Windows will try for a shared lock, fail,
> notify the caching client, wait for the file to
> be copied back across the network from the client,
> set the shared lock, and then respond. Then all
> reads and writes will be written through to the
> server: you won't get read ahead caching or write
> caching.
>
> To check the effect of this behaviour, turn off
> OPLOCKS at the network client and/or the file server:
> http://support.microsoft.com/kb/296264/
> Configuring opportunistic locking in Windows
>
> To avoid this behaviour you should avoid making a
> second connection to a file. To enforce this, you
> should make all your linked table links 'exclusive'.
> This has the effect of opening the file in exclusive
> mode, which prevents you from making a second connection,
> and also reduces Access/Jet overhead.
>
>
> Conversely, if you intend to use the data in a multi-user
> situation, you should always make sure that the file
> is open before use. This is not just for testing: opening
> a file has a lot of overhead, (and in Access, may include
> the creation of a lock file at the same time). You
> should open a link to a shared file at application
> startup, and maintain it until the application is
> closed, so that you don't have the overhead of repeatedly
> opening and closing the connection. When you have
> a local file, or opportunistic locking, the overhead
> is not so bad, but when you have a shared file
> across the network it can introduce noticeably
> delays in your processes.
>
> (david)
>
>
>
>
>
>
> "Peter" <p@x.com> wrote in message
> news:Xns965834B80AD6
5pxcom@194.109.133.133...
that[color=darkred]
write to a[color=darkred]
(a[color=darkred]
brought an[color=darkred]
of traffic[color=darkre
d]
table with 1[color=darkred]
10[color=darkred]
noticed that[color=darkred]
form[color=darkred]
took almost 2[color=darkred]
could take[color=darkred]
LOADS of[color=darkred]
a Pentium[color=darkre
d]
than 2[color=darkred]
ADODB.Connection. Execute,[color=darkr
ed]
locking[color=darkre
d]
locking set to[color=darkred]
issue.[color=darkred]
\EnableOplocks set[color=darkred]
the back-[color=darkred]
and write[color=darkred]

there. (and[color=darkred]
recieve[color=darkre
d]
Form. I[color=darkred]
out how to[color=darkred]
otherwise[color=dark
red]
possible to[color=darkred]
publically[color=dar
kred]
have tried[color=darkred]

(the MDB[color=darkred]
form is bound[color=darkred]

I open an[color=darkred]
intend to[color=darkred]
publishing.[color=darkred]
quite some[color=darkred]
that an LDB[color=darkred]
useful.[color=darkred]
>
>
>

david epsom dot com dot au

2005-05-18, 3:24 am

> is about 1 to 30, so 30 times faster without relations in the

I don't know anything about that. Have you changed
the Subdatasheet Name property from [Auto] to [None]
on each table in the BE?

(david)

"Peter" <p@x.com> wrote in message
news:Xns9659EA07361A
Dpxcom@194.109.133.133...[color=darkred]
> Hi,
>
> Thank you for explaining the processes that take place.
> I have changed "tons" of things, and have indeed been able to
> optimize performance.
>
> I found out that writing like 12000 records to a table with
> referential integrity to another, takes a LOT more resources
> than when it is 'un-related'.
>
> When having a FE/BE setup, and using an action query in the FE
> (with the 1 source table on which the query is based also in
> the FE) to update a linked table in the BE, it generates a LOT
> more network traffic when this target table has relations with
> ref. integrity than when it has not! In time this difference
> is about 1 to 30, so 30 times faster without relations in the
> target table (2 seconds opposed to 1 minute). Also during the
> entire minute network traffic is quite high (800kbyte/s), as
> opposed to otherwise it's just 2 seconds of that throughput.
>
> Is this normal behaviour or can I do anything about it? The
> field in the target table is set to TEXT, 255chars, Unicode
> compress set to YES and INDEXED set to YES but dupes OK. This
> works fast if that field is not related.
>
> The table has got 300 records now, and I want to add 12000 to
> it, and I will regularly add quite big amounts of records, so
> I'd want it optimized. I can of course remove relations
> programmatically and re-add them after writing, not sure if
> it's a good idea.
>
> I disabled opportunistic locking, in fact my MDB is intended
> for multi-user (serve a few users over ASP).
>
> This MS site had tons of tips too of which I am still to try
> one:
>
> http://support.microsoft.com/defaul...kb;en-us;889588
>
>
> "david epsom dot com dot au" < david@epsomdotcomdot
au> wrote in
> news:#2m$4enWFHA.3188@TK2MSFTNGP09.phx.gbl:
>

Sponsored Links