Microsoft Access Table Record Limitation

[mark]

Hi, is there a limit to how may rows an access table can have before it performs poorly?

 

[Arvin Meyer [MVP]]

Hi, is there a limit to how may rows an access table can have before it
performs poorly?

That's depends upon the type and quality of indexes, as well as other
factors. I have a database front-end that connects to a 1.1 GB backend with
6 million records. By using the primary key, I can pull 1 record out of the
database near instantaneously. With an indexed datefield, I was able to find
all 721 records matching a single date in about 4 seconds. Multiple joins
and or criteria may take a bit longer. Is that poor performance? I don't
think so.

 

[mark]

That's some impressive numbers Arvin. I always thought access was not that scalable but now I feel a lot more comfortable using access for my projects

Thanks

 

[Tom Wickerath]

Hi Mark,

No. You can get poor performance for a host of reasons, including searching
on a non-indexed field, not having a persistent connection to the data file,
calling a function from a query, lack of defined relationships with enforced
referential integrity, etc.

In SQL Server or Oracle, you can avoid the overhead of transferring index
trees over the network by running a stored procedure, so yes, that does save
some band width on the network.

Take a look at this paper for some ideas:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[mark]

oh, I see. Looks like i have a bit more researching on my hand. Nice article resource, Thanks Tom

 

[David W. Fenton]

That's depends upon the type and quality of indexes, as well as
other factors. I have a database front-end that connects to a 1.1
GB backend with 6 million records. By using the primary key, I can
pull 1 record out of the database near instantaneously. With an
indexed datefield, I was able to find all 721 records matching a
single date in about 4 seconds. Multiple joins and or criteria may
take a bit longer. Is that poor performance? I don't think so.

The biggest drain on performance, in my experience, is outer joins.
Inner joins are very fast, especially with criteria on both sides of
the join. Outer joins, on the other hand, are quite
resource-instensive even with relatively small tables (10s of 1000s
of records instead of 100s of 1000s or even millions).

 

[David W. Fenton]

In SQL Server or Oracle, you can avoid the overhead of
transferring index trees over the network by running a stored
procedure, so yes, that does save some band width on the network.

Why go to an sproc for that? Server-side views to do joins on the
server get you the same results for complex joins without involving
Jet with anything but the result of the view (and any criteria that
Jet is able to send to the server to apply to the join). In apps
with SQL Server back ends where I have frequently-joined tables, I
will usually create a server-side view for those frequent joins and
use that instead of joining in my client-side SQL. Jet will usually
be smart enough to hand the join off to the server, but by using a
server-side view, you insure that it happens on the server.

 

[mark]

Hi David,

When you do a make a View does actual data get stored in the View or does it run a join when it execute at query? In other words, does the size of the db increase with respect to the View size?

 

[Arvin Meyer [MVP]]

The biggest drain on performance, in my experience, is outer joins.
Inner joins are very fast, especially with criteria on both sides of
the join. Outer joins, on the other hand, are quite
resource-instensive even with relatively small tables (10s of 1000s
of records instead of 100s of 1000s or even millions).

With Outer Joins, it is often the criteria that cause the performance
degradation. Even larger performance degradation can be caused by functions,
especially User Defined Functions, and by subqueries, especially correlated
subqueries. IOW, the fastest performance when using multiple tables is an
Inner Join, which is much faster than a Where Clause.

 

[Arvin Meyer [MVP]]

More scalable than even that. That particular database has 2 back-ends, each
with approximately 6 million records, joined by a Union query. A single
record, using an indexed field on the Union query takes 32 seconds.

I normally would have used SQL-Server Express for that situation, but the
client wanted Access because of the ease of changing back end data.

The largest Access database I ever worked on was an Access 2.0 (16 bit)
database with 78 back-ends containing OLE images. totaling over 30 GBs
running on a DEC Alpha server, with the front-end on Windows 98. There were
only 2 users, and as far as I know, it has never corrupted.

 

[Armen Stein]

When you do a make a View does actual data get stored in the View
or does it run a join when it execute at query? In other words, does
the size of the db increase with respect to the View size?

No, a SQL Server View is sort of like an Access Query, in that it runs
the joins when you use it. It doesn't store the actual data from the
tables, but it is pre-optimized to run more efficiently.

The main point David was making is that by using a View, you are
ensuring that the joins are running on the server side, not the Access
side.

If you're interested, I've written a PowerPoint presentation on
techniques for using Access as a client-server front-end to SQL Server
databases. It's called "Best of Both Worlds" at our J Street
Downloads page: http://ow.ly/M2WI. It includes some thoughts on when
to use SQL Server, performance and security considerations,
concurrency approaches, and techniques to help everything run
smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Arvin Meyer [MVP]]

A View is SQL-Server parlance for a select query. Nothing is stored. You can
import Views as tables or link to them.

 

[David W. Fenton]

With Outer Joins, it is often the criteria that cause the
performance degradation.

Actually, I'd say that it's an outer join withOUT criteria on the
outer table that causes the problem. If there are criteria on that
table, it vastly changes the standard query optimization.

Even larger performance degradation can be caused by functions,
especially User Defined Functions, and by subqueries, especially
correlated subqueries.

....but again, that depends on the particular subqueries. Some are
well-optimized, some are not.

IOW, the fastest performance when using multiple tables is an
Inner Join, which is much faster than a Where Clause.

???

I'm not sure if we're talking about Jet/ACE data only or ODBC data.
My original comment was about moving outer joins to a server-side
view. In regard to explicit JOINS vs. implicit joins (i.e., using an
equivalent WHERE clause), different db engines optimize differently.
Jet optimizes the two exactly the same way.

On the other hand, there are joins that can't be written as WHERE
clauses, and vice versa. I can't come up with a principle to define
when I use implicit joins, but I do it occasionally (did so
recently, but can't find it because I don't remember which project
it was in).

 

[David W. Fenton]

When you do a make a View does actual data get stored in the View
or does it run a join when it execute at query? In other words,
does the size of the db increase with respect to the View size?

Server-side views are nothing more than saved SQL statements. SQL
Server allows you to create virtual indexes on a view (i.e., on a
calculated field), called an "indexed view," and these are actually
stored as temp tables, but my understanding is that each time you
run it, the temp table is updated. You can also explicitly create
temp tables in most server databases, but the advantage of the
indexed view is that it happens automatically once you set up the
virtual index on the view.

But that's the exception, rather than the rule. In general, a
server-side view retrieves the data fresh each time you run it, just
like a saved QueryDef in Access.