There is a lot factors here.
If you have a very small table with only 50,000 records, and you need to
retrieve a invoice record, and display it, the response of the system will
be instant. If you triple the size to 150,000 records, the response of the
system will still be instant. In fact, you can go to 300,000 records, and
you will generally still find the response instant.
Of cause, this instant response assumes you don't have an application
written by a amateur, and do stupid things like open a form to a table
without restring the records that will be loaded into a form. I can't tell
how many times I seen appcltions with a lot of records, and the designer
simply makes a access form, and sets it to a table, and then thinks they are
done.
So, the largest factor in performance is that skill level of your developer
team, and how well they design the application. With many years of
experience, you will find far more performance then some the silly
appcltions where records loaded into forms are not controlled.
You have to remember that ms-access is only a client tool to your database
of choice. The most common/default database engine used with ms-access if of
JET. The JET database is extremely fast. In fact, I have found it to be
faster then sql-server, or even MySql. Based on this, JET thus going to be
faster then most server based engines, and that would also include Oracle.
Of course, those other database engines are not file based systems, and thus
you have to grab your data via odbc, or some type of socket connection. JET
runs a good deal faster in these cases where no network is involved.
Of course, the issue of networks and how you plan to connect to the data is
also a huge issue. Are you using a network? Are you multi user? I really
could ask several thousand questions here, as there are so many variables.
If your application allows you to work with small amounts of records from a
large table, then performance will not drop too much. However, if you have
to run a daily report of customers from a given city, well when you reach 2
million customers, that report is going to slow down a lot since SO MANY
customers will meet the criteria you are using. If you have to print and
report on 300,000 customers per day..then things will be slow due to so much
data. If out of that 2 million customers you only need to retrieve 15
customers to print, then the report will run very fast. So, again, the issue
of performance will depend on the type of application, and how many records
you need to work with at a given time.
I have a application with about 60 tables (tons of relations), and tables
are very small (only in the 50,000 record range). With 5 users pounding on
the application all day long, there is no perceptible delays in forms loads,
and general operations. No question when the tables get to 500,000 sizes,
then I will most certainly be in need of something better then JET, but I
can certainly can continue to use ms-access. As mentioned, ms-access is only
a client to the database engine of your choice. You can move your data to
Oracle, or sql-server, and continue to use ms-access for the application.
So, if you outgrow the database engine called JET, then you can switch to
another engine.
