Jim Dettman
jimdettman at earthlink.net
Fri Aug 15 08:01:14 CDT 2003
John,
I never said that I believed it or hoped it to be true. I simply said I
don't know the answer. And I didn't say you were wrong either. I just
asked how you knew for sure that it was the case other then observation.
I'm glad Martin has the ability to pass this along (hopefully to Kevin)
and get an answer that is definite. Then we will know for sure.
Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Thursday, August 14, 2003 2:43 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields
Jim,
Since no one (I know) knows the internals of JET, I am not going to argue
with you on that account. In fact, I am not going to argue with you at all.
If you wish to believe (or hope, or pray) that Jet can magically parse a
bunch of stuff used to create an appended string back into it's components,
and then use that information to decide whether or not to attempt to apply
Rushmore to whatever pieces it can determine comes from table fields....
uh...
Can I state it as a fact. Of course not.
What I can say is that I have a very good class for timing stuff down to
milli-second increments. Anyone who wishes to may use that class to
empirically time such queries. I will be willing to bet good money, up to a
month's wages (I'm "unemployed" of course) against an equal number of
doughnuts that you will find, 100 times out of 100 times, that attempting to
apply a sort to such a field will, each and every time, be an order of
magnitude slower than using the component fields yourself to perform the
sort.
And of course, having done that, one still can't state as FACT that Jet
isn't attempting to use Rushmore to perform the sort on the appended string
field.
And yes, I have read it in various places over the years. I have long ago
lost the ability to recall where I read every piece of information I have
read.
So let's not start any myths here. I do not know for a fact that Jet
doesn't use Rushmore (or anything else) to attempt to optimize such queries.
I simply have the sense to look at what would be required, the silliness of
the concept, and the results I have seen in my personal experience, and
state that I know, without a shadow of a doubt, that Access makes no attempt
to apply such methods to a sort of an appended string.
John W. Colby
www.colbyconsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Thursday, August 14, 2003 1:12 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields
John,
<<Nope, I cannot give you and sources and will not spend my time looking.
Go
look in any book on Access queries, sorting and indexing and I'm sure you'll
find your own.>>
That's why I'm asking. I have pretty much read everything there is to
read about JET over the past 10 years, and I've never seen anything on this.
<<[Fielda] & Random() & [Fieldb] & ascii(right$("John Colby",1))
How in the world is any index on FieldA and FieldB going to help you sort
that expression into logical order?>>
Rushmore works by making merges/intersections between indexes. JET
queries as we all know are very aware of the underlying tables. This has
been a key feature of Access/JET since day 1. JET may be smart enough to
figure out that the logical order is based on two fields it already has
indexes for. Then again it may not be.
<<How in the world is Access going to determine whether to even try?>>
It knows the field names and what tables they come from.
<<The resulting string expression can be ANYTHING!!!>>
Yes, but that wasn't the example Mark gave.
<<Access does NOT use the indexes on the fields used to build up an appended
string. If you apply a sort order to such a string, Access will do the sort
on the resulting string, on-the-fly, and it will be PAINFULLY slow!!!>>
That seems to be the case, but do you really know for sure? I don't
believe you do. I'm not saying your wrong, but I don't think you can state
it as a fact, unless you have seen docs on JET's internal workings. That's
why I asked you to cite a source.
It's just like the myth that got started about domain functions always
being the slowest of methods. This is not the case as in some situations, a
domain function can actually beat every other method. That floated around
for several years until someone sat down and tested it and found it not to
be true.
Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Thursday, August 14, 2003 10:09 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields
<<snip>>
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com