A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old November 24th, 2009, 05:25 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default How big a record size is too big from a performance standpoint?

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,


-----


Dennis

--
Dennis
  #2  
Old November 24th, 2009, 05:52 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How big a record size is too big from a performance standpoint?

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.

"Dennis" wrote in message
...
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,


-----


Dennis

--
Dennis



  #3  
Old November 24th, 2009, 05:53 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default How big a record size is too big from a performance standpoint?

"Dennis" wrote in message
...
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

  #4  
Old November 25th, 2009, 12:50 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default How big a record size is too big from a performance standpoint

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.

--
Dennis


  #5  
Old November 25th, 2009, 04:01 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default How big a record size is too big from a performance standpoint?

On Tue, 24 Nov 2009 08:25:01 -0800, Dennis
wrote:

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 [MVP]
  #6  
Old November 25th, 2009, 04:06 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default How big a record size is too big from a performance standpoint

On Tue, 24 Nov 2009 15:50:02 -0800, Dennis
wrote:

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.
--

John W. Vinson [MVP]
  #7  
Old November 25th, 2009, 04:15 AM posted to microsoft.public.access.tablesdbdesign
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default How big a record size is too big from a performance standpoint

Dennis wrote:

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/
  #8  
Old November 30th, 2009, 05:48 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How big a record size is too big from a performance standpoint

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.

"Dennis" wrote in message
...
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.

--
Dennis




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.