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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|