"Jamie Collins" wrote in message
ups.com...
On 23 Aug, 13:03, "BruceM" wrote:
Knowledgeable people have pointed out that there are many performance
factors to be considered before physical ordering on the disk, and have
questioned whether such physical ordering is really relevant, at least in
an
office-sized database. While I accept the validity of a multi-field
index
(or several single-field indices), I don't accept the physical ordering
argument.

Yes, there are many factors to consider as regards performance. Yes,
different people will rate these factors differently. But here's a
link to a MSDN article about query performance in an Access database
which places physical ordering at the top of its list of "Tips to
improve query performance":

Information about query performance in an Access database
http://support.microsoft.com/kb/209126


"To improve query performance, try these tips: Compact your database.
When you compact your database you can speed up queries. When you
compact your database, the records of the table are reorganized so
that the records reside in adjacent database pages that are ordered by
the primary key of the table. This improves the performance of the
sequential scans of records in the table because only the minimum
number of database pages now have to be read to retrieve the records
that you want."

I never argued against compacting.


Here is another a MSDN article which has a list of "Optimization Tips
and Techniques" and physical ordering is the second one listed:

Microsoft Jet 3.5 Performance Overview and Optimization Techniques
http://msdn.microsoft.com/archive/de...baseEngine.asp

"From a performance perspective, there are many reasons to frequently
compact a database. One reason is that compacting will create a new
database that stores all table rows in a contiguous order. If a
primary key or unique index is defined, the rows will be sorted in
order of the primary key or unique index. This allows Microsoft Jet to
take full advantage of its read-ahead cache and also reduces disk I/O
when doing sequential scans of a table."

Out of interest, the first one listed is "Prevent Unnecessary Query
Recompilation" and seems to relate only to stored queries and then
only in conjunction with DAO's infamous memory leak bug (which I
understand has since been fixed).

There's also something about judicious use of indexes, and something about
the use of expressions in subqueries. The latter includes the
recommendation that expressions be used in the front end (i.e. the form) to
produce the necessary information in subforms. Anyhow, it looks like a good
article. I have bookmarked it.

In case it's not clear what these articles are getting at, here are a
few aircode examples of 'sequential scans' that will favour physical
ordering:

SELECT *
FROM TelephoneDirectory
WHERE last_name = 'Collins';

SELECT *
FROM SalaryHistory
WHERE employee_number = '454818181'
AND start_date
BETWEEN #2006-01-01 00:00:00# AND
AND #2006-12-31 23:59:59#;

SELECT employee_number,
COUNT(*) AS salary_review_tally
FROM SalaryHistory
GROUP BY employee_number;

When was the last time you used BETWEEN or GROUP BY on an autonumber
column?

Gosh, I can't remember when I last did that. But what a probing and
thought-provoking question!! sarcasm
I have been saying the same thing over and over, and will stop doing so
after this message. I do not rely on autonumber to make a record unique. I
use autonumber when no "natural" field meets my needs for an Access PK.
You have not answered my objection to linking tables through fields that are
subject to change.


Now, be fair, where are these knowledgeable people's lists and where
exactly does physical ordering appear in their lists? If they've
omitted physical ordering from their lists, how do we know they've
even considered it? TIA.

I have seen the stuff about compacting. No argument. Once again, I am not
arguing against compacting. That is to say, I think compacting is a good
thing, and I have observed performance differences after compacting.
However, I have seen information about how indexing choices can degrade
performance (for instance, when using indexes on City and PostalCode
fields). As the article points out, there is no single correct answer about
indexing.


I grant permissions to groups, which is the most common scenario,
but is in the same category as granting permissions to individual users.

OK so you give people the permissions...

Even if their intentions are the best, they should not be able to get
into
the tables directly.

Then why give you give them (via their groups) the permissions to do
so?

I don't. I never said I did. Unless you know some sort of back door (or
are a member of the appropriate group), you would not be able to open the
back end directly.


If they have the permissions of which you speak ...

.. you just confirmed that they do...

Not to access the tables directly.


...they are able to add
incorrect data that fulfill the table-level constraints.

So why not conclude, "I'd better put in some table-level constraints
to prevent incorrect data"?

I wouldn't know how to limit at the table level the list of models if "SUV"
is chosen as the class of vehicle. So far I see no reason to learn.


Integrity constraints are one thing, but I don't know that my time would
be
well spent devising what amount to backup validation rules at the table
level.

Funny, I start with the table-level constraints first (i.e. they are
not back ups) then sigh when I think about having to duplicate the
effort in the front end g.

OK, fine.

You design your tables *before* you design your forms, right? Why not
the same for constraints?

Of course I design the tables (and relationships) first. However, I have
changed elements of the table design after development of forms, etc. is
well under way. I have also gone back and re-thought the design. You?


I have attempted to agree with you, only to have you
fire back as if I had never said anything. That gets old in a hurry.

Oops, sincere apologies. Please believe I don't do that on purpose. I
think it must happen only when I've missed your point (or I think
you've missed my point when you haven't etc).

Jamie.

--