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

Author

RE: Enormous performance hit and network traffic (locking problem?

TomU

2005-05-16, 8:24 pm

I re-read your description of the problem...

Does your test create a new connection and recordset for each individual
insertion or is there just one connection and recordset? If the former,
creating the new, second connection over and over will generate a lot of net
traffic all by itself.

If your application allows it, I'd try using CurrentProject.Connection. In
my experience having a bound form open while running what amounts to batch
processes against the DB is actually faster.

I have apps processing thousands of large records over a fairly busy LAN, on
Pentium 3 PCs and it processes at what I estimate is several hundred+ records
per second.

TomU

Peter

2005-05-16, 8:24 pm

Hi,

In my tests I used 1 ADODB.Connection, opening at the beginning of the
procedure, processing the test loop of writing 1000 records, then
closing it, and setting the object reference to Nothing.

I tried several setups.

1. Dim objAdoCn as New Adodb.Connection '/ open a seperate Cn
2. Dim objAdoCn as Adodb.Connection
Set objAdoCn = CurrentProject.Connection '/ get reference to
existing object

With those connections I tried to use .Execute and then the SQL
statement "INSERT INTO temp(Path) VALUES('Testing')"

I also tried using a seperate ADODB.Recordset, and using .AddNew and
..Update, each iteration, as well as just .AddNew in the loop and after
looping a 1000 times, use .UpdateBatch, with the locking set to
adLockBatchOptimisti
c and cursorlocation to client etc.

Nothing helps.

Can my error be that I have linked tables in my Front-End to the Back-
End, and that instead of writing to the linked tables using an action
query, I And that on top of having linked tables I open an
ADODB.Connection to the Back-end and write to a table using that
instead of over the linked tables and some kind of Action Query from
within Access?

Question is, should I try to totally omit using ADO? When inside
Access anyway working with MDB.

"=?Utf-8?B?VG9tVQ==?=" <TomU@discussions.microsoft.com> wrote in
news:5A0611D4-E891-4738-B3F4- 35B347ED5617@microso
ft.com:

> I re-read your description of the problem...
>
> Does your test create a new connection and recordset for each
> individual insertion or is there just one connection and recordset?
> If the former, creating the new, second connection over and over
will
> generate a lot of net traffic all by itself.
>
> If your application allows it, I'd try using
> CurrentProject.Connection. In my experience having a bound form
open
> while running what amounts to batch processes against the DB is
> actually faster.
>
> I have apps processing thousands of large records over a fairly busy
> LAN, on Pentium 3 PCs and it processes at what I estimate is several
> hundred+ records per second.
>
> TomU

Immanuel Sibero

2005-05-16, 8:24 pm

Peter,

- For native Jet tables, whether linked or not, DAO is the best.

- Just curious, what happens if you maintain a persistent connection to the
backend while you're doing your testing? (just open a table and keep it
open i.e. minimizing it in Access).

Immanuel Sibero

"Peter" <p@x.com> wrote in message
news:Xns965913C562A2
Bpxcom@194.109.133.133...
> Hi,
>
> In my tests I used 1 ADODB.Connection, opening at the beginning of the
> procedure, processing the test loop of writing 1000 records, then
> closing it, and setting the object reference to Nothing.
>
> I tried several setups.
>
> 1. Dim objAdoCn as New Adodb.Connection '/ open a seperate Cn
> 2. Dim objAdoCn as Adodb.Connection
> Set objAdoCn = CurrentProject.Connection '/ get reference to
> existing object
>
> With those connections I tried to use .Execute and then the SQL
> statement "INSERT INTO temp(Path) VALUES('Testing')"
>
> I also tried using a seperate ADODB.Recordset, and using .AddNew and
> .Update, each iteration, as well as just .AddNew in the loop and after
> looping a 1000 times, use .UpdateBatch, with the locking set to
> adLockBatchOptimisti
c and cursorlocation to client etc.
>
> Nothing helps.
>
> Can my error be that I have linked tables in my Front-End to the Back-
> End, and that instead of writing to the linked tables using an action
> query, I And that on top of having linked tables I open an
> ADODB.Connection to the Back-end and write to a table using that
> instead of over the linked tables and some kind of Action Query from
> within Access?
>
> Question is, should I try to totally omit using ADO? When inside
> Access anyway working with MDB.
>
>
> "=?Utf-8?B?VG9tVQ==?=" <TomU@discussions.microsoft.com> wrote in
> news:5A0611D4-E891-4738-B3F4- 35B347ED5617@microso
ft.com:
>
> will
> open
>

Sponsored Links