How big a record size is too big from a performance standpoint?

[Dennis]

Hi,

I'm pretty new to Access and I would like to know from a performance
standpoing if there is a record / row size that is too big for an Access
database.

My record size will be about 4k to 6k and I will have a max of 10,000
record. They will be multiple users (1 to 4) accessing the database.

I think Access 2003 and above will handle this, but I wanted to make sure
before I got to far into the project.

Thanks,

 

[Jeff Boyce]

Dennis

Use Access HELP and check "specifications". I believe you'll find that the
max record length in Access is 2K.

That said, a record with that much data may result from a
less-than-well-normalized table structure.

If you'll describe more specifically what data you are trying to store in
your table(s), folks here may be able to offer more specific suggestions.

By the way, 10,000 records is a pittance ... no worries there. Multiple
users is what Access handles straight out of the box ... no worries there.

But, "it depends" ... performance is the result of a whole lot of factors.
You've only posited one ...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Keith Wilby]

Hi,

I'm pretty new to Access and I would like to know from a performance
standpoing if there is a record / row size that is too big for an Access
database.

My record size will be about 4k to 6k and I will have a max of 10,000
record. They will be multiple users (1 to 4) accessing the database.

I think Access 2003 and above will handle this, but I wanted to make sure
before I got to far into the project.

How do you know how "big" each record is going to be? I think that as long
as you have a normalised design and a split GUI you can't go far wrong, but
there's no substitute for testing.

Keith.
www.keithwilby.co.uk

 

[Dennis]

Jeff,

The database has been normalized to 3rd normal form. There is just a lot of
data. I was more concerned about the size of the record being too big for
decent performance on Access.

According to specs, No of chars in record (excluding Memo and OLE) with
Unicode Compression property set to Yes is 4,000. Thanks for the info.

I know that I just posted on aspect of the perfermance question, but
unfortunately I don't know enough about Access to know the performance
questions to ask.

Keith,

You asked how do I know how "big" each record is? I added up the field
lenght of each field. I don't know if Access have variable length fields or
fix length records. Until I can figure out differently, I'm assuming that
Access has fixed length record.

 

[John W. Vinson]

Hi,

I'm pretty new to Access and I would like to know from a performance
standpoing if there is a record / row size that is too big for an Access
database.

My record size will be about 4k to 6k and I will have a max of 10,000
record. They will be multiple users (1 to 4) accessing the database.

I think Access 2003 and above will handle this, but I wanted to make sure
before I got to far into the project.

Unless part of the data in the record is in a Memo field you're in trouble.
The maximum size of a record (exclusive of Memo or GUI fields) is 2000 bytes.
Annoyingly, you can easily create a table with (say) 40 Text fields each of
255 bytes, and even enter data into it - but you'll get an error message when
you first try to save a record with more than 2000 bytes actually occupied.

What is the structure of your table? How many fields, of what types? What sort
of data is in these fields? Might you be able to normalize a wide-flat table
into several related tall-thin tables?

 

[John W. Vinson]

You asked how do I know how "big" each record is? I added up the field
lenght of each field. I don't know if Access have variable length fields or
fix length records. Until I can figure out differently, I'm assuming that
Access has fixed length record.

Text fields are variable length; trailing blanks are NOT stored. This is an
advantage and also a nasty trap; as noted in my other post, you can easily
create a table which will fail when you add a record with too much data.

 

[Tony Toews [MVP]]

According to specs, No of chars in record (excluding Memo and OLE) with
Unicode Compression property set to Yes is 4,000. Thanks for the info.

That's correct.

I know that I just posted on aspect of the perfermance question, but
unfortunately I don't know enough about Access to know the performance
questions to ask.

Performance isn't the problem.

You asked how do I know how "big" each record is? I added up the field
lenght of each field. I don't know if Access have variable length fields or
fix length records. Until I can figure out differently, I'm assuming that
Access has fixed length record.

Access test fields are all variable character length. (Actually you
can make them fixed length but you have to set a property via code or
something like that.) One problem that may happen though is that a
person may be typing away in the 30th field and hit the 4 kb limit and
be rather puzzled as to what is going on.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Jeff Boyce]

Dennis

Your definition of '3rd normal' and other folks' may not match.

If you'll provide an example of the field names and contents that you
believe will add up to such a large record, folks here may be able to
provide more specific suggestions...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.