How do you mulitply in a field?

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."

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).

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?

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 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?

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

... you just confirmed that they do...

...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"?

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.

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

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.

--