"Jamie Collins" wrote in message
oups.com...
On 24 Aug, 13:37, "BruceM" wrote:
In previous discussions several people who have demonstrated a good
command
of Access and of databases in general questioned the importance of
clustered
indexes for physical ordering on the disk.
http://groups.google.com/group/micro...browse_thread/...

There's 101 posts in that thread and you linked to one which I wrote!

I was trying to point to the thread, not to a specific message. Albert
argued that there is no reason not to use PK. Presumably if a
non-maintained clustered index was important that would have been worth
mentioning. He did say that there are perfectly valid reasons to use a
natural key, too.
Amy had some observations on the subject, including regarding it as silly
(or some such) that a PK (or non-null unique index) would be chosen for
clustering reasons.

Who do you think is the knowledgeable person in this thread? I recall
David W Fenton being particularly obstructive telling he knew of lots
of things but wouldn't deign to reveal them and ironically did bother
to come back several times to tell us he couldn't be bothered g. The
only relevant post I can pick out is this:

[David W Fenton] Given that with Jet you can only have the one
clustered index, I
really think this is a pretty irrelevant consideration. I never use
the BETWEEN operator on anything but date fields, which in the vast
majority of tables could not possibly ever be a candidate for PK
(and very seldom even a candidate for inclusion in a compound
natural key, which wouldn't give you the clustered index benefit,
anyway, unless the date was the first field of the compound key).

So, I just don't see any practical benefit in your pointing out the
performance advantage of the clustered index.

You can only have one clustered in index period -- it's not a Jet
restriction, more like the laws of physics g! In *this* thread I
posted the salary history example where dates are part of the
candidate keys and we see history table designs cropping up regularly
in the Access groups. And he answered his own point about including
such a date in a compound key i.e. make it the first column in the
key's definition. I generally get the feeling he was looking at things
the wrong way to be able to see my point i.e. he was considering
considers the clustering resulting from his existing choice of PK,
whereas I'm proposing one identifies the columns for clustering then
choose the PK to fit. There are some later exchanges but he seems to
dismiss the whole idea ("I think your suggestion is bloody stupid", "I
think you're mis-using your RDBMS") without giving solid reasons.

http://groups.google.com/group/micro...tablesdbdesign...

That one's you and I again! And the only other person who commented on
clustering is David W Fenton again but this time he's actually
acknowledged the point:


One thing that bothers me is there is no frame of reference in this
thread e.g. David W Fenton does not give any comparison to other
optimization techniques. The Microsoft articles rated it highly, after
all.

There is a variety of views within those threads.

Indeed. Notice how a few people needed convincing that clustering
existed in Jet at all, after which they dropped out of the discussion.

I guess that makes you the winner. After this discussion you can add me to
the list of the vanquished, because I don't feel like extending this much
longer.


I still doubt there is a performance issue between indexing on an
arbitrary
number such as EmployeeID and and arbitrary PK.

Agreed. I can't see either would offer any advantage. Clustering is
better suited to meaningful data.

Also, I may choose to list
employees by lastname, firstname, but have many occasions to order or
group
by department, startdate, or what have you.

You then have to look at the relative usage frequency, importance,
etc. I didn't say optimization was easy g.

BTW, one of the articles states that the rows will be ordered according
to
the primary key OR the unique index, but leaves me guessing as to what
happens if both exist.

PK takes precedence, as advised by Lyle Fairfield in the first thread
you linked to.

I do know that there is no direct way of
opening the back end (and getting to the table data that way) without
going
through the secure mdw file

Users have full permissions to the table links in the FE, and limited
permissions (according to the group of which they are a member) to the
actual BE tables.

What you described with
the Excel thing is in fact a back-door update, undertaken by somebody who
is
deliberately and knowingly circumventing the intended route for entering
data. In my book that is hacking, not an innocent attempt to be more
efficient.

If they've used their own credentials via your mdw (i.e. utilizing
your security mesures rather than circuventing them) to connect to the
data in the BE from within Excel then in my book that is legitimate
usage and IMO should be accommodated.

Is there documentation that what you describe is possible?

Jamie.

--