How big a record size is too big from a performance standpoint
in [Tables]
|
Prev: How big a record size is too big from a performance standpoint?
Next: Setting up Observation Database
From: Dennis on 24 Nov 2009 18:50 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
From: John W. Vinson on 24 Nov 2009 22:06 On Tue, 24 Nov 2009 15:50:02 -0800, Dennis <Dennis(a)discussions.microsoft.com> 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]
From: Tony Toews [MVP] on 24 Nov 2009 22:15 Dennis <Dennis(a)discussions.microsoft.com> 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/
From: Jeff Boyce on 30 Nov 2009 11:48
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" <Dennis(a)discussions.microsoft.com> wrote in message news:1BEB7E5A-B8D6-443E-BEA8-DF1BD1CD92C3(a)microsoft.com... > 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 > > |