Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    No. of fields in a table

    I know that the maximum number of fields in a table is 255. I have one table which is now at 211 fields and I am wondering whether this many fields affects performance. Should I separate into separate tables with a one to one relationship?

    There are no repeating fields.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Do you really need all those fields? Do they all get values or do you have many nulls? Do you have many records on that table?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    There are currently over 17,000 records and yes almost all fields are used. It is an engineering job tracking system which tracks the requirements for each job: specs, dates etc.
    There are some nulls especially when one particular function is N/A for the job.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    What type of values are stored in those columns? Are they text columns or something smaller in terms of storage size?

    I don't know the storage structure used by Access, so I can't really tell whether it will impact performance or not. If this was SQL Server, I would try to know the size used by each record to try to figure out the best usage of the SQL Server's storage structure.
    My questions up to this point were meant to have an idea of the record size and the existance of a big number of nulls. The latter would probably suggest a new look at the underlying data model. Your reply made me think the number of nulls is not that big.

    So, I have no easy answer for your question. I would probably feel some discomfort in having so many fields and would "model my way out of it", just to ensure better manageability of all those fields, probably by grouping them and storing each group in its own table. However, as I said, I have no specific knowledge of Access that would lead me to believe that will cost you in terms of performance. Of course, indexes will be recommended, whenever you need to access just part of the table data, in order to minimize the need to scan the whole table.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Many thanks for the info.

    The record size is not that great as there are a lot of Yes/No fields.

    The problem I face is the selection criteria required by the customer. i.e.

    I could design a separate table for the dates that would be a lot more normalised. An example would be as follows:

    ID (key field)
    Description (Engineering, Drafting, Install, etc.)
    Date
    Supply (Yes/No)
    Install (Yes/No)
    Not_Applicable (Yes/No)

    The problem with this is that the user's criteria require combinations of records -
    e.g. Select all job records where Engineering Supply Yes and Drafting Supply No. I could probably do this with a Cross-tab query but then I run into problems with the data updatability.

    Hope this makes sense

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    If record size is no concern, then I probably wouldn't worry about it. Again, I don't know Access's data structures but on SQL Server, for example, a row that has a size bigger than the row size limit is stored in a different type of page than one that doesn't go over that limit. Depending on how you access the data, the access can get quite slower than if the data was all on a normal page. If record size here is not big, then it may not be a problem.

    Anyway, the type of query you describe could simply be done through joins and no crosstabs would be required.

    Maybe someone else with experience on using Access databases with a big number of fields can share that experience. My observations are a result from making a paralel with SQL Server. Sorry if this doesn't help you as you'd expect.

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I am sorry, I re-read your post and a doubt was raised on my mind. You don't have a normalized structure?

    I won't preach, but I don't really buy into the theories beyond denormalization, unless you have experienced real performance problems with a normalized structure. I know that the updateability of a recordset from a multiple table query is harder to occur, and handling updates requires more work since you can't just have Access deal with record saves automatically for you, but I wouldn't trade a recommended table structure for easier handling of record updates.
    Last edited by ruirib; 2011-02-18 at 14:53.

  8. The Following User Says Thank You to ruirib For This Useful Post:

    Peter_Thesiger (2011-02-20)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •