locked
Which method is least resource intensive? RRS feed

  • Question

  • I need to visually display two subforms, based on a common group of records.  I want subgroup 1 to appear in subform1, subgroup 2 to appear in subform2.  I can store my group of records in two tables and move records between the tables when I need to make updates.  Or I can keep them together in one table, and use an extra field to create the distinction between the two subgroups within my group of records, filtering each subform to only display records with the proper designation.  

    I desire to click through records on the main form in the fastest manner possible.  I care less about the performance and time it takes to update these records, because they will infrequently move between one subform and another.  Can anyone tell me which method will give me best results?  I've found adding a sort to my subforms can significantly degrade performance.  I haven't had the chance to test the scenario using filter, but I'm skeptical based on my experience with sort.

    I'm using a FE-BE design with the BE on the shared drive.

    Monday, February 13, 2012 9:00 PM

Answers

  • If you will need only 2 subgroups ever, a Boolean field would be ideal.

    Regards, Hans Vogelaar

    • Marked as answer by Bruce Song Monday, March 5, 2012 2:17 AM
    Monday, February 13, 2012 10:05 PM

All replies

  • From a database design viewpoint, a one-table solution would be preferable. Creating two tables with the same structure makes maintenance more work, and moving records between tables causes more database bloat than changing the value of a field.

    If you create an index on the field used to distinguish the subgroups and also on the field(s) that link the subforms to the main form, performance should be pretty good.

    But if you don't mind the overhead, having two tables is probably slightly faster. How much depends on the size of the tables.


    Regards, Hans Vogelaar

    Monday, February 13, 2012 9:35 PM
  • I'm working with a database that can have anywhere from just a couple thousand records to up to 100,000 records.  I've been a bit too enthusiastic about indexes in the past, so I'm skeptical of adding them if I really don't need them.  That said, because the only distinction I' want between records is whether they exist on one subform, or another, I could probably get by on the one table solution, using a simple binary field.  This may cut down on the resources necessary for indexing?
    Monday, February 13, 2012 10:00 PM
  • If you will need only 2 subgroups ever, a Boolean field would be ideal.

    Regards, Hans Vogelaar

    • Marked as answer by Bruce Song Monday, March 5, 2012 2:17 AM
    Monday, February 13, 2012 10:05 PM
  • Ah, yes!  I meant boolean.
    Tuesday, February 14, 2012 4:23 PM