Performance Issues with MS ACCESS

[sylvain]

Hi,

i'm using MS ACCESS to get data from an Oracle Db. One of my client,
located far away, is trying to run some queries and we have bad
performances.

We've changed the odbc from Oracle to MS because it appears that
Oracle ODBC driver badly handles number of rows fetch and the gain
shows.

When the query fetches between 1-4000 rows, we have good performance
but after that it drops dramatically. I've boosted the buffer size of
the MS odbc but it doesn't appears to have any effect. I've boosted
my users memory and we gained about 10-20%.

We dont have a trafic problem because we have sniffed it and the band
is used 0%

If you have any suggestions, please foward me some advice

 

[Albert D. Kallal]

We dont have a trafic problem because we have sniffed it and the band
is used 0%

If you have any suggestions, please foward me some advice

Well, you mention that up to about 4000 records...it seems ok.

Obvious, to get the 4000 records, the bandwidth can't be 0%

Perhaps you simply just have a slow connection?

Did you ask how fast the connection is?

Lets dump all the mumbo jumbo stuff about bits, bytes, etc. Really, I can
ask how fast a car goes...and I don't need a engineering degree!). All we
need is a comparison here by numbers. So, ask you network guy how fast, or
slow the network connection is compared to the office network (and, ask for
a answer in terms of speed...not a bunch of bits and bytes stuff. If the
network guy can't answer the question...fire him, and get a new one..).

A typical office network is rated at 100 mbits

A typical high speed internet connection (ADSL, or high speed cable) is
usually .5 to 1.5 mbitts. Lets be kind, and take the middle range of 1

And, a phone line dial up with a 56k modem is .056 (but, in real terms..the
phone line is actually limited, you get about .040 at best)

So, we have:

Office network = 100
High Speed Internet (your wan, or vpn) = 1
Dial up modem = .040

so, if something takes 5 seconds on your office network, on your high speed
internet, this will take 500 seconds (or 8 minutes).

That 5 seconds on the dial up modem will take 12500 seconds, or 208 minutes.

So, what is the speed of that remote connection (you did ask...right?).

You can certainly get a low speed remote connection to work (and, as you
mention, it does seem to work ok with about 4000 records). And, for editing
of data...you only grab one record down the wire...and that will be fast.
However, if you connection is low speed, then you really want to limit the
number of records transferred.

Also, if you have any linked tables that are used in a query (ie: MORE THEN
one linked table is used in a query...simply change this to a view, and link
to that. You do NOT WANT to used more then ONE linked table in a query...as
much performance problems can occur.

So, link to views in that case of sql joins etc. (don't join linked tables).
This simple change might be the trick you need for your speed in reports.

And, also ...get the speed of the network