Maximizing performance: Managing tables & queries

P

Pascale Breton

Hello again to all of you,

Still in the goal of improving our manners here to use MsAccess and develop
DB, I was wondering what would be more efficient in the design of a DB:

1) Reducing tables fields at a maximum
Add filters to queries to compense calculated fields deleted from the tables

OR

2) Reducing filters of my queries at a maximum
Add calculated fields in my tables to compense deleted filters from my queries

I think the second option would be the most efficient because the more you
have filters in a query, the more time it will take to run. But, my dilemma
is that if I enlarge my tables, I would have to archive or compress more
often and maybe it will have as a result that less data overtime would be
available to the users.

What are your opinions?

Thanks again :)
 
J

John Vinson

Hello again to all of you,

Still in the goal of improving our manners here to use MsAccess and develop
DB, I was wondering what would be more efficient in the design of a DB:

1) Reducing tables fields at a maximum
Add filters to queries to compense calculated fields deleted from the tables

OR

2) Reducing filters of my queries at a maximum
Add calculated fields in my tables to compense deleted filters from my queries

I think the second option would be the most efficient because the more you
have filters in a query, the more time it will take to run. But, my dilemma
is that if I enlarge my tables, I would have to archive or compress more
often and maybe it will have as a result that less data overtime would be
available to the users.

What are your opinions?

Thanks again :)
Click to expand...

Well... you're using the term "filters" in a way that I'm not used to.
To me, a Filter is a set of critiria applied to a Form or a Report,
certainly not to a Table.

My canned rant about storing calculated data in tables:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

With proper indexing and careful query design, you should only VERY
RARELY need to denormalize your table structure. My consulting resume
contains a line "judicious denormalization, only when necessary" - and
I very rarely use that loophole!

When it comes to selecting subsets of the fields, or records, in a
table, a Query is absolutely the way to go. Storing data redundantly
in another table MIGHT be needed if you're going to do *multiple
different* complex calculations using that subset of the data; if that
were to be necessary, your best bet would be to use VBA code to
*create a new temporary mdb file* to contain the temp table, linked.

John W. Vinson[MVP]
 
Top